{"id":134,"date":"2016-03-29T02:00:10","date_gmt":"2016-03-29T02:00:10","guid":{"rendered":"http:\/\/eventlogxp.com\/blog\/?p=134"},"modified":"2016-03-30T02:09:07","modified_gmt":"2016-03-30T02:09:07","slug":"exporting-event-logs-with-windows-powershell","status":"publish","type":"post","link":"https:\/\/eventlogxp.com\/blog\/exporting-event-logs-with-windows-powershell\/","title":{"rendered":"Exporting event logs with Windows PowerShell"},"content":{"rendered":"

Do you need to automate error reporting based on recent events and don’t want to use third-party tools? This article describes how to collect events from different sources and unite them in one document using standard Windows instruments only.<\/em><\/p>\n

Recently I described how to export events into Excel format using our Event Log Explorer software<\/a>. However, in some cases, using third-party software can be impossible. This may happen if your company doesn’t have budget to purchase event log utilities, or such utilities are restricted by the company’s rules. In any case, the task of regular exporting the recent events from different machines into one legible file is still crucial. That’s why I will show how you can get the events from different Windows machines and export them into one file for further investigation.<\/p>\n

Task<\/h3>\n

Let’s take the same task we solved previously. We have 4 Windows servers and we need to generate weekly reports of Error and Warning events in Application and System event logs. We should utilize only standard Windows instruments.<\/p>\n

Instruments<\/h3>\n

Microsoft features Windows PowerShell as a framework to automate different administrative tasks and perform configuration management in Windows. My scripts require at least PowerShell version 3.0. If your PowerShell is outdated, you can update it by downloading Windows Management Framework from Microsoft’s site. To check PowerShell version simply type in PowerShell console:<\/p>\n

$PSVersionTable.PSVersion<\/p><\/blockquote>\n

\"Getting<\/a><\/p>\n

In my case, PowerShell version = 3 which is OK.<\/p>\n

Research<\/h3>\n

To access event logs, Windows PowerShell comes with Get-EventLog cmdlet:<\/p>\n

Parameter Set: LogName\r\nGet-EventLog [-LogName] <String> [[-InstanceId] <Int64[]> ] \r\n[-After <DateTime> ] [-AsBaseObject] [-Before <DateTime> ] \r\n[-ComputerName <String[]> ] [-EntryType <String[]> ] \r\n[-Index <Int32[]> ] [-Message <String> ] [-Newest <Int32> ] \r\n[-Source <String[]> ] [-UserName <String[]> ] [<CommonParameters>]\r\n<\/pre>\n
First we need to define the start date (the date after which we will get events). This date is calculated as today minus 7 days:<\/p>\n
$now=get-date
\n$startdate=$now.adddays(-7)<\/p><\/blockquote>\n
Now we can read warning and error events from a log for the last week:<\/p>\n
$el = get-eventlog -ComputerName Serv1 -log System -After $startdate -EntryType Error, Warning<\/p><\/blockquote>\n
Let’s check the result. Just type $el in the console. Yes, we can see events from the event log.
\nBut how will we export the event log? Windows PowerShell doesn’t have cmdlets to export to Excel. But it supports export to CSV file. Let’s try it now:<\/p>\n
$el | export-csv eventlog.csv<\/p><\/blockquote>\n
Yes, it works, but multi-line descriptions ruined the output file.
\nMaybe export to XML will help?<\/p>\n
$el | export-clixml eventlog.xml<\/p><\/blockquote>\n
But how to display it in clear way? Excel understands XML files, but I have no idea how to interpret it:<\/p>\n
\"PowerShell<\/a><\/p>\n
I guess we can make an XML transformation to convert this XML into more readable file, but I’m not an XML guru, but I have a more or less useful solution. We can solve our problem if we just export to CSV only several event properties (without event description):<\/p>\n
$el |Select EntryType, TimeGenerated, Source, EventID | Export-CSV eventlog.csv -NoTypeInfo<\/p><\/blockquote>\n
Now we can read eventlog.csv in Excel without problems.<\/p>\n
Putting all together<\/h3>\n
It’s time to write the PowerShell script.
\nBrief: we will read recent (7 days) error and warning events from Application and System event logs, join them, sort them by time and export to CSV format.<\/p>\n
#\r\n#  This script exports consolidated and filtered event logs to CSV\r\n#  Author: Michael Karsyan, FSPro Labs, eventlogxp.com (c) 2016\r\n#\r\n\r\nSet-Variable -Name EventAgeDays -Value 7     #we will take events for the latest 7 days\r\nSet-Variable -Name CompArr -Value @(\"SERV1\", \"SERV2\", \"SERV3\", \"SERV4\")   # replace it with your server names\r\nSet-Variable -Name LogNames -Value @(\"Application\", \"System\")  # Checking app and system logs\r\nSet-Variable -Name EventTypes -Value @(\"Error\", \"Warning\")  # Loading only Errors and Warnings\r\nSet-Variable -Name ExportFolder -Value \"C:\\TEST\\\"\r\n\r\n\r\n$el_c = @()   #consolidated error log\r\n$now=get-date\r\n$startdate=$now.adddays(-$EventAgeDays)\r\n$ExportFile=$ExportFolder + \"el\" + $now.ToString(\"yyyy-MM-dd---hh-mm-ss\") + \".csv\"  # we cannot use standard delimiteds like \":\"\r\n\r\nforeach($comp in $CompArr)\r\n{\r\n  foreach($log in $LogNames)\r\n  {\r\n    Write-Host Processing $comp\\$log\r\n    $el = get-eventlog -ComputerName $comp -log $log -After $startdate -EntryType $EventTypes\r\n    $el_c += $el  #consolidating\r\n  }\r\n}\r\n$el_sorted = $el_c | Sort-Object TimeGenerated    #sort by time\r\nWrite-Host Exporting to $ExportFile\r\n$el_sorted|Select EntryType, TimeGenerated, Source, EventID, MachineName | Export-CSV $ExportFile -NoTypeInfo  #EXPORT\r\nWrite-Host Done!\r\n<\/code><\/pre>\n
Scheduling the task<\/h3>\n
To run the script, we should run this command:<\/p>\n
PowerShell.exe -ExecutionPolicy ByPass -File export-logs.ps1<\/p><\/blockquote>\n
We can open Windows scheduler GUI to make this task, or use PowerShell console:
\nMicrosoft recommends this way to schedule a PowerShell script:<\/p>\n
$Trigger=New-JobTrigger -Weekly -At “7:00AM” -DaysOfWeek “Monday”
\nRegister-ScheduledJob -Name “Export Logs” -FilePath “C:\\Test\\export-logs.ps1” -Trigger $Trigger<\/p><\/blockquote>\n
But this may miswork, because it adds to Windows Task Scheduler the following action:<\/p>\n
powershell.exe -NoLogo -NonInteractive -WindowStyle Hidden -Command “Import-Module PSScheduledJob; $jobDef = [Microsoft.PowerShell.ScheduledJob.ScheduledJobDefinition]::LoadFromStore(‘Export Logs’, ‘C:\\Users\\Michael\\AppData\\Local\\Microsoft\\Windows\\PowerShell\\ScheduledJobs’); $jobDef.Run()”<\/p><\/blockquote>\n
If your policy prevents running PoweShell scripts, our export script won’t run because powershell parameters miss -ExecutionPolicy option.
\nThat’s why I will use ScriptBlock instead of FilePath. This code does the trick:<\/p>\n
$trigger=New-JobTrigger -Weekly -At \"7:00AM\" -DaysOfWeek \"Monday\"\r\n$action=\"PowerShell.exe -ExecutionPolicy ByPass -File c:\\test\\export-logs.ps1\"\r\n$sb=[Scriptblock]::Create($action)\r\nRegister-ScheduledJob -Name \"Export Logs\" -ScriptBlock $sb -Trigger $trigger\r\n<\/code><\/pre>\n
Note that to run Register-ScheduledJob cmdlet, you need to start PowerShell elevated.
\nThat’s all. Now you should have a task that runs every Monday at 7:00, collects events from your servers and exports them to CSV files.<\/p>\n
Conclusion<\/h3>\n
As you can see, the problem of exporting events to Excel can be solved without third-party tools. This method is somewhat limited, but it works.<\/p>\n\"Facebook\"<\/a>\"twitter\"<\/a>\"reddit\"<\/a>\"pinterest\"<\/a>\"linkedin\"<\/a>\"mail\"<\/a>","protected":false},"excerpt":{"rendered":"
Do you need to automate error reporting based on recent events and don’t want to use third-party tools? This article describes how to collect events from different sources and unite them in one document using standard Windows instruments only. Recently I described how to export events into Excel format using our Event Log Explorer software. However, in some cases, using third-party software can be impossible.\u2026 Read More »<\/a><\/span><\/p>\n","protected":false},"author":2,"featured_media":142,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[39],"tags":[38,54,56,53,50,55],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/eventlogxp.com\/blog\/wp-json\/wp\/v2\/posts\/134"}],"collection":[{"href":"https:\/\/eventlogxp.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/eventlogxp.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/eventlogxp.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/eventlogxp.com\/blog\/wp-json\/wp\/v2\/comments?post=134"}],"version-history":[{"count":6,"href":"https:\/\/eventlogxp.com\/blog\/wp-json\/wp\/v2\/posts\/134\/revisions"}],"predecessor-version":[{"id":143,"href":"https:\/\/eventlogxp.com\/blog\/wp-json\/wp\/v2\/posts\/134\/revisions\/143"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/eventlogxp.com\/blog\/wp-json\/wp\/v2\/media\/142"}],"wp:attachment":[{"href":"https:\/\/eventlogxp.com\/blog\/wp-json\/wp\/v2\/media?parent=134"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/eventlogxp.com\/blog\/wp-json\/wp\/v2\/categories?post=134"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/eventlogxp.com\/blog\/wp-json\/wp\/v2\/tags?post=134"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}