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](50) NULL,
     [LogName] [varchar](50) NULL,
     [MachineName] [varchar](255) NULL,
     [Message] [varchar](max) NULL,
     [ProviderName] [varchar](255) NULL,
     [RecordID] [bigint] NULL,
     [TaskDisplayName] [varchar](50) 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 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.

EDIT: If you care about speed, check out this post where I write about using wevtutil instead of Get-WinEvent.

Posted in PowerShell, SQL Server, Windows
2 comments on “Importing Windows Forwarded Events into SQL Server using PowerShell
  1. Martin Scott says:

    Hi Chrissie

    Great post. Nicely written and easy to follow (especially for a complete powershell virgin!)

    I did hit the issue of null fields (am in the UK, therefore locale of en-gb) but running the script in powershell 2.0 got around the problem until Microsoft pull their finger out and fix the problem.

    Thanks a lot!
    :)

  2. Ian James says:

    I’d first like to say thanks for this. 1 Year on and it pointed me in the right direction for what I wanted.

    I needed this for server 2008 R2. I changed a few things to fit my needs. But the child was spawned from the parent. So thanks.

    SQL

    CREATE TABLE [dbo].[Events](
    [Id] [int] NULL,
    [RecordID] [varchar](50) NULL,
    [Server] [VarChar] (255) NULL,
    [Message] [text] NULL,
    [Source] [text] 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].[Events]
    (
    [RecordID] ASC,
    [Server] ASC,
    [Source] ASC
    ) WITH (IGNORE_DUP_KEY = ON)
    GO

    PowerShell

    $events = Get-EventLog Security | Select-Object MachineName, Index, InstanceId, Message, Source, TimeGenerated
    $events = Get-EventLog Application | Select-Object MachineName, Index, InstanceId, Message, Source, TimeGenerated
    $events = Get-EventLog “Windows Powershell” | Select-Object MachineName, Index, InstanceId, Message, Source, TimeGenerated
    $events = Get-EventLog System | Select-Object MachineName, Index, InstanceId, Message, Source, TimeGenerated

    $connectionString = “Data Source=;Database=;Persist Security Info=True;User ID=;Password=;”
    $bulkCopy = new-object (“Data.SqlClient.SqlBulkCopy”) $connectionString
    $bulkCopy.DestinationTableName = “Events”
    $dt = New-Object “System.Data.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)
    }

    $bulkCopy.WriteToServer($dt)
    #get-eventlog -list |%{$_.clear()}

    Commented line at end will clear down the event logs on the box should you wish it.

    Once again, thanks. :)

Leave a Reply

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

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">