Creating a CSV with selected values from irregular log file

PowerShell Help
1

I’ve been asked to create a CSV from a log file with clustered output like the example below.

Attempting to 'connect' to endpoint: tcp://localhost:[PORT]
Connected to endpoint: tcp://localhost:[PORT]
Subscribed to topic: "geofence_notify"
[1] Recv event with topic "geofence_notify"
seq: [NUMBER]
timestamp: [UNIX-TIMESTAMP]
op: OP_ADD
topic_seq: [NUMBER]
source_id: [NUMBER]
geofence_notify {
    geofence_event: [EVENT]
    geofence_id: [NUMBER]
    geofence_name: [NAME]
    dwell_time: [NUMBER]
    hashed_sta_mac: [NUMBER]
}

[2] Recv event with topic "geofence_notify"
seq: [NUMBER]
timestamp: [UNIX-TIMESTAMP]
op: OP_ADD
topic_seq: [NUMBER]
source_id: [NUMBER]
geofence_notify {
    geofence_event: [EVENT]
    geofence_id: [NUMBER]
    geofence_name: [NAME]
    dwell_time: [NUMBER]
    hashed_sta_mac: [NUMBER]
}

The output I’m looking for would ideally be something like this:

timestamp; geofence_event; geofence_name; dwell_time; hashed_sta_mac
[DATETIME]; [EVENT]; [NAME]; [NUMBER]; [NUMBER]
[DATETIME]; [EVENT]; [NAME]; [NUMBER]; [NUMBER]

I’ve managed to find Select-String to grab the desired values from the log file and also the way to convert the Unix TimeStamp to human readable DateTime format.

To take the timestamp as an example:

$tStamps = Select-String -Path [LOG FILE] -Pattern "(?<=timestamp: ).*.$" | select -ExpandProperty Matches

$tStamps.Value returns all the Unix timestamps from the log file and I can then convert them to human readable like this:

foreach ($ts in $tStamps){ 
  Get-Date (get-date 01.01.1970).AddSeconds($ts.Value) -Format 'yyyy-MM-dd HH:ss'
}

Everything else is much simpler as I can just modify the -Pattern parameter to grab the right values.

However, I worry I’m not thinking right here as this would give me five arrays with the TimeStamps, Events, Names, Dwell time and hashed MACs, which would then have to be merged somehow to produce the desired CSV.
I don’t know if I should somehow try to grab the values from each separate Recv event with topic “geofence_notify” block, but I’m not sure how I’d go about doing this best.

Any suggestions are welcome. I may be overthinking this to a massive degree… Would not be the first time.

2

… looks for me like the perfect occasion to (re-) watch the following video:

Especially from time code 23:15 …

3

Is the log always that exact format? If so, you could just grab the line numbers of the timestamp lines and then add an offset to get the rest of the data. Something like this:

$log = Get-Content 'E:\Temp\Files\testlog.txt'

$lineNumbers = $log | Select-String -Pattern 'timestamp' | Select-Object -ExpandProperty LineNumber

foreach ($lineNumber in $lineNumbers) {
    $index = $lineNumber - 1
    [PSCustomObject] @{
        timestamp      = ($log[$index] -split ': ')[1]
        geofence_event = ($log[$index + 5] -split ': ')[1]
        geofence_name  = ($log[$index + 7] -split ': ')[1]
        dwell_time     = ($log[$index + 8] -split ': ')[1]
        hashed_sta_mac = ($log[$index + 9] -split ': ')[1]
    } | Export-Csv -Path 'E:\Temp\Files\logAsCSV.csv' -Append -NoTypeInformation
}
4

Sorry for the late reply. Thank you both (again)!

Was a bit of a rush job, so Matt’s solution worked perfectly for just translating/converting a number of files.
Did watch the video from Olaf and should we need a more permanent solution in the future I’ll probably look at rewriting it for a more readable version.