Event Logs provide a very informative tool for system administrators. They can serve as an early warning system for system failures and can aid greatly in the detection and resolution of existing problems. Today we’re going to explore ways of using WSH to parse that information for better analysis and archival purposes.
To begin, I’m going to demonstrate how to use WMI in conjunction with WSH to read events in the various Win32 event logs. We’re going to read this information into a database. This database could be used as a simple archive, or to sort the data for easier analysis.
I’m going to use Microsoft’s Jet driver simply because it’s very common. You can use any database driver you like. I’m also going to build an MDB (Microsoft Access) database, again because it’s common.
Different database drivers support different levels of functionality. Some even support different properties and methods. If you’re unsure about the one you choose, MSDN is a good place to find that documentation. Chances are good that you can just drop in this code and run with it anyway.
Finally, I’m not going to go into great detail about manipulating this information. I’m assuming that you are either familiar with working with databases or are at least familiar with database management software like Access. My focus in this article series is going to be setting up the database and the strategy behind that.
This project is actually two-fold. While the immediate benefits of moving this information to a database are obvious, there is much more to be had here. Over the course of this article series, I’ll be building upon this base idea to develop a really nice solution for network and system administrators alike.
But I’m not letting the cat out of the bag on that just yet; you’re going to have to come back for the rest of the articles. I promise you’re going to like what I’ve come up with this time.
Put on your coding caps and fill up those coffee cups. Let’s get started!
Since we’re dealing with event logs, it’s probably a good idea to start out by describing how to access them, eh?
We are once again turning to WMI. You’ll find—especially with system administration scripts—that the scripts with the most functionality and power tend to rely directly on WMI. This is because WMI technology allows us to have direct access to nearly every conceivable part of the system. It’s also extremely manageable and highly configurable.
I trust everyone remembers how to connect to the WMI Service. For those who don’t, we need to connect to the CIMV2 provider like this:
You’ll notice that I’ve set the impersonation level. If you want to use this script in a network environment to analyze remote machines, you’ll need to have this set. If you’re not in such an environment, it doesn’t hurt to leave it in there either.
Next you need to form a query to poll the events from the event log. All of the events are contained in the Win32_NTLogEvent class.
Set colEvents = objWMIService.ExecQuery( _
"SELECT * FROM Win32_NTLogEvent")
If you wanted, you could filter the event to return specific event logs. Simply add a WHERE clause to your query and specify an event property.
SELECT * FROM Win32_NTLogEvent WHERE LogFile = 'System'
Each item returned represents a single event. The table below lists the available item properties that you will probably find useful.
Category
An integer representing the event category.
ComputerName
A string that returns the computer’s DNS name.
EventCode
And integer representing the error number.
EventType
And integer representing the event type.
LogFile
A string that returns which log an event is from.
Message
The textual message associated with an event.
RecordNumber
An incremented integer that serves as a unique ID for each event record.
SourceName
A string representing the source of the event.
TimeGenerated
The date and time when the event occurred.
TimeWritten
The date and time when the event was written to the log.
Type
The textual description of the event type.
User
A user name representing the user session in which the event took place.
All we’ve done here is create the flat file that will contain our database. As it sits now, this file is completely unusable. In order to make it usable we need to add a database table and create some fields. We’ll do that simply with ADODB.
Set oConn = CreateObject("ADODB.Connection")
oConn.Open strConnection
oConn.Execute "CREATE TABLE EventTable(" _
& "Category INT, " _
& "ComputerName VARCHAR(50), " _
& "EventCode INT, " _
& "Message VARCHAR(100), " _
& "EventType VARCHAR(50), " _
& "RecordNumber INT, " _
& "SourceName VARCHAR(50), " _
& "TypeDesc VARCHAR(15), " _
& "UserName VARCHAR(50), " _
& "TimeGenerated VARCHAR(19), " _
& "TimeWritten VARCHAR(19)" _
& ")", , 129
This is a two step process. First connect to the ADODB object, which in turn opens a database connection. Then, execute a SQL statement that creates the DB table in our file. Notice that I have not closed the ADODB connection. I’ve intentionally left it open as we’ll be using it again in the next step.
All I’ve done is create a field for each of the event log properties. Notice that I’ve limited the Message field to 100 characters. We’ll have to make sure that we don’t write more than that to our database.
Why have I restricted it? I’ve restricted it mostly because different DB drivers support different row lengths. I wanted to make this script universal for all readers regardless of what driver or database format they choose to use.
Aside from all of that, nine times out of ten, you’re not going to need all of the information in the message field anyway. So limiting its size in our database will make it more functional and faster.
Here comes the fun part. Now that we’ve learned how to read the event data and how to create a database, let’s learn how to add that data to the database.
To do this, we’ll first write our data to a record set. Then, we’ll use the record set to update the data.
Set objRs = CreateObject("ADODB.Recordset")
objRs.Open "SELECT * FROM EventTable;", oConn, 0, 3
The above code reads our database file and creates a record set with any information that it finds. In this case, it just creates an empty record set because the database doesn’t contain any information.
Think of a record set as a copy of our database in memory. With a record set we can easily manipulate the data, sort fields, and add and delete records. Then we can write the entire record set to the database all at once.
With our record set created, we need to begin adding data to it. If you remember correctly, we still have the colEvents collection holding the event data that was returned from our WMI query. We’re going to construct a For Each…Next loops to move through that collection.
We’ll make a call to each of the event properties as we go and add that information to our record set. Don’t get confused, it’s easier that it sounds. Here, take a look at the code.
For Each objEvent In colEvents
Set objEvent = colEvents.NextEvent()
objRs.AddNew
objRs("Category") = objEvent.Category
objRs("ComputerName") = objEvent.ComputerName
objRs("EventCode") = objEvent.EventCode
strMessage = objEvent.Message
If Len(strMessage) > 100 Then strMessage = Left(strMessage, 100)
Notice the use of the AddNew and Update methods in each iteration of the loop. The AddNew method adds a new record to the record set. Then we add data to each of the fields. Finally, the Update method writes that information to the database to which the record set is attached.
I’ve also done a little formatting here. You remember that we limited our Message field to 100 characters. We need to do a little string manipulation to make sure that we’re not returning more than that.
I’ve also done some manipulating to prevent the UserName field from being empty. If an event occurs before a user is logged on to the system, it logs a null value for the user field. I’ve just applied a dummy string if that occurs.
Once we’ve looped through all of the events, the only thing left to do is wrap up loose ends.
objRs.Close
oConn.Close
Close the record set to remove it from memory and then close the database connection.
You can now open this database in Access and manipulate the data and create reports. Or you can just keep a historical archive. Play around with the SQL statements and try adding condition statements to filter only those results that you want.
Can you come up with a solution that only archives Warning events? How about only BSODs? Give it a try. And be sure to stop by for part two of this series when I’ll show you those things and more. Until next time, keep coding!
Windows Scripting 
Advanced Event Log Parsing in WSH
