Viewing Exchange Logs in Excel

It is possible to view details of the Microsoft Exchange server email logs in Microsoft Excel using the steps below (these were tested with Exchange 6.5 and Excel 2003).

Make a copy of the Exchange email log you wish to view. I have an Exchange 6.5 server running on a Windows Small Business Server (SBS) 2003 system. The server is named "S" and its email logs are in C:\Program Files\Exchsrvr\S.log. New logs are created each day with the date used as the name of the file for that day, e.g. 20080212.log for the log file for February 12, 2008. To view that day's log entries in Excel, I copy that file. It is a text file that can be edited with Notepad or any text editor. I can then take the following steps.

  1. Open the copy of the log file
  2. You will see something like the following at the start of the log file (it will all be on one line): 

    # Message Tracking Log File
# Exchange System Attendant Version 6.5.7638.1
# Date

    Delete everything up to "Date" in the log file.
  3. Open Excel and create a new workbook.
  4. In Excel, click on Data.
  5. Select Import External Data.
  6. Select Import Data.
  7. For Files of type, select All files.
  8. Navigate to the location of the log file and select it.
  9. Leave Delimited selected as the file type and click on Next.
  10. Leave Tab selected as the delimiter and click on Next.
  11. At the data format window, which is "step 3 of 3", for the first column select Date as the column data format and select YMD, which stands for "Year Month Day", presuming that is the date format used in your log files.
  12. Click on Finish.
  13. When asked where you want to put the data, you can keep the Existing worksheet selected and click on OK

With the Exchange System Manager Message Tracking Center, I can search for all email addressed to someone, such as example123@hotmail.com, but it won't let me search for all hotmail.com addresses. Once I have the data in Excel, I can search for all such addresses or perform other data searches I can't perform in the Message Tracking Center.

References:

  1. How to analyze Exchange Server SMTP log files in Microsoft Excel
    By Brian M. Posey, MCSE
    February 14, 2007
    SearchExchange.com