Today I was asked to count the number of emails received on a given address (more than one), across a given time frame. I ended up using Microsoft’s Log Parser (the existence of which I discovered thanks to this post).
Log Parser let’s you run SQL queries on a range of differently formatted log files. Pretty handy stuff: I’ll surely find other uses for it.

MS Exchange, when Message Tracking is enabled, generates a bunch of log files into something like a C:\Exchsrvr\SERVERNAME.log\ folder. The data we need is tracked there.

“-q” stands for “quiet”, “-i:w3c” states that the input log(s) are in W3C format, “-o:tsv” tells Log Parser to output tab-separated fields, “-headers OFF” is self explanatory and then comes the SQL query. I’m selecting distinct combinations of MSGID and Recipient-Address. Distinct because info about an email message is stored in the log files across multiple lines, keyed by MSGID. A single query is enough to filter all of the addresses we’re interested in, ORed together. Also notice that in the SQL “FROM” clause I used “*.log”; you may need to change that to suit your time frame (message tracking logs are switched daily and stored for a configurable amount of days).

Log Parser’s output, redirected to a file, is then fed to cut/sort/uniq. Remember to change the line termination sequence (“:set fileformat=unix”, on vim) if you don’t have the afore mentioned commands on Windows and move the file to a Unix box.

We use cut (which defaults to tab separated fields) to trash MSGID and just select recipients addresses. These ones get sorted and counted. Last step is a reverse numerical sort. This kind of pipe sequence is a rather common “idiom” on Unix: it computes word (record) frequencies in a file.

Phew, no lines of script written for once… 🙂