Pivot Table Analysis of the Event Log

Recently I had to investigate a problem with our SMTP server. One of things I wanted to know was when the SMTP problems started. Like most computer problems multiple event IDs were were being triggered each time a problem occurred. The Pivot Table Wizard is a great tool for quickly summarizing the event log data. Here is how I did it.

  1. Open the Event Viewer, filter your view to the event source you are interested in, export the list, and transfer the exported list back to your work station.
  2. Open a new blank worksheet in Excel and import the data using the Import External Data Wizard.
  3. Open the Pivot Table Wizard. Drop the “Date” into the row area. Drop the “Event” field into the column area. Drop any other field into the data area. I used the “Source” field. You should now have a pivot table that has columns for each event ID and a count of the number of events per day per event ID.
  4. I prefer the data to sorted in descending order so I went into the Advanced Field properties for the “Date” and set it to descending.

In my case with the pivot table analysis I could see that one event ID, 4000, was the primary event. The rest of the event IDs were secondary events.