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

CodePudding user response：

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"

CodePudding user response：

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 with multi-line cells.

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