One of the most grueling tasks an administrator has to do in troubleshooting a problem is to sift through the event logs to find relevant events that will provide important clues to resolution. Often
This could extend over a period of days, weeks or even months, which makes sorting out all the events in between a real challenge. Perhaps you are looking for a specific event and want to see the frequency of those events. In other cases, there might be many instances of an error, such as a replication or authentication error, where each instance refers to a different server.
What tools should you use? How about Excel?
While there are sophisticated tools like Microsoft Operations Manager that allow searching, filtering and sorting, if you are a small or medium-sized company you might not have the budget for that. Let's look at a very powerful tool for examining event logs; you probably already have it on your desktop or laptop. That tool is Microsoft Excel. Let's see how it works.
To import the event log data into an Excel spreadsheet, use the following steps:
- Load the event log into the event viewer.
- In the event viewer, right-click on the event log and save it as a text file.
- Open the text file in Notepad, select all the data, right-click and select copy.
- Open Excel in a new workbook, click on the A1 cell, right-click and select paste. The data will be formatted nicely in the spreadsheet. No need to set up any fancy import wizards. It works very well.
Figure 1 shows the imported data from a system log that has been imported to an Excel worksheet. Note how the date, time, error level, event ID, source, originating server and descriptions appear properly formatted into cells. You can now sort the columns to arrange the data in a manner that will help your analysis. Figure 2 shows how I sorted on the event ID column. That allowed me to collect all the events together for comparison.
A specific example
To use the Sort function in Excel for this purpose, click on a column heading to select the entire column, then click on the Data option in the toolbar and select the Sort option. A dialog box appears, asking if you want to expand the selection, shown in Figure 2.
If you answer no, it will sort the column selected but nothing else, putting everything out of order. Answer yes to that question. You could select the entire worksheet and not get this dialog, but I find it easier to do it this way as it can take a lot of time to manually select the data in a large log file.
You then get an option as shown in Figure 3. There is no header row in this data, so select that option. Now you can select a multiple field search. In this case, I wanted to sort on the column that has the event ID, then the date, then the time. That gave me a chronological list of all events, sorted primarily by event ID. Using this basic technique, it is easy to collect data for comparison.
In Figure 4, I used this method to show all of Event ID 16 and list them by date and then by time, and doing that has organized the interesting data and I can see how often these events are occurring.
Use Excel to load multiple event logs
Another powerful way to use Excel is to load multiple event logs into separate worksheets in a single workbook. In Figure 5, I was looking at the event logs for two servers. I imported the Application and System logs into separate worksheets, labeling the worksheets with the server name and the log type. This allows me to easily switch between the logs, sorting each one. This is especially handy when you have something like copy errors in the Application log and you want to look for disk errors in the system log.
On one occasion, I was asked to do a health check on all the DCs in the forest before and after a migration. I created a separate .xls file for each log type -- application, system and directory services. In each file I created a worksheet for each DC. Of course you could create a separate .xls file for each DC, but with 50 or so DCs, that's a lot of files.
The Excel comments field
Here's another trick: Use the highlight and comment features to help you make notes and find certain events later on. Note, however, that Excel limits the amount of data that can be imported. I often exceed that limit. If you exceed the limit, you will get a dialog box indicating that only part of the data will be imported. To correct this, just undo the import to give you a blank worksheet, then go back to the text file and delete data until you get it inside the Excel limit. For instance, the logs may go back six months, but the errors started three weeks ago. Therefore, you can just import the data from the last three weeks. If you do want to get all the data, you'll have to import it in chunks and put them in separate worksheets. Be sure to label the worksheets appropriately.
So, there you go. A very powerful, but free, event log analysis tool. I make my living troubleshooting Active Directory problems, and I use this method every day. Try it -- you'll love it!
Gary Olsen is a systems software engineer for Hewlett-Packard in Global Solutions
Engineering. He authored Windows 2000:
Active Directory Design and Deployment and co-authored Windows Server 2003 on HP ProLiant
This was first published in September 2006