Importing Windows Forwarded Events into SQL Server using PowerShell

Over the past couple weeks, I’ve looked into a number of ways of parsing and importing Windows Forwarded Events into SQL Server: from using SSIS to LogParser to PowerShell to setting up a linked server to the “Forwarding Events.evtx” file.

Ultimately, the only thing that worked was PowerShell’s Get-WinEvent cmdlet. And then, it only worked in one specific case for me — if the events are collected and parsed on a Windows 2012 server. As of today, there’s an unresolved bug in Get-WinEvent that often results in NULL LevelDisplayName, Message, and TaskDisplayName columns. I copied the exact code below on a Win2k8 R2 server and a Win 8 workstation and ran into the NULLs issue repeatedly. Your results may vary, however, as some users have reported success by tweaking a few things in Win2k8 R2 Server.

So, fire up a Windows 2012 box, setup your SQL Server and let’s get started:

The SQL Part

After looking at the data returned by Get-WinEvent, I found the following columns to be the most useful: ID, LevelDisplayName, LogName, MachineName, Message, ProviderName, RecordID, TaskDisplayName, TimeCreated. Then I created a table using those columns:

CREATE DATABASE EventCollections
GO
USE EventCollections
GO
-- the table name loosely relates to the name of my Win Event Subscription name
CREATE TABLE [dbo].[GeneralEvents]( 
     [Id] [int] NULL,
     [LevelDisplayName] [varchar](255) NULL,
     [LogName] [varchar](255) NULL,
     [MachineName] [varchar](255) NULL,
     [Message] [varchar](max) NULL,
     [ProviderName] [varchar](255) NULL,
     [RecordID] [bigint] NULL,
     [TaskDisplayName] [varchar](255) NULL,
     [TimeCreated] [smalldatetime] NULL
)
-- Create Unique Clustered Index with IGNORE_DUPE_KEY=ON to avoid duplicates in sqlbulk imports
CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex-EventCombo] ON [dbo].[GeneralEvents]
(
     [RecordID] ASC,
     [MachineName] ASC,
     [LogName] ASC
) WITH (IGNORE_DUP_KEY = ON)
GO

In order to avoid duplicates during the hourly imports, I created the table using a unique index with IGNORE_DUP_KEY = ON on 3 columns: RecordID, MachineName and LogName.

Next I had to decide how I’d get the data from PowerShell into SQL Server. After reading up on sqlservercentral.com and technet, I decided on hourly imports using sqlbulkcopy.

The PowerShell Part

Forwarded Events are a tricky thing. For some reason, the way that one would usually filter Get-WinEvent results using FilterHasTable kept returning the result Get-WinEvent : No events were found that match the specified selection criteria. I found a number of others who ran into this issue, too and similar errors occurred when people attempted to use LogParser. After all that, I didn’t have much hope in FilterXML working, but it actually did! So we’re going to use that after we perform our initial import.

Here’s the code for the initial import which gathers ALL events in Forwarded Events.

$events = Get-WinEvent ForwardedEvents |  Select-Object ID, LevelDisplayName, LogName, MachineName, Message, ProviderName, RecordID, TaskDisplayName, TimeCreated  

$connectionString = "Data Source=sqlserver;Integrated Security=true;Initial Catalog=EventCollections;"
$bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString
$bulkCopy.DestinationTableName = "GeneralEvents"
$dt = New-Object "System.Data.DataTable"

# build the datatable
$cols = $events | select -first 1 | get-member -MemberType NoteProperty | select -Expand Name
foreach ($col in $cols)  {$null = $dt.Columns.Add($col)}
  
foreach ($event in $events)
  {
     $row = $dt.NewRow()
     foreach ($col in $cols) { $row.Item($col) = $event.$col }
     $dt.Rows.Add($row)
  }
  
 # Write to the database!
 $bulkCopy.WriteToServer($dt)

You may have noticed that I manually built a datatable instead of using Out-DataTable.ps1, which appears to be a fan favorite. I felt the code above kept things a little more tidy and the performance is still quite good.

Since Event Collection is an on-going thing, you’ll likely want to import them on a regular basis. I built the necessary XML query by right clicking on Forwarded Events in Event Viewer -> Filter Current Log… -> Logged: (Change to one hour) -> Click XML tab at top -> Copy/Paste -> Voila.

Actually, using the syntax of this query, I figured out the syntax for FilterHashTable but having the GUI build my query makes it easy, so I stuck with that. Here is the code for the hourly import that you can setup in Task Scheduler.

# While this script is intended to run on an hourly basis, the filter is set for going back 65 minutes.
# This allows the script to run for 5 minutes without any missing any events. Because we setup the 
# table using the IGNORE_DUPE_KEY = ON, duplicate entries are ignored in the database.

$xml = @'
<QueryList>
  <Query Id="0" Path="ForwardedEvents">
    <Select Path="ForwardedEvents">*[System[TimeCreated[timediff(@SystemTime) &lt;= 3900000]]]</Select>
  </Query>
</QueryList>
'@

$events = Get-WinEvent -FilterXml $xml |  Select-Object ID, LevelDisplayName, LogName, MachineName, Message, ProviderName, RecordID, TaskDisplayName, TimeCreated  

$connectionString = "Data Source=sqlserver;Integrated Security=true;Initial Catalog=EventCollections;"
$bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString
$bulkCopy.DestinationTableName = "GeneralEvents"
$dt = New-Object "System.Data.DataTable"

# build the datatable
$cols = $events | select -first 1 | get-member -MemberType NoteProperty | select -Expand Name
foreach ($col in $cols)  {$null = $dt.Columns.Add($col)}
  
foreach ($event in $events)
  {
     $row = $dt.NewRow()
     foreach ($col in $cols) { $row.Item($col) = $event.$col }
     $dt.Rows.Add($row)
  }
 
# Write to the database!
$bulkCopy.WriteToServer($dt)

With any luck, your SQL output should look something like this:

Woo.

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