Visualize incoming mail statistics with Log Parser

Last week a colleague asked me to generate a list of all mail sent to a specific mailbox, including the timestamp. We use Exchange 2003, and we don’t delete the log files, so theoretically I had all the information: all the emails sent to the mailbox, and the date and time. Hmm… how to digg into 6 GB (!!) of log-files? Some search resulted in Log Parser, a free tool from Microsoft.

Log Parser is a very powerful (but very little-known) tool to execute SQL queries on log files. It’s possible to export the results to a text-file, csv, or even a SQL-database! A nice column can be found on the site of Microsoft.

I did the following to extract the information from the log files and to create a nice graph:

1. Download and install log parser
2. Run dir > dirlist.txt on the Exchange log file folder to get a list of all the log files of Exchange, comma separated. Then use i.e. Notepad+ to convert the list to a nice comma separated line
3. Run the following query to extract the needed information from your log file(s): logparser -i:w3c -q:ON “SELECT * FROM [complete path to your logfile *.log] [, complete path to your second logfile *.log when needed] WHERE Recipient-Address LIKE ‘[the email address you want to monitor]‘ AND Event-ID = 1028? > c:\export.txt
4. Load the created text file into Excel, delete the unnecessary columns (every column except the date and time in my case) and create some fancy graphs
5. Done!