Monday, September 28, 2015

Importing Windows Event Log into SQL Server

LogParser is your friend: http://www.microsoft.com/en-us/download/details.aspx?id=24659

Here is a sample command to import the data into SQL Server:

LogParser -i:EVT "SELECT * into prodEvents FROM  c:\temp\prod.evtx" -o:SQL -server:sqlServerName -driver:"SQL Server" -database:testDb -createtable:ON  -cleartable:ON -transactionRowCount:-1  -maxstrfieldlen:8000

note: use -username:test -password:test to specify username and password if you need to.
note: I cant seem to find a way around the 8000 character limit in LogParser. So it always truncates at 8000 characters :(

The table definition is:

CREATE TABLE [dbo].[prodEvents](
    [EventLog] [varchar](8000) NULL,
    [RecordNumber] [int] NULL,
    [TimeGenerated] [datetime] NULL,
    [TimeWritten] [datetime] NULL,
    [EventID] [int] NULL,
    [EventType] [int] NULL,
    [EventTypeName] [varchar](8000) NULL,
    [EventCategory] [int] NULL,
    [EventCategoryName] [varchar](8000) NULL,
    [SourceName] [varchar](8000) NULL,
    [Strings] [varchar](8000) NULL,
    [ComputerName] [varchar](8000) NULL,
    [SID] [varchar](8000) NULL,
    [Message] [varchar](8000) NULL,
    [Data] [varchar](8000) NULL
) ON [PRIMARY]

Keep in mind that you can run many SQL like queries directly against the EVT file locally on your machine without importing the data into a table.

Other useful queries:

logparser.exe -i:evt "select * from c:\temp\prod.evtx where timegenerated > '2015-09-01 00:00:00'"

More info:

Useful commands and tips: (for ASP.net, but useful even for Event Logs) https://support.microsoft.com/en-us/kb/910447