Using Log Parser to determine the email traffic statistics to and from a particular mailbox

Several times there is a requirement to figure out how many emails are sent or received by a particular user in the exchange organization. Exchange Server 2007 makes it easier to determine by simply running a transport agent on an Edge Box which can be downloaded from Microsoft Download Center Message Statistics Sample Agent. But, when it comes to Exchange Server 2003 it is quite difficult to trace such type of reports. Fortunately, Exchange Server 2003 still holds this information with it in the form of message tracking logs per server. If you have message tracking enabled on your exchange servers it would not be hard for you figure out the number of sent or received emails by a particular mailbox.

Still, the question remains unanswered. How do I do this if my boss asks me to provide such reports. There are many third party tool available in the market to do the same task. But, if you are not willing to invest money to buy a dedicated software for doing this; you can use the Microsoft Log Parser tool to export this information. Below are the steps to do it:

Prerequisites:

Microsoft Log Parser 2.2 – Download

Microsoft Windows 2000 Professional or higher.

Microsoft Exchange Server 2000 or higher.

Message Tracking Log file structure:

Once you have downloaded the Log Parser from above link you can install the log parser by using the GUI interface. Yet, the application does not have a very good GUI itself. It’s a purely a console based application. Before you actually start using this application once the installation is completed we need to understand the format of the Exchange Server message tracking file. Message tracking files are saved in W3C format by default and can be parsed using Log Parser. Basically, if you open a sample message tracking log file in a text editor you can see the fields mentioned in it. They appear as below.

# Message Tracking Log File                                                                               
# Exchange System Attendant Version 6.5.7638.1                                                                               
# Date    Time    client-ip    Client-hostname    Partner-Name    Server-hostname    server-IP    Recipient-Address    Event-ID    MSGID    Priority    Recipient-Report-Status    total-bytes    Number-Recipients    Origination-Time    Encryption    service-Version    Linked-MSGID    Message-Subject    Sender-Address

In above example the Exchange System Attendant Version suggests the version of Exchange Server to which the log files belong and the text marked in maroon color suggests the fields in the message tracking log files according which details of each message is sorted.

Now we can go ahead and start sorting the things of our interest. To determine the number of emails sent/received or both by a specific user You can simply start with a simple command at Log Parser command prompt:

C:Program FilesLog Parser 2.2>Log parser -i:w3c "SELECT * FROM C:20090118.log WHERE Sender-Address like ‘F1E2K3-IS@warriorcorp.com’" -O:CSV >C:Output.csv

Considering the above example as a specimen, Sender-Address can be replaced by any fields mentioned above and same applies for the replacement of ‘*’. At the end of processing you get a filtered output for it. Each of the fields can be separated by a comma (,) same will apply for email addresses as well.

Limitations:

  • You can not parse more files from different servers at the same time.
  • If you have multiple files  to extract data from; the queries has to be run on each file separately. As a work around to it a little bit of scripting would help you to accomplish your requirement.
  • Exchange stores the message tracking information for a single day in each file.

3 thoughts on “Using Log Parser to determine the email traffic statistics to and from a particular mailbox”

  1. Hello,

    I am trying to run extract the specific sender information from the exchange 2003 message tracking logs.

    The command executes but the output file is of 0KB and no data in it.

    LogParser 2.2>Log parser -i:w3c “SELECT * FROM C:20090118.log WHERE Sender-Address like ‘Administrator@SMTPDomain.com’” -O:CSV >C:OutputSender.csv

    Howerver if i replace Sender-Address with Recipient-Address, it works fine.

    Any help on this is much appriciated.

    Thanks
    Ravi

    1. Hi Ravi,
      Thank you for posting a question. You may already know that exchange will store all infomration in many log files instead of using only one to hold all day’s data. Have you tried searching through all log files you have for a specific day? I have tried covering this part in Limitations section of this post. Do let me know if that does not help you.

  2. Hi,

    Thanks for your reply.

    I have also tried to run the below command to extract the sender email address – But still the command completes successfully but it generates 0KB file with NO data in it.

    C:Program FilesLog Parser 2.2>logparser -q -i:w3c “SELECT* FROM ‘C:TrackingLogFiles*.log’ WHERE Sender-Address like’DefaultPublicFolder@ENR.com'” -O:CSV > C:OutputAgainTest.csv

    I guess its a bug in the product which is not identifying Sender-Address properly. I & my colleague played around a bit with Message Tracking Logs by opening it in XLS and moving the Sender-Address column to next and saving the log.

    After which i am able to extract the Sender-Address fine. I guess seems like it has to do with the way the date is listed after ‘sender-address’

    Thanks
    Ravi

Comments are closed.