Import Security event logs using Log parser and SQL Server - Page 2

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

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

When this VbScript is executed, it forms the following batch commands and executes them sequentially [Refer 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.

Step 8

Query the Audit table in SQL Server as shown in 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.

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