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
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.
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
» 
