Log Parser and SQL

I thought everyone who wants to know how to put the windows events or IIS Logs to SQL already know about it. But for my amazement it is not the case. Quick search on the web did not turned up a short tutorial. So how to move my window events to SQL? Here it is

 

Install the Log Parser on the box.

Get the Log Parser from http://www.microsoft.com/downloads/details.aspx?FamilyID=890cd06b-abf8-4c25-91b2-f8d975cf8c07&displaylang=en

 

Create a database and a table in SQL.

 

Run the following Query

 

Use master

Go

Create Database MyEventDataBase

Go

 

use MyEventDataBase

go

CREATE TABLE [Audit] (

      [EventLog] [varchar] (255) NULL ,

      [RecordNumber] [int] NULL ,

      [TimeGenerated] [datetime] NULL ,

      [TimeWritten] [datetime] NULL ,

      [EventID] [int] NULL ,

      [EventType] [int] NULL ,

      [EventTypeName] [varchar] (255) NULL ,

      [EventCategory] [int] NULL ,

      [EventCategoryName] [varchar] (255) NULL ,

      [SourceName] [varchar] (255) NULL ,

      [Strings] [varchar] (max) NULL ,

      [ComputerName] [varchar] (255) NULL ,

      [SID] [varchar] (255) NULL ,

      [Message] [varchar] (max) NULL ,

      [Data] [varchar] (max) NULL

) 

GO

 

 

Make a directory called LogImport in C drive.

 

Make a bat file with the following entries:

 

 

copy \\LogMachineName\Logs\EventLog.evtx .  /Y

 

c:\Program Files (x86)\Log Parser 2.2\logparser.exe” -i:evt “select  * into Audit from  c:\logImport\eventlog.evtx” -iCheckPoint:CheckPoint.lpc -o:SQL -oConnString: “Driver={SQL Server Native Client 10.0};server=RAFAT20082;Database=DownloadEventLog;Trusted_Connection=yes;

del *.evtx /Q

 

NOTE: Pay attention to the highlighted area and fix the path as per your environment.

 

Check help for LogParser and play with other options, it is a powerful tool; its numerous options will always give you a solution for your Log problems.

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

%d bloggers like this: