Import Security event logs using Log parser and SQL Server

In order to satisfy the demands of General-Controls review
by public auditors and regulatory agencies, companies should monitor the
security events of every production system they have. This article illustrates
how to use Microsoft Logparser 2.2, Job Scheduler and SQL Server database to
monitor the event logs, and extract Monthly, Weekly Low security
event reports from the SQL Server database.

Pre-requisite

1. 
Log parser 2.2.
It comes with Windows resource kit.

2. 
Make sure
Scheduler service is running on the machine where you are scheduling this job

3. 
Make sure the
login used for running this job has access to Event log on all the boxes

Note: This article uses Log Parser version 2.2

Step 1

Create
Folder C:\LogParser [Refer Fig 1.0]

Step 2

Install Log parser 2.2 [Refer
Fig .1.1 and 1.2] to the Folder C:\LogParser. {Download the parser from the Microsoft
website or your Internet Information Services (IIS) 6.0 Resource Kit Tools.}



Fig 1.1



Fig 1.2

Step 3

Create the
database and tables on the SQL Server database using the script below.


Use master
Go
Create Database SecurityLog
go
use SecurityLog
go
–Drop table Audit
CREATE TABLE [Audit] (
[EventLog] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecordNumber] [int] NULL ,
[TimeGenerated] [datetime] NULL ,
[TimeWritten] [datetime] NULL ,
[EventID] [int] NULL ,
[EventType] [int] NULL ,
[EventTypeName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EventCategory] [int] NULL ,
[EventCategoryName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SourceName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Strings] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ComputerName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SID] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Message] [varchar] (1255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Data] [varchar] (1255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

go
sp_addlogin ‘TrainAudit’,’ECE2F287′,’SecurityLog’
go
sp_adduser ‘TrainAudit’
go
sp_addrolemember ‘db_datareader’,’TrainAudit’
go
sp_addrolemember ‘db_datawriter’,’TrainAudit’
go

Download SQL.sql

Step 4

Create
C:\LogParser\Servers.txt and list all of the server names that you would like
to audit, as shown in the Fig 1.3


SQL
Stargate
Claire
Boomer



Fig 1.3

Step 5

Create
C:\LogParser\Logparse.vbs as shown in Fig 1.4


‘Objective: Execute LogParser for all the server listed in serverlist.txt
‘Created by: MAK
‘Date: May 2, 2005
Set objArgs = WScript.Arguments
inputfile=objArgs(0)
Set oShell = WScript.CreateObject (“WSCript.shell”)

mydate=dateadd(“d”,-1,now())
year1=year(mydate)
month1=month(mydate)
day1=day(mydate)
FromDate=cstr(Year1)+”-“+right(“00″+cstr(month1),2)+”-“+right(“00″+cstr(day1),2) +” 00:00:00″
Todate=cstr(Year1)+”-“+right(“00″+cstr(month1),2)+”-“+right(“00″+cstr(day1),2) +” 23:59:59″
wscript.echo fromdate
wscript.echo todate

Set iFSO = CreateObject(“Scripting.FilesyStemObject”)
Set ifile = iFSO.OpenTextFile(inputfile)

Do until ifile.AtEndOfLine
ServerName= ifile.ReadLine
Shellstring =”Logparserbatch.bat ” + FromDate+” “+ToDate +” “+Servername+””
wscript.echo Shellstring
iRC = oShell.Run(Shellstring, 1, True)
Loop



Fig 1.4

Download LogParse.vbs_

Latest Articles