dcsimg

Import Security event logs using Log parser and SQL Server

June 29, 2005



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]

Click for larger image

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_








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers