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.

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.

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 and holds a master's degree in Systems Engineering. Chrissy is also certified in SQL Server, Linux, SharePoint and network security. You can follow her on Twitter at @cl.

Posted in PowerShell, SQL Server, Windows
3 comments on “PowerShell Get-WinEvent Bug Workaround on Windows 2008 R2 Server — Importing Windows Forwarded Events into SQL Server using PowerShell
  1. Peter says:

    Indeed, a lot faster! Thanks for sharing!

  2. Carl says:

    Very interesting set of posts here.
    I've been tasked to keep Server event logs centralized to a type of "Audit" server. Initially I thought dumping the logs to XML with a timestamp to a fileserver would be good but now I'm seeing better approaches.

    I've attempted to tailor your script to suite my needs but my powershell experience is minimal and I'm not managing to achieve what I need.

    I get 2 errors when I initally run your script (one of which I know the cause):

    1) Cannot convert null to type "System.DateTime" (line 44, Char 67)
    2) Exception calling "WriteToServer" winth 1 argument

    Error 2 is down to the SQL connection string which I need to figure out but error 1 is the one I'm stuck with.

    Also, we would like to store every bit of the log and aren't interested in inserting specific columns – could the script be simplified to encompass this or does each column need defining either way?

    Appreciate any assistance you can give with this – Again, awesome post :-)

  3. Kirk says:

    This is awesome. You may be getting the System.DateTime Null message if your xml variable is empty. I had to change a few things to make this work – I think the post is losing a few parts.
    # Grab events from the last 1.5 minutes from the Forwarded Events Collector on DC1

    [xml]$xml = (wevtutil qe Forwardedevents /rd:true /f:RenderedXml /e:Events /q:"*[System[(EventID=4720 or EventID=4722 or EventID=4723 or EventID=4724 or EventID=4725 or EventID=4726 or EventID=4727 or EventID=4728 or EventID=4729 or EventID=4740 or EventID=4741 or EventID=4743 or EventID=4780)]] and *[System[TimeCreated[timediff(@SystemTime) <= 90000]]]")

    # build the sql data connection
    $connectionString = "Data Source=SQLServer;Integrated Security=true;Initial Catalog=Database;"
    $bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString
    $bulkCopy.DestinationTableName = "IT.Events_Gathered"

    # build the datatable
    $dt = New-Object "System.Data.DataTable"
    $null = $dt.Columns.Add("EventID")
    $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")
    $null = $dt.Columns.Add("EventData")
    # populate data table
    if (!$xml.Events.Event)
    echo "xml is Empty"
    echo "xml length: "$xml.length
    if ($xml.Events.Event)
    $xml.Events.Event | ForEach-Object {
    $row = $dt.NewRow()
    $eventID = $_.System.EventID."#text"
    if (!$eventID) { $eventID = $_.System.EventID }
    $row.Item("EventID") = $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
    $Datas = $_.EventData.Data
    foreach($Data in $Datas)
    $strEventData = $strEventData + "<Data Name='" +$Data."Name"+"'>"+$Data."#text"+"</Data>"
    $row.Item("EventData") = $strEventData

    #Need to setup columnmappings for bulkcopy. Not including SQL ID table that is PRI key.
    # same as $bulkCopy.ColumnMappings.Add("EventID","EventID") for each column.
    Foreach($col in $DT.Columns) {$bulkCopy.ColumnMappings.Add($col.ColumnName, $col.ColumnName) }

    # Write to the database!

    Thank You!

Leave a Reply

Your email address will not be published. Required fields are marked *