Import Security event logs using Log parser and SQL Server

Wednesday Jun 29th 2005 by Muthusamy Anantha Kumar aka The MAK
Share:

Learn how to use Microsoft Logparser 2.2, Windows Job Scheduler and SQL Server database to monitor the event logs, and extract Monthly, Weekly Low security event reports from SQL Server database.



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_




Step 6


Create C:\LogParser\Logparserbatch.bat as shown in Fig 1.5


LogParser.exe "select * INTO Audit 
   from \\%5\security 
   WHERE TimeGenerated >= '%1 %2' and 
   TimeGenerated <= '%3 %4'" 
   -o:SQL -server:SQL  
   -database:SecurityLog 
   -driver:"SQL Server" 
   -username:TrainAudit 
   -password:ECE2F287 
   -createtable:OFF


Click for larger image

Fig 1.5


Download Logparserbatch.bat_

Note: Please update the Server name SQL to your Server Name. If you had changed the database name, table name login name and password in Step 3, then use the new database name, table name, login name and pass word in the batch file.




Step 7


Execute C:\LogParser\Logparse.vbs as shown in Figure 1.6.


Cscript Logparse.vbs C:\LogParser\Servers.txt



Fig 1.6

When this VbScript is executed, it forms the following batch commands and executes them sequentially [Refer Fig 1.7]


Fig 1.7

These batch commands form the Logparser.exe statement dynamically by passing 'from date' and 'to date' as parameters. [Fig 1.8] The formed logparser statement brings yesterday's security data from the event log from every server listed in servers.txt from 12:00am to 11:59:59pm.

Click for larger image

Fig 1.8

Step 8

Query the Audit table in SQL Server as shown in Fig 1.9.


Fig 1.9

Step 9

Schedule this C:\LogParser\Logparse.vbs to be executed every morning at 6:00 am as shown in the Fig 2.0 and Fig 2.1.


Fig 2.0


Fig 2.1

Conclusion

This article has illustrated how to use Microsoft Logparser 2.2, Windows Job Scheduler and SQL Server database to monitor the event logs, so that reports such as the Monthly, Weekly Low security events can be extracted from SQL Server database. The data collected in the SQL Server database satisfy the demands of General-Controls review by public auditors and regulatory agencies. If you want to collect data in a real time, you can update the VBScript and the job schedule to be executed to collect information every 15 minutes.

» See All Articles by Columnist MAK

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved