The Event Log Query Utility

Selecting Event Types
Beginning at Section 2, the script determines which events to gather by evaluating which radio button the user selected. The radio button objects are zero-based array elements. In this script, the radio button array is named R1 and each button selection is a specific element in that array, as follows:

• The first element, R1(0), represents the "Errors and Warnings" radio button option, which tells the script to query only events classified as Errors or Warnings.
• The second element, R1(1), represents the "Errors and Audit Failures" selection, which tells the script to query for Errors and Security Log Audit Failures.
• The third element, R1(2), represents "Errors, Warnings and Audit Failures," which tells the script to query for these three event types.
• The fourth element, R1(3), represents "All Events," which lists all event log events that occur within a given number of days.
• The fifth element, R1(4), represents "List Just Codes Below," which queries only Event Code numbers that the user provides.
• The sixth element, R1(5), represents "Errors Only," which queries only for errors.
• The seventh element, R1(6), represents the "Free Form" radio button option, which lets the user enter his or her own WHERE clause conditions.

Notice that this section also builds the final WHERE clause, which changes slightly depending on which radio button the user selects.

Let's take a closer look at one of the event-type options. The script would execute the following two lines of code if the user selected the "Errors Only" radio button (array element R1(5)):

ElseIf R1(5).Checked Then
  q = "Select * From _
  Win32_NTLogEvent WHERE " & _
  "(Type='error' " & _
  "IIF(Isempty(mcodes), " _
  ", " or mcodes) & ") " & _
  "AND timegenerated > " _
  & "'" & vdate & "'" 

After the ElseIf clause, notice the array element followed by the Checked property, which indicates that the radio button representing array element R1(5) is checked. If you want to determine whether a button isn't checked, simply precede the element with the Not operator—If Not R1(5).Checked.

The next line of code stores the WQL statement to a variable named 'q'. Within that Select statement, you'll see that the script is querying the Win32_NTLogEvent class with WMI for events that have a Type value of 'error'. The code is also using the Immediate IF (IIF) function to see whether it needs to include any additional EventCodes in the query. And last, the code specifies that it wants to gather only events that have a timegenerated date stamp greater than the datetime stamp programmatically calculated from the Day(s)-back input and stored in the vdate variable.

Most radio button options relate to specific event types, so for the most part, the WHERE clauses will differ only slightly depending on which event types you want to gather— except for the All Events and Free Form options. When a user selects All Events, the query retrieves all events that meet the timegenerated criteria. And when Free Form is selected, the query uses conditions entered in the multipurpose input box as well as the specified timegenerated criteria. I didn't want to have to enter timegenerated comparison strings in the Free Form entry box, but if you wanted to, you could easily modify the Free Form query statement to omit the timegenerated > vdate code, then include all the timegenerated conditions you wanted in your Free Form conditions.

If you'd like to see the Select statement your criteria produces, I've left a commented Message Box command under this section of code. Look for Msgbox q. You can simply uncomment the command if you want to see the Select statement, but note that the command is best left uncommented when you have only a few computers to cycle through. You wouldn't want to leave it uncommented if you were looking at hundreds of computers.

Beginning at Section 3, the script executes the WMI query. If any events meet the criteria specified, the script cycles through the collection and writes the events to the Excel spreadsheet. If the event is an error-type event, the script formats that row in the spreadsheet in red. I've also included code that checks whether the event being written to Excel has an event code that matches a user-supplied event code; if it does, the script formats that Excel row in blue.

When the script has cycled through all the computers and written all the records in the query collections, it sorts the spreadsheet by computer (in case you're evaluating multiple computers from an input text file) and orders events by most recent date and time. The script then brings the application screen out of hiding. You're now armed with the details you need to tackle your troubleshooting or reporting duties—and in a fraction of the time it would typically take to gather this information. Note that the spreadsheet headers are all set to Autofilter, which lets you temporarily filter the spreadsheet on values of your choosing.

Don't Leave Home Without It
That's how the application works, but there are a couple of tips I'd like to mention. First, if you're entering a Free Form query that includes a backslash, you need to precede that backslash with another backslash character. The backslash is considered an escape character and indicates that the character following shouldn't be treated as a special character. So for example, if you want to query event logs for events where the Event Log User property equals NT Authority\System, you could enter user ='NT AUTHORITY\\ SYSTEM'.

Second, if an event log record doesn't contain a message (or description of the event), the script uses the contents of the InsertionStrings property to fill in the message field in the Excel report. The InsertionStrings property usually contains useful information—basically values that it plugs into messages when they exist; this information is better than having nothing at all in the report. For these types of exceptions, the script colors the Excel message cell contents purple.

The Event Log Query Utility is one of my top 10 administration tools. I carry it with me at all times on my flash drive. It's versatile yet easy to use, and it can save you a ton of time. Although there are other event log scripts available, I think you'll find this one has unique qualities to help you navigate that first or second level of troubleshooting.