Export event log to csv

Question

I have this simple script to backup events related to certification authority activities in the security log.

if ((Get-Date).TimeOfDay.TotalDays -lt 0.5) {$Time = "night"} else {$Time = "day"}
$Date = (Get-Date).Year.ToString()+"-"+(Get-Date).Month.ToString()+"-"+(Get-Date).Day.ToString()
Get-EventLog security | ?{$_.CategoryNumber -match 12805} | fl | Out-File C:\CALogs\CAlog-$Date-$Time.log

The script is run each midnight and noon since the security log gets overwritten in about a day. It works perfectly fine, but now I'd like to export the results to csv file. It theoretically works but the Message item of the event, since it's split in several lines, breaks down the results so that it's unusable. Can I somehow encase the Message in a quotation marks or anything so it is considered one "box" by the csv converter? The last line of the script when modified for csv export so far looks like this:

Get-EventLog security | ?{$_.CategoryNumber -match 12805} | Export-Csv -Path $PSScriptRoot\CAlog-$Date-$Time.csv

Well, yes it does, but when you open it in Excel and want to transfer it to easily readable file, where each cell has its own value (with whole message in one cell as well), it's unusable. — Petr Synek, Aug 15 '22 at 13:19
Try adding switches `-UseCulture` and `-NoTypeInformation` to the Export-Csv line — Theo, Aug 15 '22 at 13:26
... and note that Excel is not synonymous with CSV, and (IMO) Excel tends to be rather terrible with CSV files. — BigBen, Aug 15 '22 at 14:49
Might be a duplicate of this. It has 32 answers, lol. https://stackoverflow.com/questions/2668678/importing-csv-with-line-breaks-in-excel-2007 Or `| % { $_.message = $_.message -replace '\n',' '; $_ }` — js2010, Aug 15 '22 at 15:11

score 0 · Answer 1 · answered Aug 15 '22 at 14:27

It seems like the issue is Encoding, try using the -Encoding parameter and use BigEndianUnicode or ASCII. ASCII works fine for English, but BigEndianUnicode works better for some other languages. I'd also add the switches Theo mentioned, -UseCulture and -NoTypeInformation, but without that it'd be:

Get-EventLog security | ?{$_.CategoryNumber -match 12805} | Export-Csv -Path $PSScriptRoot\CAlog-$Date-$Time.csv -Encoding ASCII

Bonus!:

$Date = (Get-Date).Year.ToString()+"-"+(Get-Date).Month.ToString()+"-"+(Get-Date).Day.ToString()

This can be simplified to:

$Date = Get-Date -Format "yyyy-M-d"

js2010 · Answer 2 · 2022-08-15T19:02:08.213

0

Here's an example, converting message to one line, with spaces instead of newlines. Even export-excel would put it all on one line, and a csv made with excel couldn't be imported properly with multi-line cells.

get-winevent application | select -first 5 | 
  % { $_.message = $_.message -replace '\n',' '; $_ } | export-csv file.csv
.\file.csv  # open in excel

edited Aug 15 '22 at 19:02

answered Aug 15 '22 at 16:22

js2010

23,033
6
64
66

Export event log to csv

2 Answers2