Log Parser and SQL

  • Article

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 https://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.