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.

# Grab events from the last 65 minutes
[xml]$xml = (wevtutil  /r:dc qe Application /e:Events)
# build the sql data connection
$connectionString = "Data Source=SQLSERVER;Integrated Security=true;Initial Catalog=EventCollections;"
$bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString
$bulkCopy.DestinationTableName = "Events"

#/q:"*[System[TimeCreated[timediff(@SystemTime) <= 3900000]]]" 
# build the datatable
$dt = New-Object "System.Data.DataTable"
$null = $dt.Columns.Add("ID")
$null = $dt.Columns.Add("LevelDisplayName")
$null = $dt.Columns.Add("LogName")
$null = $dt.Columns.Add("MachineName")
$null = $dt.Columns.Add("Message")
$null = $dt.Columns.Add("ProviderName")
$null = $dt.Columns.Add("RecordID")
$null = $dt.Columns.Add("TaskDisplayName")
$null = $dt.Columns.Add("TimeCreated")

# populate data table
$xml.Events.Event | ForEach-Object {
   $row = $dt.NewRow()  
      $eventID = $_.System.EventID."#text"
      if (!$eventID) { $eventID = $_.System.EventID }
      $row.Item("ID") = $eventID  
      $eventlevel = $_.System.Level
            switch ($eventlevel) 
             { 
                  1 {$eventLevel = "Critical"} 
                  2 {$eventLevel = "Error"} 
                  3 {$eventLevel = "Warning"} 
                  4 {$eventLevel = "Information"} 
             }
      $row.Item("LevelDisplayName") = $eventLevel
      $row.Item("LogName") = $_.System.Channel
      $row.Item("MachineName") = $_.System.Computer
      $row.Item("Message") = $_.RenderingInfo.Message
      $row.Item("ProviderName") = $_.System.Provider.Name
      $row.Item("RecordID") = $_.System.EventRecordID
      $row.Item("TaskDisplayName") = $_.RenderingInfo.Task
      $row.Item("TimeCreated") =  [datetime]$_.System.TimeCreated.SystemTime
   $dt.Rows.Add($row)
}
  
# Write to the database!
$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.

PS C:\Scripts> Measure-Command {c:\scripts\final-getwinevent.ps1}
Days              : 0
Hours             : 0
Minutes           : 1
Seconds           : 19
Milliseconds      : 293
Ticks             : 792930218
TotalDays         : 0.00091774330787037
TotalHours        : 0.0220258393888889
TotalMinutes      : 1.32155036333333
TotalSeconds      : 79.2930218
TotalMilliseconds : 79293.0218


PS C:\Scripts> Measure-Command {c:\scripts\final-wevtutil.ps1}
Days              : 0
Hours             : 0
Minutes           : 0
Seconds           : 4
Milliseconds      : 957
Ticks             : 49571333
TotalDays         : 5.73742280092593E-05
TotalHours        : 0.00137698147222222
TotalMinutes      : 0.0826188883333333
TotalSeconds      : 4.9571333
TotalMilliseconds : 4957.1333

From 79 seconds to 5 for 5500 records! Looks like having to rewrite this was a good thing, after all.

Chrissy is a Cloud and Datacenter Management & Data Platform MVP who has worked in IT for over 20 years. She is the creator of the popular SQL PowerShell module dbatools, holds a master's degree in Systems Engineering and is coauthor of Learn dbatools in a Month of Lunches. Chrissy is certified in SQL Server, Linux, SharePoint and network security. You can follow her on Twitter at @cl.

Posted in PowerShell, SQL Server, Windows