Event log Export Script           Download Files

The event log export script uses a Semi-Synchronous query of the application log to retrieve all events in the log and then uses ADO to write those events to an Access database.

Asynchronous, Semi-Synchronous and Synchronous WMI event log queries

I learnt an important thing while writing this script about the perils of using synchronous WMI queries when trying to return large datasets. When you invoke a synchronous query the query maintains control of your application for the duration of the query (meaning that it blocks the execution of everything else until its finished which means "not responding" in the real world). This is okay on small queries but for a large query involving a large dataset you end up with a query that runs extremely slow and never finishes. When querying large datasets I found that using semi-synchronous queries the most reliable and fastest method. A very good resource that it explains this is  http://www.huntland.co.uk/Downloads/WMI/AsyncOperations.html

The code itself first uses some ADO code to connect to the access database file that sits on the d: drive of the server and then queries the Windows event log using WMI. The For next loop then runs though and formats the data returned from the query and then inserts this into the access database.

Category = "N/A"
Computer_Name = "N/A"
Event_Code = "N/A"
Message = "N/A"
Record_Number = "N/A"
Source_Name = "N/A"
Time_Written = "N/A"
Event_Type = "N/A"
User = "N/A"
Set Cnxn1 = CreateObject("ADODB.Connection")
strCnxn1 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\eventlog.mdb;"
Cnxn1.Open strCnxn1
set WshShell = CreateObject("WScript.Shell")
Set fso = CreateObject("Scripting.FileSystemObject")
compname = WshShell.ExpandEnvironmentStrings("%computername%")
strComputer = "."
Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colLoggedEvents = objWMIService.ExecQuery("Select * from Win32_NTLogEvent Where Logfile='Application'",,48)
For Each objEvent in colLoggedEvents
    Category = objEvent.Category
    Computer_Name = objEvent.ComputerName
    Event_Code = objEvent.EventCode
    Message = objEvent.Message
    Record_Number = objEvent.RecordNumber
    Source_Name = objEvent.SourceName
    Time_Written = objEvent.TimeWritten
    Event_Type = objEvent.type
    User = objEvent.User
    Time_Written = left(Time_Written,(instr(Time_written,".")-1))
    rem message section
    if (isnull(Message)) then
    	message = "NA"
    else 
	Message = replace(Message,chr(13)," ")
    	Message = replace(Message,chr(10),"")
	Message = replace(Message,",","")
	Message = replace(Message,chr(34),"`")  	 	
    	Message = replace(Message,"'","")  	 	
    	Message = mid(message,1,(len(Message)-2))
    	Message = left(Message,254)
    end if
    line_to_insert = "'" & Time_written & "','"  & category & "','"  & Event_code & "','" & Event_Type & "','" & Source_name & "','" & message & "'"
    sqlstate1 = "INSERT INTO Eventlog_raw ( Event_Time, Category, Event_Code, Event_Type, Source_Name, Message ) values(" & line_to_insert & ")"
    Cnxn1.Execute(sqlstate1)
next
df = msgbox("done")

Installing and using the event-log query script

To use this event log query script there is one hard coded reference you should be aware of which is

strCnxn1 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\eventlog.mdb;"

This means the database must be located on the d: drive on the server unless you change the reference to another location. Other then that its just a normal console script that will pops up a message at the end to let you know its done.

Report Files and Database

To display the data saved in the database and extrapolated by the different access queries I've created 6 ASP pages that access the Access database using ADO. Within these ASP pages their is one hardcode reference to the database sitting on the D: drive on the server

dataConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\eventlog.mdb" 'make connection

After this to install them you can create a directory under the default website call it something like eventlog put the 6 asp pages in that directory and you should be ready to go. If you are using Windows 2003 you need to make sure you have enabled ASP pages under the IIS web service extensions.

Download Files