One of the useful reports you can extract from message tracking logs is the daily email message traffic load for an Exchange server.

I run this report almost every day (we retain up to 30 days of message tracking logs so running every day is not required) to look for any patterns or trends that may concern us.

The report can be quickly generated using Log Parser. Install it on the server and run the following query from the folder where the message tracking logs are stored.

SELECT TO_LOCALTIME(TO_TIMESTAMP(EXTRACT_PREFIX(TO_STRING([#Fields: date-time]),0,'T'), 'yyyy-MM-dd')) AS Date,
       COUNT(*) AS Hits
from *.log
where (event-id='RECEIVE')
GROUP BY Date
ORDER BY Date ASC

As a single command line it will be as follows:

"C:Program Files (x86)Log Parser 2.2logparser.exe" "SELECT TO_LOCALTIME(TO_TIMESTAMP(EXTRACT_PREFIX(TO_STRING([#Fields: date-time]),0,'T'), 'yyyy-MM-dd')) AS Date, COUNT(*) AS Hits from *.log where (event-id='RECEIVE') GROUP BY Date ORDER BY Date ASC" -i:CSV -nSkipLines:4 -rtp:-1

This will give you output similar to this:

Date       Hits
---------- ----
2012-03-17 5311
2012-03-18 2575
2012-03-19 6296
2012-03-23 283
2012-03-24 1
2012-03-25 1
2012-03-26 5
2012-03-27 1
2012-03-29 635
2012-03-30 255
2012-03-31 883
2012-04-01 856
2012-04-02 1051
2012-04-03 1178
2012-04-04 1145
2012-04-05 1138
2012-04-06 1203
2012-04-07 866
2012-04-08 1016
2012-04-09 1210
2012-04-10 1171
2012-04-11 833
2012-04-12 26
2012-04-14 21
2012-04-15 1

Statistics:
-----------
Elements processed: 106917
Elements output:    25
Execution time:     2.66 seconds

Pretty useful on its own, but if you plan to create graphical reports using this data you can save yourself a bit of time and let Log Parser generate the chart for you, as long as you have Office or the Office Web Components installed on the computer running Log Parser.

"C:Program Files (x86)Log Parser 2.2logparser.exe" "SELECT TO_LOCALTIME(TO_TIMESTAMP(EXTRACT_PREFIX(TO_STRING([#Fields: date-time]),0,'T'), 'yyyy-MM-dd')) AS Date, COUNT(*) AS [Daily Email Traffic] INTO DailyTraffic.gif from *.log where (event-id='RECEIVE') GROUP BY Date ORDER BY Date ASC" -i:CSV -nSkipLines:4 -chartType:Column3D

Calculate Daily Email Traffic using Message Tracking Logs and Log Parser

About the Author

Paul Cunningham

Paul is a former Microsoft MVP for Office Apps and Services. He works as a consultant, writer, and trainer specializing in Office 365 and Exchange Server. Paul no longer writes for Practical365.com.

Comments

  1. バッグ,財布&小物専門店,

    世界一流スーパーコピー 時計
    ※弊社は海外安心と信頼のスーパーコピー 時計です。
    ※2018 新作が満載!皆様を歓迎して当店をご光臨賜ります。
    ロレックススーパーコピー,パネライスーパーコピー,ウブロスーパーコピー,シャネル スーパーコピー等。
    サイトは世界一流スーパーコピー 時計専門店です。
    ぜひ一度当店の商品をお試しください。
    驚きと満足を保証致します。ご利用をお待ちしております。

  2. Stanley Wasserman

    Very useful script, thanks. Is there anything that needs to be modified in this string in order for it to process all of logs in the MessageTracking directory. Currently it will only process 19 out of 83 logs on one sever I have. My reading of the stings says it should parse all of the logs based on “*.log” being in the string.

    Any ideas on why it’s not?

  3. Inderjit Singh

    Hi Paul,

    The script works just fine, i was wondering if we can tweak this script for a particular exchange database, your help is appreciated.

    Regards
    -Inderjit

  4. Amir Arsalan

    Hi paul thanks again for this useful article,just one question,how can i make this amazing report for a specific mailbox?

  5. Mike Prisco

    Paul,
    I am looking to have this script run everyday via task scheduler. It is looking for messages sent to each DL every day. So I manually ran this one with todays date, need to have it run each day with that days date and time
    I m stuck on how to get working, any ideas?

    get-TransportServer | Get-MessageTrackingLog -Resultsize unlimited -EventID Expand -Start “10/5/2016 12:01AM” -end “10/5/2016 11:59PM” | ? {$_.RelatedRecipientAddress -like “DistroList@abc.com”} | ft Timestamp,Sender,MessageSubject -Autosize

    Has to check these DL as well every day
    DistroList1@abc.com
    DistroList2@abc.com
    DistroList3@abc.com

    1. Mike Prisco

      Paul,
      I am looking to have this script run everyday via task scheduler. It is looking for messages sent to each DL every day. So I manually ran this one with todays date, need to have it run each day with that days date and time. Save the results as .csv And email that csv to Me@abc.com
      I m stuck on how to get working, any ideas?

      get-TransportServer | Get-TransportServer | Get-MessageTrackingLog -Resultsize unlimited -EventID Expand -Start “10/5/2016 12:01AM” -end “10/5/2016 11:59PM” | ? {$_.RelatedRecipientAddress -like “DistroList@abc.com”} | ft Timestamp,Sender,MessageSubject -Autosize

      Has to check these DL as well every day
      DistroList1@abc.com
      DistroList2@abc.com
      DistroList3@abc.com

  6. Adeel Memon

    Is it possible to get this report for subdomains? Company X hosts emails for 10 different domains. Would like to get a report of total mail volume for each domain.

    1. Paul Cunningham

      You can create any query you like. Look at the log file that is being queried by Log Parser, and you’ll see which fields are available. You can then modify the Log Parser query to filter to specific domains.

      But I suspect you’ll find it far more cost effective to buy a reporting solution that can do this for you.

  7. Zaid

    when I copy n paste the query from within the log parser studio, it says to select log type and so far I tried a few doesn’t seem to work ..

    the default queries use type EELLOG.

  8. Adnan Siddiqui

    Hi Paul, Just wanted to know, I am running Exchange 2010 and wanted to get the report of outgoing emails with or without TLS.
    Can I get that report in Exchange 2010?

    Thank you,
    Adnan

    1. Paul Cunningham

      It isn’t a built-in report. You could possibly infer it from the protocol logs.

  9. Greg

    Great post!!! Works like a champ on my 2013 Edge servers!

    1. antar

      how can I count smime messages sent

  10. Lanette

    I receive the following error when trying to run the reports:

    Error: WHERE Clause; Syntax Error: unknown field ‘event-id’

    1. Tuan Le

      It should be “event” only not “event-id”.

  11. Yasir

    Hi Paul
    as i am planning to migrate from exchange 2010 to 2013 on premises. how can i calculate daily send item in exchange server 2010. for receiving messages i am using iGetmail. need your guidance.

    Thanks

  12. Vijay

    Hi Paul

    When i run your group command below. i am getting error like “Error: invalid parameter “chartType””

    One more how do i get the sending message count

    Your command:

    “C:Program Files (x86)Log Parser 2.2logparser.exe” “SELECT TO_LOCALTIME(TO_TIMESTAMP(EXTRACT_PREFIX(TO_STRING([#Fields: date-time]),0,’T’), ‘yyyy-MM-dd’)) AS Date, COUNT(*) AS [Daily Email Traffic] INTO DailyTraffic.gif from *.log where (event-id=’RECEIVE’) GROUP BY Date ORDER BY Date ASC” -i:CSV -nSkipLines:4 -chartType:Column3D

    I am getting error below, can you please suggest me.

    Error: invalid parameter “chartType”

    Thanks.

      1. Vijay

        Yes.. i have office 2007, is need to install office web components.

      2. Vijay

        Paul

        how do i take send mail count ?

    1. MI

      check path in script, missing \ sign

  13. taylor

    paul, can reports of incoming/outgoing be specific to activesync?

    1. Paul Cunningham

      No I don’t think message tracking logs contain any data that would help you separate that out.

      You can use the IIS logs to look at ActiveSync traffic in general. Log Parser Studio comes with some preset reports for that.

  14. Larry

    First of all, this is fantastic stuff – I was able to get this working on my Win7 Management workstation with no issues
    minor challenge though : I keep my Exchange 2010 Tracking Logs for 6 months in the Exchange Tracking Logs DIR so the resulting .GIF is cluttered. When I copy some logs to another folder say Z:TrackingLogs the CD to that DIR I get no input in my statistics from Log Parser CMD and an empty GIF file. Any thought as to why this happens?
    e.g.
    Statistics:
    ————-
    Elements processed: 124415
    Elements output: 0
    Execution time: 0.89 seconds

  15. Kishore

    Hi Paul,

    I’ve installed the log parser on a Exchange machine and copied the Message tracking logs to a folder in that machine and running the above command giving me following error.

    PS E:KishoreMessage_track_logs_HUB01> “C:Program Files (x86)Log Parser 2.2logparser.exe” “SELECT TO_LOCALTIME(TO_TI
    MESTAMP(EXTRACT_PREFIX(TO_STRING([#Fields: date-time]),0,’T’), ‘yyyy-MM-dd’)) AS Date, COUNT(*) AS Hits from *.log where
    (event-id=’RECEIVE’) GROUP BY Date ORDER BY Date ASC” -i:CSV -nSkipLines:4 -rtp:-1

    Error message
    ———————
    Unexpected token ‘SELECT TO_LOCALTIME(TO_TIMESTAMP(EXTRACT_PREFIX(TO_STRING([#Fields: date-time]),0,’T’), ‘yyyy-MM-dd’)
    ) AS Date, COUNT(*) AS Hits from *.log where (event-id=’RECEIVE’) GROUP BY Date ORDER BY Date ASC’ in expression or sta
    tement.
    At line:1 char:255
    + “C:Program Files (x86)Log Parser 2.2logparser.exe” “SELECT TO_LOCALTIME(TO_TIMESTAMP(EXTRACT_PREFIX(TO_STRING([#Fi
    elds: date-time]),0,’T’), ‘yyyy-MM-dd’)) AS Date, COUNT(*) AS Hits from *.log where (event-id=’RECEIVE’) GROUP BY Date
    ORDER BY Date ASC” <<<< -i:CSV -nSkipLines:4 -rtp:-1
    + CategoryInfo : ParserError: (SELECT TO_LOCAL…DER BY Date ASC:String) [], ParentContainsErrorRecordExc
    eption
    + FullyQualifiedErrorId : UnexpectedToken
    —————————————————–

  16. Kevin O'Brien

    Hi Paul,

    Thank you for sharing this script. Can you tell me how to have it only report on yesterday date? I would like to run this script daily and export the data to another application of ours to graph it.

    Thanks for your help.
    Kevin

  17. Kyle Kennedy

    This looks like it only pulls from a local or mapped folder for the logs there. What about multiple front end servers? How can you aggregate all of the inbound/outbound mail into one view if there are, say, 4 HT servers behind a load balancing solution? Would I have to manually export each report and combine in Excel?

  18. Paul Cunningham

    Possible, yes I imagine so. But I don’t have a code sample for you sorry 🙂

      1. Paul Cunningham

        Yep, the basic approach will be to use the sender or recipient fields to determine whether the message is an incoming or outgoing message. It gets a little more complex if there’s a mix of internal and external recipients on a message, but if you’re looking for general stats rather then precise numbers then it should be fine.

  19. Oleg A

    Hi, Paul.

    Could you help me with this task: I need to calculate daily outbound and inbound traffic separetly. Is it possible with logparser?

    Regards,
    Oleg.

  20. Victor

    Whenever I run the following command

    “C:Program Files (x86)Log Parser 2.2logparser.exe” “SELECT TO_LOCALTIME(TO_TIMESTAMP(EXTRACT_PREFIX(TO_STRING([#Fields: date-time]),0,’T’), ‘yyyy-MM-dd’)) AS Date, COUNT(*) AS Hits from *.log where (event-id=’RECEIVE’) GROUP BY Date ORDER BY Date ASC” -i:CSV -nSkipLines:4 -rtp:-1

    I get “The system cannot find the path specified” could you please let me know what am I doing wrong.

    thank you

  21. Donna

    Can you have the results written out to a file?

    1. Andre

      I think you could use after the command the following
      ” | export-csv NameOfFile.csv”

      or

      ” > NameOfFile.txt ”

      It will be saved in the directory in which you run the command.

      Don’t use the double quotes it is just to indicate that you should copy in between the quotes.

  22. Dax

    This is great Paul! Just a quick question, is it safe to install Office on a production HT server?

    1. Paul Cunningham

      I can’t see it causing a problem, but you’re probably better off just using a management server or admin workstation, and mapping a drive to the message tracking logs folder.

  23. nawar

    Paul, did you compare the output from message tracking to the ones that exchange provide automatically (transportroleslogsserverstats), do they match ?

  24. Nick

    Great report out and super easy! Thanks!

  25. Dan

    One more question regarding this post. Is this just received messages are send/receive?

    1. Paul Cunningham

      It uses the “Receive” message tracking event as the basis of the calculation. Every message should have at least one “Receive” event, but it isn’t necessarily 100% accurate in terms of only counting each unique message once. Consider it a reasonable estimate rather than an exact number.

      I’ll be posting more message tracking solutions for things like number of incoming/outgoing messages soon too.

  26. Dan

    Can logparser look for all log files in subfolders? I’m working on a script that copies all the message tracking logs to subfolders as they are named the same on the servers. Then I want to run a line that builds a chart from all the log files witihin the subfolders. Can I point at c:logs only?
    c:logs
    c:logsservera
    c:logsserverb
    c:logsserverc

    1. Paul Cunningham

      I’m working on a follow up post for that type of scenario (multiple log sources) since it is one that I am dealing with as well 🙂

      1. Willie

        Did you ever find out how to deal with multiple log sources?

        1. Alberto Varas

          With logparser you can point to different log locations separating them by “coma” “,” for example:

          logparser -i:csv -nSkipLines:4 “Select * from \Server1d$*log, \Server2d$*log,\Server3d$*log ” -o:datagrid

Leave a Reply