PowerShell Get-WinEvent Bug Workaround on Windows 2008 R2 Server -- Importing Windows Forwarded Events into SQL Server using PowerShell
This is sort of a continuation of my earlier post, Importing Windows Forwarded Events into SQL Server using PowerShell, where I mentioned that I was unable to get the script to work on Windows 2008 R2 due to a known bug in Get-WinEvents. I had to end up deploying my solution to a Windows 2008 R2 Server and was required to write a workaround -- here it is. As always, I prefer using natively available commands, so I eschewed LogParser and used wevtutil.exe instead.
1# Grab events from the last 65 minutes
2[xml]$xml = (wevtutil /r:dc qe Application /e:Events)
3# build the sql data connection
4$connectionString = "Data Source=SQLSERVER;Integrated Security=true;Initial Catalog=EventCollections;"
5$bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString
6$bulkCopy.DestinationTableName = "Events"
7
8#/q:"*[System[TimeCreated[timediff(@SystemTime) <= 3900000]]]"
9# build the datatable
10$dt = New-Object "System.Data.DataTable"
11$null = $dt.Columns.Add("ID")
12$null = $dt.Columns.Add("LevelDisplayName")
13$null = $dt.Columns.Add("LogName")
14$null = $dt.Columns.Add("MachineName")
15$null = $dt.Columns.Add("Message")
16$null = $dt.Columns.Add("ProviderName")
17$null = $dt.Columns.Add("RecordID")
18$null = $dt.Columns.Add("TaskDisplayName")
19$null = $dt.Columns.Add("TimeCreated")
20
21# populate data table
22$xml.Events.Event | ForEach-Object {
23 $row = $dt.NewRow()
24 $eventID = $_.System.EventID."#text"
25 if (!$eventID) { $eventID = $_.System.EventID }
26 $row.Item("ID") = $eventID
27 $eventlevel = $_.System.Level
28 switch ($eventlevel)
29 {
30 1 {$eventLevel = "Critical"}
31 2 {$eventLevel = "Error"}
32 3 {$eventLevel = "Warning"}
33 4 {$eventLevel = "Information"}
34 }
35 $row.Item("LevelDisplayName") = $eventLevel
36 $row.Item("LogName") = $_.System.Channel
37 $row.Item("MachineName") = $_.System.Computer
38 $row.Item("Message") = $_.RenderingInfo.Message
39 $row.Item("ProviderName") = $_.System.Provider.Name
40 $row.Item("RecordID") = $_.System.EventRecordID
41 $row.Item("TaskDisplayName") = $_.RenderingInfo.Task
42 $row.Item("TimeCreated") = [datetime]$_.System.TimeCreated.SystemTime
43 $dt.Rows.Add($row)
44}
45
46# Write to the database!
47$bulkCopy.WriteToServer($dt)
This code imports events from the last 65 minutes. For the initial import, set $xml to wevtutil.exe qe ForwardedEvents /e:Events. As an aside, I was surprised to see that wevtutil is FAR faster than PowerShell's Get-WinEvent, especially during the initial import of a large logs.
1PS C:\Scripts> Measure-Command {c:\scripts\final-getwinevent.ps1}
2Days : 0
3Hours : 0
4Minutes : 1
5Seconds : 19
6Milliseconds : 293
7Ticks : 792930218
8TotalDays : 0.00091774330787037
9TotalHours : 0.0220258393888889
10TotalMinutes : 1.32155036333333
11TotalSeconds : 79.2930218
12TotalMilliseconds : 79293.0218
13
14PS C:\Scripts> Measure-Command {c:\scripts\final-wevtutil.ps1}
15Days : 0
16Hours : 0
17Minutes : 0
18Seconds : 4
19Milliseconds : 957
20Ticks : 49571333
21TotalDays : 5.73742280092593E-05
22TotalHours : 0.00137698147222222
23TotalMinutes : 0.0826188883333333
24TotalSeconds : 4.9571333
25TotalMilliseconds : 4957.1333
From 79 seconds to 5 for 5500 records! Looks like having to rewrite this was a good thing, after all.