In the first part of this series, we took a look at how to parse event logs and write that information to a database. We’re going to build on that script today and show you some more advanced techniques as well as some specific applications for this type of script.
In the last article I challenge you to see if you could create a script that returned only Warning type events. As promised, we're going to take a look at how to do that.
First off, we need to figure out how to determine if an event is a Warning type. We know we can tell that by checking the event’s Type property. With that in mind, there are two ways to filter our results depending on how we want to handle the information.
The first way is to adjust the WMI query that we are using so that it only returns the information we want. We can do by adding a WHERE clause.
Set colEvents = objWMIService.ExecQuery( _
"SELECT * FROM Win32_NTLogEvent" WHERE Type = 'Warning'")
With this code, the WMI service only returns those items that are in the Win32_NTLogEvent class which have a Type property value of “Warning.” In other words, the collection only contains warning events.
This can be further filtered to return warnings from a specific log. Here’s an example:
Set colEvents = objWMIService.ExecQuery( _
"SELECT * FROM Win32_NTLogEvent" WHERE Type = 'Warning' " _
& "AND LogFile = 'System')
With the above query, only warning events from the System event log are returned in the collection.
The second way to filter events is by using conditional statements in VBS. We’re going to return all events in the colEvents collection, and then filter them ourselves so we can handle specific event types differently.
Here the conditional statements separate event types as part of our For Each…Next loop. As a result, the colEvents collection still returns all events, but we can react differently based on types. Here warning events are written to the database. We could add an Else block to write remaining events to a different database if we wanted.
Let’s take a look at a couple more real-world examples of how you might want to filter results. We’ll begin with the last challenge I gave you in the last article. How can you create a script that only returns events for BSODs?
While the Blue Screen Of Death is dreaded by most users, it can be a very informative tool for system administrators and support specialists. The BSOD is usually a very good indicator of specific problems in Windows.
It also returns a unique event. This makes it possible to filter out only those events that represent BSODs. Now system and network administrators can monitor these events specifically.
("Select * from Win32_NTLogEvent Where Logfile = 'System' " _
"and EventCode = '6008'")
This query will return code 6008 events. These are “Improper Shutdowns.”
Learn to take advantage of the filtering abilities. Take a look at your event log and note the properties for the event or events that you want to monitor and then construct your queries accordingly.
Okay, so far these scripts have been designed for one time use. Let’s take a look at how we can alter them to make them reusable. This is nice if you want to create a scheduled task to run them at regular intervals.
The only problem we really have to worry about in this case is the fact that our script always tries to create a database. What if it already exists?
We can make use of the FileSystemObject to see if the file exists. If it does, we proceed, if not, we create it first. Here’s what it looks like.
Set objFso = CreateObject("Scripting.FileSystemObject")
If objFso.FileExists("C:events.mdb") Then
oConn.Open strConnection
Else
Set objCatalog = CreateObject("ADOX.Catalog")
objCatalog.Create strConnection
Set objCatalog = Nothing
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
End If
This code should be very familiar to you. We begin by connecting to the FileSystemObject. Then we turn to the FileExists function to check for our database file.
If the file exists, just open the connection and move on to our next code segment. If it doesn’t, we perform the code for creating it and establishing our table before moving on.
If you’ve taken the time to construct a useful a query, you should now have a completely reusable code sample. Put it into use by making it a Scheduled Task or by launching it as the result of another process.
Be sure to come back for the final part of this series when we take a look at how to make this code more useful in network environments. We’ll look at how to make it run on several machines at once as well as how to add a very cool feature. You won’t want to miss it. Until the, keep coding!
Windows Scripting 
Parsing Event Logs in WSH
