Parsing Event Logs in WSH - Adding event data to the database
(Page 4 of 4 )
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)
objRs("Message") = strMessage
objRs("EventType") = objEvent.EventType
objRs("RecordNumber") = objEvent.RecordNumber
objRs("SourceName") = objEvent.SourceName
objRs("TypeDesc") = objEvent.Type
strUser = objEvent.User
If IsNull(strUser) Then strUser = "N/A"
objRs("UserName") = strUser
objRs("TimeGenerated") = Date2String(objEvent.TimeGenerated)
objRs("TimeWritten") = Date2String(objEvent.TimeWritten)
objRs.Update
Next
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!
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
