The Event Log Query Utility - 13 Nov 2006

Downloads
93973.zip

Have you ever started a script that you thought would be a piece of cake to write only to find it a hard nut to crack? When I started writing the Event Log Query Utility, I simply wanted to pull from computer event logs a list of errors and warnings occurring within a given number of days, insert those events in a Microsoft Excel spreadsheet, and send the spreadsheet to management.

Sounds pretty simple, right? Just create a list of computer names in a text file, read that file into an array, and using the Windows Management Instrumentation (WMI) Win32_NT LogEvent class, query each computer in the array for errors and warnings. Creating the script was pretty simple, and the automated event-log query tool sure beat the manual method I'd been using. Then, management realized it also needed a list of Audit Failure events from the Security log. "No problem," I thought. "I'll just add another OR condition to my query to include Audit Failure types."

However, there was a problem. Security events that I could clearly see in an event log weren't showing up in my Excel output. After some digging, I found that querying the Security event log is a Privileged Operation that requires a Security parameter when connecting to WMI. To include the Security parameter in your WMI moniker, you construct the moniker a little differently than you normally would. The Web-exclusive sidebar "Creating a WMI Moniker: Including the Security Parameter" (http://www.windowsitpro.com/windowsscripting, InstantDoc ID 93972) describes how to include the Security parameter.

With the Security moniker created, my script now worked as expected, returning the required Security Audit Failures and greatly reducing the amount of time it took to get server error-event information to management and auditors. I even found myself using the utility more in my daily work, particularly when trying to resolve server problems or tackling support calls. But for everyday work, I realized I needed something more flexible. I didn't want to modify a text file whenever I needed events from a different machine. I also wanted to be able to query for a wider range of event types, not just errors, warnings, and Security Audit Failures. The HTML Application (HTA) I describe here is the culmination of several months of fine-tuning and enhancements resulting from real-world troubleshooting experiences, support calls, and other problem-solving efforts. The grand finale is the added free-form query functionality, which lets me query virtually any condition I want to.

The Application
If you've ever provided user support, you know you might need to check for any number of event codes or event types when troubleshooting different problems. So I needed to make my event log query script more versatile. As I considered how to improve the utility, I found that even information-type events can be essential in exploring problems. Take a machine connecting at half duplex—an event that's generated as an information-type event on some systems. If you know all your machines are supposed to connect at full duplex and one isn't doing so, you've hit on a possibly significant piece of information. Some Application Popup events are also logged as informational events, even though when you look at the event description, the popup might indicate an application failure.

Figure 1, shows the Event Log Query Utility application screen. The upper section of the screen describes how to use the utility. Note that you can press F1 to get more detailed Help information. Under the information section is the first input box with a Browse button beside it. Here, you have three options for giving the application the list of computers you want to query: You can leave the input box blank to query the local machine, you can enter a computer name or an IP address to query just one machine, or you can browse to a text file that contains any number of computer names and/or IP addresses. The text file must have one entry per line consisting of either a computer name or a computer IP address. Keep in mind that this application uses Excel as its reporting mechanism, and Excel has a limit of a little more than 65,000 rows. So depending on what you're querying and how many days back you want to look, you might need to use a few computer lists and process each one separately.

The section in the middle of the application screen features seven radio buttons that let you specify the types of events you want to gather. Appropriately enough, Type is the name of the Win32_NTLogEvent property the script uses to query the various types of events. You can find five types of events within event logs: Error, Warning, Information, Security Audit Success, and Security Audit Failure. The application's radio buttons offer various combinations of these event types to gather, with the exception of the Security Audit Success event type. I hardly ever need to sift through those events, so I didn't include them in the categories. But don't worry; you can query for those events if you need to, as you'll see in a moment. All but the fifth and seventh radio button options are self-explanatory; I'll get back to these two exceptions shortly.

The input box just below the radio button section—Within the Last x Day(s)—lets you indicate how many days back you want to look into the event logs. For example, entering 1 day specifies that you want to look at the last 24 hours of events. The script converts the Day(s) value for comparison with the Win32_NTLogEvent timegenerated property, which records the date and time an event was generated. The script creates a query condition that looks for events that have a time-generated date-stamp greater than the number of Day(s) back you specify.

Now, let's go back to the fifth and seventh radio button options, which work in conjunction with the last large input box near the bottom of the application screen. The name for this Textbox is EventCodesAndFreeForm, and this multipurpose input box takes on different functionality depending on which radio button you select.

Selecting the fifth radio button, List Just Codes Below, lets you enter Event ID numbers into the multipurpose input area so that you can query the event logs for just those event codes. Event IDs are typically listed as event numbers when you look at an event through the Event Viewer GUI. System Event ID 6005, for instance, is the Event ID associated with the event log service starting. Note, however, that what you see as an Event ID in the GUI is actually called an EventCode in the Win32_NTLogEvent class properties. If you choose to enter Event IDs in the multipurpose input box, they must be numeric and you must separate multiple Event IDs with commas.

Selecting the seventh radio button, Free Form, lets you provide the conditional syntax for the Win32_ NTLogEvent WMI query's WHERE clause in the multipurpose input box. You can specify the simplest of conditions to very complicated ones, as the following examples show.

• For a simple Free Form query to list only Security Audit Successes, you would enter Eventtype=4.
• To use wildcards to look for events with a SourceName starting with MS, you would enter sourcename like 'Ms%'.
• To use wildcards to return any event that has an s as the second character of the Sourcename, you would enter sourcename like '_s%'.
• For a more complicated condition that looks for Application Popup events, you would enter Sourcename='Application Popup' AND eventcode=26.
• To query for events that contain the word fail, you would enter a query such as message like '%fail%'.

The more precise the conditions in your Free Form query, the fewer unwanted records your output will contain. And the more you use this utility, the better you'll get at writing your Free Form queries. You might consider saving your more complicated queries to a text file or Microsoft OneNote so that you don't have to rekey them. You could even expand this HTA's capabilities to include another drop-down list or text box that contains your most commonly used Free Form queries.

At the bottom of the application screen, you'll find the RunScript button, the Exit button, and a hyperlink to the Win32_NTLogEvent Class reference. This link takes you to the Web page that features the Win32_NTLog-Event property definitions. You might want to review this page to familiarize yourself with the Win32_NTLogEvent Class's various properties and definitions.

Inside the Script
I can't walk through the lengthy Event-LogQuery.hta script line for line (you can download the complete application at InstantDoc ID 93973), but let's look at the key code sections. To use the Event Log Query Utility, you supply a list of computer names whose event logs you want to check, specify the events you want to look for, select the number of days to look back at, and press RunScript.

When you press RunScript, the script's main subroutine, getevents, moves the HTA application window out of sight, in effect minimizing the application window. HTA windows can be distracting and rather ugly if your main process takes a while to complete. You can't move them, you can't see anything behind them, and they don't refresh until the main process is complete. Because you might be querying large numbers of computers, you don't want to deal with this annoyance during processing. When processing completes, the application window is immediately moved back on screen.

After hiding the application, the script sets up a couple of initialization variables and stores the user-supplied computer list input in a variable called srvrlist. The code uses the initialization variables, evnts_exist and do_once, as flags or toggles throughout the script to determine whether to launch Excel. If there were no events or errors to report, I didn't want an empty spreadsheet staring at me. So if the program encounters an error connecting to a computer or a query returns a collection of events, the code sets the evnts_exist flag to true. The script uses the do_once flag or toggle to determine whether an instance of Excel has been created either because there's an error to report or because there are events to report.

The script then creates a reference to a starting point in time. You will query event logs for events that have a timegenerated date greater than a given number of days back from this reference point. If you want to look back three days, the reference point will be 72 hours before the time you press the RunScript button. Note that the script uses a fair amount of string manipulation to convert this simple number entry into a format equivalent to the Win32_NTLogEvent timegenerated property's format. A timegenerated property value looks like 20060915182038.703000-240, which maps to yyyymmddhhmmss.milisec timezonebias.

The script creates a starting-point datetime stamp by querying the local-datetime property from the Win32 _OperatingSystem class and storing the value in a variable called vdate. This timestamp value is in the same format as a timegenerated value, making the coding a little easier. To produce a value that represents the numbers of days back you want to look at, the code begins by subtracting the number of days back from the current year and storing that value in a variable (vyear). Next, the code subtracts the number of days back from the current month, storing the value in another variable (vmonth), then subtracts the number of days back from the current day and stores that value in yet another variable (vday). The script concatenates these three variables (vyear, vmonth, and vday) into a string variable and replaces the first eight characters of the starting datetime stamp variable vdate with the concatenated string, leaving the rest of the datetime stamp string untouched. So if you want to look back three days and the original vdate value is 20070101183038.000000-240, the conversion changes the vdate value to 20061229183038.000000-240. By querying the event logs for a timegenerated date greater than vdate, you can report on just the events generated within the last 72 hours.

The script then determines which computers to query. As I mentioned earlier, you have several ways to indicate which computers' event logs you want to query. The code beginning at Section 1 first checks to see if the input contains a period, which would indicate that the entry is a filename, an IP address, or a nonexistent file. To determine whether the entry is a file, the script uses the FileExists property of the Scripting.FileSystemObject class. If the file exists, the script opens it, reads the entire file into an array called Servers, and exits the main conditional checking section. If the file doesn't exist, the script checks to see whether the input is an IP address. The code turns the input into an array by using the Split function with a period (.) as the designated delimiter. Then, using the Ubound function, the script checks the array to determine whether it contains just three elements. If the array does contain three elements, the script checks to see if all the elements are numeric; if so, the script assumes the input is an IP address and inserts the value into the Servers array. If the input contains a period but doesn't meet either of these conditional checks, the script delivers a "File Not Found" message, returns to the application screen, and awaits user input.

If the entry doesn't contain a period and the input box is blank, the script fills the Servers array with a single element containing the name of the local computer. If the entry isn't blank and doesn't contain a period, the script considers the entry a computer name and inserts the computer name into the Servers array.

Next, the script moves into its main loop, which Listing 1 shows, and cycles through the array of computer names. (Note that the script checks for blank entries and discards any it finds.) At callout A in Listing 1, the script attempts to connect to WMI on the remote computer by using the security-level moniker discussed in the Web-exclusive sidebar "Creating a WMI Moniker: Including the Security Parameter." If this line of code raises an error, the computer probably doesn't exist or is inaccessible. At this point, the script creates an instance of Excel if it hasn't already been created and writes the error to the spreadsheet, indicating which computer was inaccessible. The script cycles back to the beginning of the For Next loop and retrieves the next computer name in the array (if there are more) and goes through the cycle again.

If there are no errors, at callout B the script checks whether any additional event codes have been specified. If there are additional codes, the script inserts them into an array (weeding out anything non-numeric) and builds a variable called mcodes, which contains a piece of the query's WHERE clause that's used later to query the event logs. For example, if a user enters 26,34 in the multipurpose input box to search for those event codes, the script constructs the mcode variable that contains the string EventCode=26 or EventCode=34. Note that the only time the script won't execute this section of code is when the user selects the Free Form radio button, which indicates that the user used the multipurpose input box for a user-supplied query and not additional event codes.

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.