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:

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 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.

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.

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


Chrissy is a PowerShell MVP who has worked in IT for nearly 20 years, and currently serves as a Sr. Database Engineer in Belgium. Always an avid scripter, she attended the Monad session at Microsoft’s Professional Developers Conference in Los Angeles back in 2005 and has worked and played with PowerShell ever since. Chrissy is currently pursuing an MS in Systems Engineering at Regis University and helps maintain in her spare time. She holds a number of certifications, including those relating to SQL Server, SuSE Linux, SharePoint and network security. She recently became co-lead of the SQL PASS PowerShell Virtual Chapter. You can follow her on Twitter at @cl.

Posted in PowerShell, SQL Server, Windows
5 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.


    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


    $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 }

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

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

    Once again, thanks. :)

  3. JamesB says:

    Hi Chrissie

    Thanks for a well thought out and documented article!!

    I am very new to Powershell and would like to beg some assistance with an issue I am having with the script after I changed it a bit to capture the System and Application logs.

    Mostly it run fine, but it happens that there are no Application log entries when I execute the script from time to time, it then errors in the part under “# build the datatable” in your script.

    The error: The corresponding line is ‘$cols = $events | select -first 1 | get-member -MemberType NoteProperty | select -Expand Name’. Correct the script and reschedule the job. The error information returned by PowerShell is: ‘No object has been specified to the get-member cmdlet. ‘ A job step received an error at line 11 in a PowerShell script. The corresponding line is ‘foreach ($col in $cols) {$null = $dt.Columns.Add($col)}’. Correct the script and reschedule the job. The error information returned by PowerShell is: ‘Exception calling “Add” with “1” argument(s): “‘column’ argument cannot be null. Parameter name: column” ‘. Process Exit Code -1. The step failed.

    Can you please assist with the code needed if we get an empty resultset?

    Thanks again and keep up the good work.


  4. Chris says:

    Expand column LogName from 50 to 255 chars, because some event logs has larger name.


  5. Anderson says:

    Interesting script but any way to export millions of events. I´m trying to export more than 7 millions of events and ONLY get time out and high memory consumption. Never ends

    There must be an option to optimize the export when you are using a variable to store all events (a batch size or stuff like that).

    Regards and thanks

2 Pings/Trackbacks for "Importing Windows Forwarded Events into SQL Server using PowerShell"
  1. […] am using the solution documented here to create a SQL database and then write the data to it. The solution comprises two parts. First, a […]

Leave a Reply

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