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.