Load Eventviewer files (evtx) to SQL Server Table with PowerShell


We can use Get-WinEvent to get all information.
Two methods have been tested to load the information into the prepared [MyLogEvents] table.
Both works fine but I would use the two steps to load data into the table with bcp method for the speed.

Method 1 is to use bcp command;
Method 2 is to use PoserShell user functions.

First we need to create a table to hold data:

 --Create a table
 /*
 
CREATE TABLE [dbo].[MyLogEvents]
  (
     [RecordID] [BIGINT] NULL
	 ,[TaskDisplayName] [VARCHAR](50) NULL
	 ,[TimeCreated] [SMALLDATETIME] NULL
	 ,[ProviderName] [VARCHAR](255) NULL
	 ,id INT NULL
	 ,[Message] [VARCHAR](max) NULL
  ) 


*/

 

The details of the two methods.

Method 1: (PowerShell part)

$myFilelist= “c:\temp\logs\ProdAD01sec08-11-2014@08-00-02.evtx”, “c:\temp\logs\ProdAD01sec08-11-2014@09-00-02.evtx”, “c:\temp\logs\ProdAD01sec08-11-2014@10-00-02.evtx”,”c:\temp\logs\ProdAD01sec08-11-2014@11-00-02.evtx”, “c:\temp\logs\ProdAD01sec08-11-2014@12-00-02.evtx”, “c:\temp\logs\ProdAD01sec08-11-2014@13-00-03.evtx”, “c:\temp\logs\ProdAD01sec08-11-2014@14-00-03.evtx”, “c:\temp\logs\ProdAD01sec08-11-2014@15-00-02.evtx”
Get-WinEvent -Path $myFileList | select RecordID,TaskDisplayName,TimeCreated, ProviderName, Id, @{n=’Message’;e={$_.Message -replace ‘\s+’, ” “}} | Export-Csv “c:\temp\logs\Logging.csv”

(T-SQL part) bcp:


use myDB;

 --1.1Generate XML format file 
 exec master..xp_cmdshell  'bcp  myDB.[dbo].[MyLogEvents] format nul -c -x -f  C:\temp\logs\myFormatFile.xml -T  -t\, -S myMachine\MSSQL2014';
  
--Step 2
--BCP or BUlk Insert

----bcp in with all data
exec master..xp_cmdshell  'bcp myDB.[dbo].[MyLogEvents] IN "C:\temp\logs\Logging1.csv" -f "C:\temp\logs\myFormatFile.xml" -F 2 -b 100000 -T -S myMachine\MSSQL2014';

go


 

Method 2:
PowerShell script:

.”c:\temp\myinclude.ps1″
$myFilelist= “c:\temp\logs\ProdAD01sec08-11-2014@08-00-02.evtx”, “c:\temp\logs\ProdAD01sec08-11-2014@09-00-02.evtx”, “c:\temp\logs\ProdAD01sec08-11-2014@10-00-02.evtx”,”c:\temp\logs\ProdAD01sec08-11-2014@11-00-02.evtx”, “c:\temp\logs\ProdAD01sec08-11-2014@12-00-02.evtx”, “c:\temp\logs\ProdAD01sec08-11-2014@13-00-03.evtx”, “c:\temp\logs\ProdAD01sec08-11-2014@14-00-03.evtx”, “c:\temp\logs\ProdAD01sec08-11-2014@15-00-02.evtx”
$events = Get-WinEvent -Path $myFileList | select RecordID,TaskDisplayName,TimeCreated, ProviderName, Id, @{n=’Message’;e={$_.Message -replace ‘\s+’, ” “}} | Out-DataTable | Write-DataTable -ServerInstance myMachine\MSSQL2014 -Database myDB -TableName MyLogEvents

References:

http://shellyourexperience.com/2011/08/25/quick-tipstoring-get-eventlog-output-into-a-sql-server-table/
http://stackoverflow.com/questions/23282462/powershell-parse-get-winevent-into-csv-with-headers-including-all-child-objects
http://www.gsx.com/blog/bid/81096/Enhance-your-PowerShell-experience-by-automatically-loading-scripts
http://blogs.technet.com/b/heyscriptingguy/archive/2010/08/10/how-to-reuse-windows-powershell-functions-in-scripts.aspx

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s