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:

USE [Test]
GO
      /****** Object:  Table [dbo].[EventViewer]    
      /Script Date: 08/28/2011 08:56:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[EventViewer](
        [Index] [int] NULL,
        [Time] [datetime] NULL,
        [EntryType] [varchar](MAX) NULL,
        [Source] [varchar](MAX) NULL,
        [InstanceID] [varchar](MAX) NULL,
        [Message] [varchar](MAX) NULL
  ) ON [PRIMARY]
GO
  SET ANSI_PADDING OFF
GO
      

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

Get-EventLog -ComputerName YourComputerName -LogName Security -After "22-08-2011" | select index,TimeGenerated,EntryType,Source,InstanceID,Message | Out-DataTable | Write-DataTable -ServerInstance YourServer -Database YourDatabase -TableName EventViewer

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

Measure-Command{ Get-EventLog -ComputerName Vader -LogName Security -After "22-08-2011" | select index,TimeGenerated,EntryType,Source,InstanceID,Message | Out-DataTable | Write-DataTable -ServerInstance Vader -Database Test -TableName EventViewer }

Days                            : 0
Hours                          : 0
Minutes                     : 0
Seconds                     : 1
Milliseconds            : 753
TotalMilliseconds : 1753,707

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

$variable = ( Get-EventLog -ComputerName YourComputer -LogName Security -After "22-08-2011" | select index,TimeGenerated,EntryType,Source,InstanceID,Message); $valuedatatable = Out-DataTable -InputObject $variable ; Write-DataTable -ServerInstance YourServer -Database YourDatabase -TableName EventViewer -Data $valuedatatable

. And how many seconds did that take?

Measure-Command { $variable = ( Get-EventLog -ComputerName YourComputer -LogName Security -After "22-08-2011" | select index,TimeGenerated,EntryType,Source,InstanceID,Message); $valuedatatable = Out-DataTable -InputObject $variable ; Write-DataTable -ServerInstance YourServer -Database YourDatabase -TableName EventViewer -Data $valuedatatable }

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:

USE [Test]
GO
      /****** Object:  Table [dbo].[EventViewer]  
      /Script Date: 08/28/2011 09:21:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[EventViewer](
        [ComputerName] [varchar](50) NULL,
        [Index] [int] NULL,
        [Time] [datetime] NULL,
        [EntryType] [varchar](MAX) NULL,
        [Source] [varchar](MAX) NULL,
        [InstanceID] [varchar](MAX) NULL,
        [Message] [varchar](MAX) NULL
  ) ON [PRIMARY]
GO
  SET ANSI_PADDING OFF
GO
      

Then we can use the Script: