{"id":3394,"date":"2011-08-31T18:31:00","date_gmt":"2011-08-31T18:31:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/storing-windows-event-viewer-output-in-a-sql-server-table-with-powershell\/"},"modified":"2017-09-28T15:41:57","modified_gmt":"2017-09-28T15:41:57","slug":"storing-windows-event-viewer-output-in-a-sql-server-table-with-powershell","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/storing-windows-event-viewer-output-in-a-sql-server-table-with-powershell\/","title":{"rendered":"Storing Windows Event Viewer Output in a SQL Server table with PowerShell"},"content":{"rendered":"

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

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.<\/i><\/p>\n

First lets create the table to receive the cmdlet output:<\/p>\n

USE [Test]\r\nGO\r\n      \/****** Object:  Table [dbo].[EventViewer]    \r\n      \/Script Date: 08\/28\/2011 08:56:09 ******\/\r\nSET ANSI_NULLS ON\r\nGO\r\nSET QUOTED_IDENTIFIER ON\r\nGO\r\nSET ANSI_PADDING ON\r\nGO\r\nCREATE TABLE [dbo].[EventViewer](\r\n        [Index] [int] NULL,\r\n        [Time] [datetime] NULL,\r\n        [EntryType] [varchar](MAX) NULL,\r\n        [Source] [varchar](MAX) NULL,\r\n        [InstanceID] [varchar](MAX) NULL,\r\n        [Message] [varchar](MAX) NULL\r\n  ) ON [PRIMARY]\r\nGO\r\n  SET ANSI_PADDING OFF\r\nGO\r\n<\/pre>\n
Then let’s populated the table, passing the objects by pipeline (which means that I am inserting the data line by line)<\/p>\n
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  \r\n<\/pre>\n
Just as a benchmark, let’s see how long that takes:<\/p>\n
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 }\r\n<\/pre>\n
Days              : 0\r\nHours             : 0\r\nMinutes           : 0\r\nSeconds           : 1\r\nMilliseconds      : 753\r\nTotalMilliseconds : 1753,707\r\n<\/pre>\n
On the other hand, let’s populate the table using Write-DataTable with an appropriate set of values.<\/p>\n
$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  \r\n<\/pre>\n
And how many seconds did that take?<\/p>\n
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 }  \r\n<\/pre>\n
Days               : 0\r\nHours              : 0\r\nMinutes            : 0\r\nSeconds            : 1\r\nMilliseconds       : 192\r\nTotalMilliseconds  : 1192,0523  \r\n<\/pre>\n
We can clearly see the difference ; the first script took 1753 millisecond’s and the second only took 1192<\/i>. Looks like it is not only in the SQL Server that line by line operations are evil.<\/p>\n
Scaling Out<\/h3>\n
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<\/b> cmdlet to. Remember: because the Write-DataTable<\/b> cmdlet uses sqlbulkcopy<\/b>, 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<\/b> in this example. So, let’s start by creating the receiving table:<\/p>\n
USE [Test]\r\nGO\r\n      \/****** Object:  Table [dbo].[EventViewer]  \r\n      \/Script Date: 08\/28\/2011 09:21:24 ******\/\r\nSET ANSI_NULLS ON\r\nGO\r\nSET QUOTED_IDENTIFIER ON\r\nGO\r\nSET ANSI_PADDING ON\r\nGO\r\nCREATE TABLE [dbo].[EventViewer](\r\n        [ComputerName] [varchar](50) NULL,\r\n        [Index] [int] NULL,\r\n        [Time] [datetime] NULL,\r\n        [EntryType] [varchar](MAX) NULL,\r\n        [Source] [varchar](MAX) NULL,\r\n        [InstanceID] [varchar](MAX) NULL,\r\n        [Message] [varchar](MAX) NULL\r\n  ) ON [PRIMARY]\r\nGO\r\n  SET ANSI_PADDING OFF\r\nGO\r\n<\/pre>\n
Then we can use the Script:<\/p>\n
foreach ($server in Get-Content c:tempservers.txt) { $variable = ( Get-EventLog -ComputerName $server -LogName Security -After \"22-08-2011\" | select @{Expression={$($server) };Label = \"ComputerName\"} ,index,TimeGenerated,EntryType,Source,InstanceID,Message ) $valuedatatable = Out-DataTable -InputObject $variable Write-DataTable -ServerInstance YourServer -Database YourDatabase -TableName EventError -data $valuedatatable  \r\n<\/pre>\n
Simple, Fast and Clean – classic PowerShell.<\/p>\n
#PowerShellLifeStyle<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"
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……<\/p>\n","protected":false},"author":221715,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[6819],"class_list":["post-3394","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/3394","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221715"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=3394"}],"version-history":[{"count":6,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/3394\/revisions"}],"predecessor-version":[{"id":73550,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/3394\/revisions\/73550"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=3394"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=3394"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=3394"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=3394"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}