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

  6. Paul says:

    Excellent thank you… :)
    For those collecting “Forwarded Events” not in en-us (Get-Culture) should add this to the top of the powershell script to get the message and information data.

    $culture = [System.Threading.Thread]::CurrentThread.CurrentCulture = New-Object “System.Globalization.CultureInfo” “en-US”
    Set-Culture $culture

  7. Ed says:

    The only thing missing from this solution, for me, is having the userID attached to the event. I’ve taken a crack at it but seem to be falling short. Any chance you’ve looked into this?

  8. jim h says:

    Hi Chrissie,



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 *