Parse log file into powershell, then sort into columns to output to CSV

Question

I've been trying to wrap my head around this as I've attempted some solutions but could not get any of the work. Im currently trying to read a log file and input it into an array and then have it sort the array out into columns and the data underneath..

This is what I have so far:

$logFileArray = Get-Content -Path @("C:\Users\testuser\datatoparse.log")

foreach($item in $logFileArray)
{

    write-host $item
}

$logFileArray | Export-CSV -Path "logfile.csv" -NoTypeInformation

Pause

An example of what is in the log file.

1   IKE Peer: 192.168.8.188 (PHX2)
Type    : L2L             Role    : initiator 
Rekey   : no              State   : MM_ACTIVE 
Encrypt : 3des            Hash    : SHA       
Auth    : preshared       Lifetime: 86400
Lifetime Remaining: 52140

An example of what I want it to look like for the end result in the csv file

Any help or pointers would be much appreciated, thank you!

EDIT to show more example of the log file

1   IKE Peer: 192.168.8.188 (PHX2)
Type    : L2L             Role    : initiator 
Rekey   : no              State   : MM_ACTIVE 
Encrypt : 3des            Hash    : SHA       
Auth    : preshared       Lifetime: 86400
Lifetime Remaining: 52140
2   IKE Peer: 192.168.8.189 (Worcester)
Type    : L2L             Role    : initiator 
Rekey   : no              State   : MM_ACTIVE 
Encrypt : aes-256         Hash    : SHA       
Auth    : preshared       Lifetime: 28800
Lifetime Remaining: 5929
3   IKE Peer: 192.168.8.190 (Fresno)
Type    : L2L             Role    : initiator 
Rekey   : no              State   : MM_ACTIVE 
Encrypt : aes-256         Hash    : SHA       
Auth    : preshared       Lifetime: 28800
Lifetime Remaining: 6564
4   IKE Peer: 192.168.8.191 (San Leandro)
Type    : L2L             Role    : initiator 
Rekey   : no              State   : MM_ACTIVE 
Encrypt : 3des            Hash    : SHA       
Auth    : preshared       Lifetime: 86400
Lifetime Remaining: 71608

Can you post a few more sample of the data in the log files to I can see how they are separated? — jdweng, Jun 09 '23 at 14:14

score 2 · Answer 1 · answered Jun 09 '23 at 15:21

Not the prettiest but this regex seems to work for the provided example. The result looks as follows:

Connection IKEPeer                     Type Role      Rekey State     Encrypt Hash Auth      Lifetime
---------- -------                     ---- ----      ----- -----     ------- ---- ----      --------       
1          192.168.8.188 (PHX2)        L2L  initiator no    MM_ACTIVE 3des    SHA  preshared 86400
2          192.168.8.189 (Worcester)   L2L  initiator no    MM_ACTIVE aes-256 SHA  preshared 28800
3          192.168.8.190 (Fresno)      L2L  initiator no    MM_ACTIVE aes-256 SHA  preshared 28800
4          192.168.8.191 (San Leandro) L2L  initiator no    MM_ACTIVE 3des    SHA  preshared 86400

Regex details: https://regex101.com/r/fd6tNy/1

# Note the use of `-Raw` is mandatory here:
$content = Get-Content -Path "C:\Users\testuser\datatoparse.log" -Raw

$re = -join @(
    '(?ms)'
    '(?<Connection>^\d+)\s+IKE\sPeer[\s:]+(?<IKEPeer>[\d\.]+\s\([\w ]+\)).+?'
    'Type[\s:]+(?<Type>[\w]+)\s+Role[\s:]+(?<Role>\w+).+?'
    'Rekey[\s:]+(?<Rekey>\w+)\s+State[\s:]+(?<State>\w+).+?'
    'Encrypt[\s:]+(?<Encrypt>[\w-]+)\s+Hash[\s:]+(?<Hash>\w+).+?'
    'Auth[\s:]+(?<Auth>\w+)\s+Lifetime[\s:]+(?<Lifetime>\d+).+?'
    'Lifetime\sRemaining[\s:]+(?<LifetimeRemaining>\d+)'
) -as [regex]

$out = [ordered]@{}

$re.Matches($content) | ForEach-Object {
    $out.Clear()
    foreach($group in $_.Groups | Select-Object -Skip 1) {
        $out[$group.Name] = $group.Value
    }
    [pscustomobject] $out
} | Format-Table -AutoSize

When I went ahead and used this. my log file is around 40 connections and it actually skips some of the connections. The below answer was able to output every connection. It was very insightful to see how you attempted this question tho. Thank you so much :) — Jamie Lovenduski, Jun 09 '23 at 16:18
@JamieLovenduski i see, that's unfortunate. The regex in this answer is designed to match exactly what you provided in the answer and would fail if the input isn't exactly as shown in the question. I Should've made that clear my bad — Santiago Squarzon, Jun 09 '23 at 21:48

Grok42 · Accepted Answer · 2023-06-09T18:47:22.157

2

Blerg. Already has some good-looking answers. Here's a variation using Switch.

Edited to use @mklement0 suggestion of -Regex for Switch

Get-Content -Path "[Path to Logfile]" |
    ForEach-Object {
        switch -Regex ($_) {
            '^(\d+)\s.*Peer:\s(\d+\.\d+\.\d+\.\d+.*\(.*\))$' {
                $ROW = [PSCustomObject]@{
                    Connection = $matches[1]
                    'IKE Peer' = $matches[2]
                }
            }
            '^([A-Z][\w\-_]+)[^\w]+([\w\-_]+)[^\w]+([\w\-_]+)[^\w]+([\w\-_]+)' {
                $ROW | Add-Member -NotePropertyName $matches[1] -NotePropertyValue $matches[2]
                $ROW | Add-Member -NotePropertyName $matches[3] -NotePropertyValue $matches[4]
            }
            '^Lifetime\sRemaining:\s+([0-9]+)$' {
                $ROW | Add-Member -NotePropertyName 'Lifetime Remaining' -NotePropertyValue $matches[1]
                $ROW | Select-Object -Property Connection,'IKE Peer',Type,Rekey,Encrypt,Auth,Role,State,Hash,Lifetime,'Lifetime Remaining' |
                    Export-Csv -Path "[Path to CSV]" -NoTypeInformation -Append
            }
        }
    }

edited Jun 09 '23 at 18:47

answered Jun 09 '23 at 15:53

Grok42

196
4

Hello, I wanted to try your code out as it does the export to csv function. When I attempted to use this It runs into an error saying: `Add-Member : Cannot validate argument on parameter 'NotePropertyName'. The argument is null or empty. Provide an argument that is not null or empty, and then try the command again.` – Jamie Lovenduski Jun 09 '23 at 16:22
Also says this under that: `Add-Member : Cannot add a member with the name "28" because a member with that name already exists. To overwrite the member anyway, add the Force parameter to your command.` – Jamie Lovenduski Jun 09 '23 at 16:23
Sorry. I only tested against the four entries you provided. This Regex may need to be tweaked `':.*:'` if there are lines in your log with two colons that are not shown in your example. – Grok42 Jun 09 '23 at 17:09
@Grok42 No worries, where would you make this tweak at would it be at the `{ $_ -match ':.*:' }` portion. I do not have anything with another colon but just increasing past a single digit in terms of connection. Like my data log file goes to 40 connections so the beginning number will expanded from a single digit to two digits. Thank you – Jamie Lovenduski Jun 09 '23 at 17:29
Just guessing, but maybe one of your connections has a Port # `192.168.8.191:443 (San Leandro)`? You could probably tighten-up the Regex better than this, but for `{ $_ -match ':.*:'`... try `'[A-Z].*:.*:'`. And you would have to replace a `\s` in the capture pattern above it with `.*`. to this... `$_ -match '^(\d+)\s.*Peer:\s(\d+\.\d+\.\d+\.\d+.*$.*$)$' | Out-Null` – Grok42 Jun 09 '23 at 18:10
I fortunately don't have any connections with a port. I did however just test your basic code with using the same 4 connections and it works just fine. However it does not output anything to the csv file. Its just blank – Jamie Lovenduski Jun 09 '23 at 18:26
1

Not sure why it's not exporting CSV content for you. Edited above to use @mklement's suggestion. Also moved Export-Csv within switch statement. Maybe give it a shot? – Grok42 Jun 09 '23 at 18:48

score 0 · Answer 3 · answered Jun 09 '23 at 15:21

Try following :

$filename = 'c:\temp\test.txt'

$reader = [System.IO.StreamReader]::new($filename)
$table = [System.Collections.ArrayList]::new()
$newRowPattern = '^(?<connection>\d+)\s+(?<key>[^:]+):(?<value>.*)'
$pattern = '^(?<key>[^:]+):(?<value>.*)'
While(($line = $reader.ReadLine()) -ne $null)
{
    $match = [regex]::match($line,$newRowPattern)

    if($Match.Success -eq $true)
    {
      $newRow = New-Object -TypeName psobject
      $newRow | Add-Member -NotePropertyName connection -NotePropertyValue $match.Groups['connection'].Value.Trim()
      $newRow | Add-Member -NotePropertyName $match.Groups['key'].Value.Trim() -NotePropertyValue $match.Groups['value'].Value.Trim()
      $table.Add($newRow)  | Out-Null
    } 
    else
    {
       $firstItem = $line.Substring(0,24)
       $match =[regex]::match($firstItem,$pattern)
       if($Match.Success -eq $true) { $newRow | Add-Member -NotePropertyName $match.Groups['key'].Value.Trim() -NotePropertyValue $match.Groups['value'].Value.Trim()}
       $secondItem = $line.Substring(24)
       $match =[regex]::match($secondItem,$pattern)
       if($Match.Success -eq $true) {$newRow | Add-Member -NotePropertyName $match.Groups['key'].Value.Trim() -NotePropertyValue $match.Groups['value'].Value.Trim()}

    }
}
$table | Format-List

Results :

connection         : 1
IKE Peer           : 192.168.8.188 (PHX2)
Type               : L2L
Role               : initiator
Rekey              : no
State              : MM_ACTIVE
Encrypt            : 3des
Hash               : SHA
Auth               : preshared
Lifetime           : 86400
Lifetime Remaining : 5214

connection         : 2
IKE Peer           : 192.168.8.189 (Worcester)
Type               : L2L
Role               : initiator
Rekey              : no
State              : MM_ACTIVE
Encrypt            : aes-256
Hash               : SHA
Auth               : preshared
Lifetime           : 28800
Lifetime Remaining : 5929

connection         : 3
IKE Peer           : 192.168.8.190 (Fresno)
Type               : L2L
Role               : initiator
Rekey              : no
State              : MM_ACTIVE
Encrypt            : aes-256
Hash               : SHA
Auth               : preshared
Lifetime           : 28800
Lifetime Remaining : 6564

connection         : 4
IKE Peer           : 192.168.8.191 (San Leandro)
Type               : L2L
Role               : initiator
Rekey              : no
State              : MM_ACTIVE
Encrypt            : 3des
Hash               : SHA
Auth               : preshared
Lifetime           : 86400
Lifetime Remaining : 7160

As previously discussed [here](https://stackoverflow.com/a/75348106/45375) and [here](https://stackoverflow.com/a/76011282/45375), I am down-voting for unidiomatic PowerShell code that sets a bad example. — mklement0, Jun 09 '23 at 17:06
@mklement0 : You rather want the OP to use something that is not pretty and another solution that doesn't output the CSV. My code is simple and easy to understand and a simple Regex pattern that is self explanatory. My college professors would give you an F and I would get A+ — jdweng, Jun 09 '23 at 20:31
My down-vote was unrelated to any other answers. Your code is the worst of both the following worlds, hence my vote: It combines _unnecessarily low-level .NET API calls_ with _unnecessarily verbose, inefficient, and outdated_ PowerShell techniques. — mklement0, Jun 10 '23 at 00:02
What you are then referring is Power Shell is superior to c#. Powershell is built on the same Net Library as c# and is not compiled. So c# is really more efficient than Powershell which is completely the opposite of what you are saying. — jdweng, Jun 10 '23 at 06:28
I said nothing about superiority and don't think that's a helpful perspective. PowerShell and C# are different languages (PowerShell is also a shell), with different purposes. _Of course_ C#, as a compiled language, is faster, but how is that relevant? The point is that you're not writing PowerShell answers, you're writing C#-in-disguise answers, which misses out on most of what makes PowerShell PowerShell, as argued before in detail [here](https://stackoverflow.com/a/75348106/45375). — mklement0, Jun 10 '23 at 17:50

Parse log file into powershell, then sort into columns to output to CSV

3 Answers3