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:
1CREATE DATABASE EventCollections
2GO
3USE EventCollections
4GO
5-- the table name loosely relates to the name of my Win Event Subscription name
6CREATE TABLE [dbo].[GeneralEvents](
7 [Id] [int] NULL,
8 [LevelDisplayName] [varchar](255) NULL,
9 [LogName] [varchar](255) NULL,
10 [MachineName] [varchar](255) NULL,
11 [Message] [varchar](max) NULL,
12 [ProviderName] [varchar](255) NULL,
13 [RecordID] [bigint] NULL,
14 [TaskDisplayName] [varchar](255) NULL,
15 [TimeCreated] [smalldatetime] NULL
16)
17-- Create Unique Clustered Index with IGNORE_DUPE_KEY=ON to avoid duplicates in sqlbulk imports
18CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex-EventCombo] ON [dbo].[GeneralEvents]
19(
20 [RecordID] ASC,
21 [MachineName] ASC,
22 [LogName] ASC
23) WITH (IGNORE_DUP_KEY = ON)
24GO
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.
1$events = Get-WinEvent ForwardedEvents | Select-Object ID, LevelDisplayName, LogName, MachineName, Message, ProviderName, RecordID, TaskDisplayName, TimeCreated
2
3$connectionString = "Data Source=sqlserver;Integrated Security=true;Initial Catalog=EventCollections;"
4$bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString
5$bulkCopy.DestinationTableName = "GeneralEvents"
6$dt = New-Object "System.Data.DataTable"
7
8# build the datatable
9$cols = $events | select -first 1 | get-member -MemberType NoteProperty | select -Expand Name
10foreach ($col in $cols) {$null = $dt.Columns.Add($col)}
11
12foreach ($event in $events)
13 {
14 $row = $dt.NewRow()
15 foreach ($col in $cols) { $row.Item($col) = $event.$col }
16 $dt.Rows.Add($row)
17 }
18
19 # Write to the database!
20 $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.
1# While this script is intended to run on an hourly basis, the filter is set for going back 65 minutes.
2# This allows the script to run for 5 minutes without any missing any events. Because we setup the
3# table using the IGNORE_DUPE_KEY = ON, duplicate entries are ignored in the database.
4
5$xml = @'
6<QueryList>
7 <Query Id="0" Path="ForwardedEvents">
8 <Select Path="ForwardedEvents">*[System[TimeCreated[timediff(@SystemTime) <= 3900000]]]</Select>
9 </Query>
10</QueryList>
11'@
12
13$events = Get-WinEvent -FilterXml $xml | Select-Object ID, LevelDisplayName, LogName, MachineName, Message, ProviderName, RecordID, TaskDisplayName, TimeCreated
14
15$connectionString = "Data Source=sqlserver;Integrated Security=true;Initial Catalog=EventCollections;"
16$bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString
17$bulkCopy.DestinationTableName = "GeneralEvents"
18$dt = New-Object "System.Data.DataTable"
19
20# build the datatable
21$cols = $events | select -first 1 | get-member -MemberType NoteProperty | select -Expand Name
22foreach ($col in $cols) {$null = $dt.Columns.Add($col)}
23
24foreach ($event in $events)
25 {
26 $row = $dt.NewRow()
27 foreach ($col in $cols) { $row.Item($col) = $event.$col }
28 $dt.Rows.Add($row)
29 }
30
31# Write to the database!
32$bulkCopy.WriteToServer($dt)
With any luck, your SQL output should look something like this:
Woo.