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
– jdweng, Commented Jun 9, 2023 at 14:14

Grok42 · Accepted Answer · 2023-06-09 18:47:22Z

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 9, 2023 at 18:47

answered Jun 9, 2023 at 15:53

Grok42

2061 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Jamie Lovenduski Over a year ago

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 Over a year ago

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.

Grok42 Over a year ago

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.

Jamie Lovenduski Over a year ago

@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

Grok42 Over a year ago

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?

|

Santiago Squarzon · Accepted Answer · 2023-06-09 15:21:07Z

2

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

answered Jun 9, 2023 at 15:21

Santiago Squarzon

65.5k5 gold badges26 silver badges59 bronze badges

2 Comments

Jamie Lovenduski Over a year ago

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 :)

Santiago Squarzon Over a year ago

@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

jdweng · Accepted Answer · 2023-06-09 15:21:34Z

0

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

answered Jun 9, 2023 at 15:21

jdweng

34.5k3 gold badges17 silver badges19 bronze badges

5 Comments

mklement0 Over a year ago

As previously discussed here and here, I am down-voting for unidiomatic PowerShell code that sets a bad example.

jdweng Over a year ago

@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+

mklement0 Over a year ago

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.

jdweng Over a year ago

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.

mklement0 Over a year ago

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.

Collectives™ on Stack Overflow

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

3 Answers 3

7 Comments

2 Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

2 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related