Storing Windows Event Viewer Output in a SQL Server table with PowerShell

My good friend Marcos Freccia (blog | twitter) asked me for a simple and fast way to save the output of running the Get-EventLog cmdlet on a SQL Server table. Well, the quickest and easiest way that I know is to use Chad Miller’s Out-DataTable and Write-DataTable functions, because the Write-dataTable function uses sqlbulkcopy. I took the liberty of tweaking the Write-DataTable function to get the output object via Pipeline, and you can download the modified version over on my blog.

Keep in mind that when you pass the object by pipeline, it will be using the SqlBulkCopy too, but line by line. I will show two variations.

First lets create the table to receive the cmdlet output:

Then let’s populated the table, passing the objects by pipeline (which means that I am inserting the data line by line)

Just as a benchmark, let’s see how long that takes:

On the other hand, let’s populate the table using Write-DataTable with an appropriate set of values.

And how many seconds did that take?

We can clearly see the difference ; the first script took 1753 millisecond’s and the second only took 1192. Looks like it is not only in the SQL Server that line by line operations are evil.

Scaling Out

First we have to add a column to our SQL Server table that stores the computer name and instance ID which you’re applying the Get-EventLog cmdlet to. Remember: because the Write-DataTable cmdlet uses sqlbulkcopy, you need the pass the columns to it in the same order as they occur in the table (as you’ll see below). We’ll also need to store the locations of the servers we want to survey in a flat text file, called servers.txt in this example. So, let’s start by creating the receiving table:

Then we can use the Script:

Simple, Fast and Clean – classic PowerShell.