Hi all,

 

I often have to go onsite and go through a customers' eventlogs looking for errors. While the eventvwr does provide for nice filtering it doesn't allow me to sort. I like to use Excel for sorting and making a nice table format. In order to do this however I need to be able to format the eventlogs output in a nice delimited format. Powershell provides the ability (ConvertTo-CSV & Export_CSV) to pipe data out to a CSV file however it 'keeps' the carriage returns and line feeds so that ultimately my delimited csv is a mess when I import it into Excel.

I created the following Powershell script to help me collect x number of days worth of data and have it exported nicely to delimited format.

 

<#

Required data

$logPath : which folder to write the collected data to, may also be in the format "\\fileshare"

$computers : semi-colon delimited list of computers to retreive logs for

$days : how many days worth of logs to collect

#>

$logPath = "C:\Temp" # "\\fileshare"
$log = "Application;System"
$computers = "CNTSQL"
$days = 5

$date = get-date -format M.d.yyyy
$now = get-date
$subtractDays = New-Object System.TimeSpan $days,0,0,0,0
$then = $Now.Subtract($subtractDays)
# Error,Information,FailureAudit,SuccessAudit,Warning
[string] $logName = ""
[string] $eventID = ""
[string] $MachineName = ""
[string] $entryTime = ""
[string] $message = ""
[string] $source = ""
[string] $timeGenerated = ""
[string] $fullentry = ""

Function ExecutePerComputer
{ param ([string] $computers)
# a list of servers for the eventlog collection in semi-colon delimited list
#$compArray
$compArray = $computers.Split(";")
[string] $c = ""
foreach ($c in $compArray)
{
# call GetLog with the servername and the logs to get
Write-Host $c
GetLog $c $log
}
}
Function GetLog
{ param ([string] $server, [string] $logs)
# a list of the logs to return
#$logArray
$logArray = $logs.Split(";")
[string] $l = ""
foreach ($l in $logArray)
{
$systemErrors = Get-EventLog -Computername $server -LogName $l -After $then -Before $now -EntryType Error | select LogName,EventID,MachineName,EntryType,Message,Source,TimeGenerated
#Write-Host $l
foreach ($obj in $systemErrors)
{
$logName = $l
$eventID = $obj.EventID.ToString()
$MachineName = $obj.MachineName
$entryTime = $obj.EntryType
$message = $obj.Message.Replace("`r`n", ",").Replace("`n", ",").Replace("`r", ",")
$source = $obj.Source
$timeGenerated = $obj.TimeGenerated.ToString()

$fullentry = $logName + ";" + $eventID + ";" + $MachineName + ";" + $entryTime + ";" + $message + ";" + $source + ";" + $timeGenerated

WriteToCSV "$logPath\$computers-$log-$date.csv" $fullentry
}
}
}
Function WriteToCSV
{ param ([string] $filePath, [string] $content)

$content | Out-File -FilePath $filePath -Append
}

$File = "LogName;EventID;MachineName;EntryType;Message;Source;TimeGenerated"
WriteToCSV "$logPath\$computers-$log-$date.csv" $File
ExecutePerComputer $computers

 

 Excelsior!

Norman