Most Exchange server environments will demonstrate a predictable volume of email traffic throughout a typical day. However from time to time some problem may arise that causes an unusual spike in email traffic.

To be able to identify this type of problem you should first know what your normal email traffic patterns look like. You can find this information by using Log Parser to search through your Exchange server’s message tracking logs.

There are two ways to look at this traffic.

  • As a total of the traffic per hour for all days combined
  • As the traffic per hour for each separate day

Total Email Traffic Per Hour for All Days Combined

First let’s look at the Log Parser query to calculate the total email traffic per hour for all days combined.

SELECT QUANTIZE(TO_LOCALTIME(TO_TIMESTAMP(EXTRACT_PREFIX(TO_STRING(EXTRACT_SUFFIX([#Fields: date-time],0,'T')),0,'.'), 'hh:mm:ss')),3600) AS Hour,
	COUNT(*) AS Messages
FROM *.log
WHERE (event-id='RECEIVE')
GROUP BY Hour
ORDER BY Hour ASC

When run from the folder where the message tracking logs are located the full syntax is:

"C:Program Files (x86)Log Parser 2.2logparser.exe" "SELECT QUANTIZE(TO_LOCALTIME(TO_TIMESTAMP(EXTRACT_PREFIX(TO_STRING(EXTRACT_SUFFIX([#Fields: date-time],0,'T')),0,'.'), 'hh:mm:ss')),3600) AS Hour, COUNT(*) AS Messages from *.log where (event-id='RECEIVE') GROUP BY Hour ORDER BY Hour ASC" -i:CSV -nSkipLines:4 -rtp:-1

This will return results similar to these:

Hour     Messages
-------- --------
00:00:00 408
01:00:00 415
02:00:00 363
03:00:00 347
04:00:00 273
05:00:00 327
06:00:00 403
07:00:00 450
08:00:00 590
09:00:00 574
10:00:00 637
11:00:00 810
12:00:00 612
13:00:00 597
14:00:00 700
15:00:00 789
16:00:00 821
17:00:00 448
18:00:00 396
19:00:00 527
20:00:00 346
21:00:00 476
22:00:00 348
23:00:00 448

Statistics:
-----------
Elements processed: 106823
Elements output:    24
Execution time:     0.58 seconds

You can also generate graphs straight from Log Parser (if you have the required Office Web Components installed) with slightly different syntax like this:

"C:Program Files (x86)Log Parser 2.2logparser.exe" "SELECT QUANTIZE(TO_LOCALTIME(TO_TIMESTAMP(EXTRACT_PREFIX(TO_STRING(EXTRACT_SUFFIX([#Fields: date-time],0,'T')),0,'.'), 'hh:mm:ss')),3600) AS Hour, COUNT(*) AS [Messages per Hour] INTO HourlyTraffic.gif from *.log where (event-id='RECEIVE') GROUP BY Hour ORDER BY Hour ASC" -i:CSV -nSkipLines:4 -chartType:Column3D

That Log Parser query will generate a column graph for you similar to this.

Calculate Hourly Email Traffic using Message Tracking Logs and Log Parser

Total Email Traffic Per Hour for Each Separate Day

If you’d rather see the hourly traffic for each separate day you can use the following Log Parser query that handles the timestamps slightly differently.

SELECT TO_LOCALTIME(QUANTIZE(TO_TIMESTAMP(TO_STRING(EXTRACT_PREFIX([#Fields: date-time],0,'.')), 'yyyy-MM-ddThh:mm:ss'),3600)) AS Hour,
	COUNT(*) AS Messages
FROM *.log
WHERE event-id='RECEIVE'
GROUP BY Hour
ORDER BY Hour ASC

The full Log Parser command when run from the folder containing the message tracking logs is as follows:

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

This will generate a lot of output depending on the amount of log retention you have configured.

Hour                Messages
------------------- --------
2012-04-05 10:00:00 48
2012-04-05 11:00:00 77
2012-04-05 12:00:00 35
2012-04-05 13:00:00 74
2012-04-05 14:00:00 84
2012-04-05 15:00:00 86
2012-04-05 16:00:00 79
2012-04-05 17:00:00 35
2012-04-05 18:00:00 44
2012-04-05 19:00:00 24
2012-04-05 20:00:00 41
2012-04-05 21:00:00 19
2012-04-05 22:00:00 40
2012-04-05 23:00:00 59
2012-04-06 00:00:00 28
2012-04-06 01:00:00 42
2012-04-06 02:00:00 22
2012-04-06 03:00:00 31
2012-04-06 04:00:00 16
2012-04-06 05:00:00 31
2012-04-06 06:00:00 40
2012-04-06 07:00:00 22
2012-04-06 08:00:00 84
2012-04-06 09:00:00 77
2012-04-06 10:00:00 46
2012-04-06 11:00:00 84
....

As with other Log Parser results you can output this one to a graph as well (again, providing that you have the Office Web Components installed).

Because of the amount of data a line graph seems more appropriate than a column graph. The default size of a graph generated by Log Parser is 640×480, but you can use the -groupsize parameter to set a custom width x height that suits the amount of data being included.

"C:Program Files (x86)Log Parser 2.2logparser.exe" "SELECT TO_LOCALTIME(QUANTIZE(TO_TIMESTAMP(TO_STRING(EXTRACT_PREFIX([#Fields: date-time],0,'.')), 'yyyy-MM-ddThh:mm:ss'),3600)) AS Hour, COUNT(*) AS Messages INTO DailyTrafficPerHour.gif from *.log where event-id='RECEIVE' GROUP BY Hour ORDER BY Hour ASC" -i:CSV -nSkipLines:4 -chartType:Line -groupsize:1024x480

 

Calculate Hourly 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. Siva

    Hi Paul,

    Is there a way to generate a similar report on Exchange Online(O365)?

    I mean daily message tracking report count send and received.

  2. Joshua B

    can you specify a single receive connector on all servers also?

  3. Priya

    Hi Paul,
    Very useful script…works like a charm!
    Does this conver the time to Local time based on server time or the results are based on UTC.
    Cheers
    Priya

  4. Mohan KV

    Hi Paul,

    Is it possible the get the message sizes along with message count through log parser?

    Regards,
    Mohan KV

  5. Victor Garcia

    where is more information about the option “-rpt:-1”,

  6. Leonard

    i fix it , from CMD works perfect .

    br
    leo

  7. Leonard

    I have the followin error .

    what to do ???

    [PS] C:Program FilesMicrosoftExchange ServerV14TransportRolesLogsMessageTracking>”C:Program Files (x86)Log Pars
    er 2.2logparser.exe” “SELECT QUANTIZE(TO_LOCALTIME(TO_TIMESTAMP(EXTRACT_PREFIX(TO_STRING(EXTRACT_SUFFIX([#Fields: date-
    time],0,’T’)),0,’.’), ‘hh:mm:ss’)),3600) AS Hour, COUNT(*) AS Messages from *.log where (event-id=’RECEIVE’) GROUP BY Ho
    ur ORDER BY Hour ASC” -i:CSV -nSkipLines:4 -rtp:-1
    Unexpected token ‘SELECT QUANTIZE(TO_LOCALTIME(TO_TIMESTAMP(EXTRACT_PREFIX(TO_STRING(EXTRACT_SUFFIX([#Fields: date-time
    ],0,’T’)),0,’.’), ‘hh:mm:ss’)),3600) AS Hour, COUNT(*) AS Messages from *.log where (event-id=’RECEIVE’) GROUP BY Hour
    ORDER BY Hour ASC’ in expression or statement.
    At line:1 char:294
    + “C:Program Files (x86)Log Parser 2.2logparser.exe” “SELECT QUANTIZE(TO_LOCALTIME(TO_TIMESTAMP(EXTRACT_PREFIX(TO_ST
    RING(EXTRACT_SUFFIX([#Fields: date-time],0,’T’)),0,’.’), ‘hh:mm:ss’)),3600) AS Hour, COUNT(*) AS Messages from *.log wh
    ere (event-id=’RECEIVE’) GROUP BY Hour ORDER BY Hour ASC” <<<

  8. Kyle

    Hi Paul,

    How should someone go about rewriting this script if they do not have the “date-time” field? We are on an Exchange 2007 environment and our IIS W3 logs do have the date-time parameter. We only have “Date” and “Time” separately. When I attempt to run the script I get the error:

    “Error parsing query: SELECT clause: Syntax Error: unknown field ‘#Fields: date-time’.
    The closest match for input format ‘IISW3C’ is ‘date’. [Record field does not exist.]

    Thanks for an assistance you can provide!

    Kyle

  9. Sivakumar

    Hi Paul,

    I am getting below error message when i try to run the command.

    //

    [PS] C:Windowssystem32>”C:Program Files (x86)Log Parser 2.2logparser.exe” “SELECT QUANTIZE(TO_LOCALTIME(TO_TIMESTAM
    P(EXTRACT_PREFIX(TO_STRING(EXTRACT_SUFFIX([#Fields: date-time],0,’T’)),0,’.’), ‘hh:mm:ss’)),3600) AS Hour, COUNT(*) AS M
    essages from *.log where (event-id=’RECEIVE’) GROUP BY Hour ORDER BY Hour ASC” -i:CSV -nSkipLines:4 -rtp:-1
    Unexpected token ‘SELECT QUANTIZE(TO_LOCALTIME(TO_TIMESTAMP(EXTRACT_PREFIX(TO_STRING(EXTRACT_SUFFIX([#Fields: date-time
    ],0,’T’)),0,’.’), ‘hh:mm:ss’)),3600) AS Hour, COUNT(*) AS Messages from *.log where (event-id=’RECEIVE’) GROUP BY Hour
    ORDER BY Hour ASC’ in expression or statement.
    At line:1 char:294
    + “C:Program Files (x86)Log Parser 2.2logparser.exe” “SELECT QUANTIZE(TO_LOCALTIME(TO_TIMESTAMP(EXTRACT_PREFIX(TO_ST
    RING(EXTRACT_SUFFIX([#Fields: date-time],0,’T’)),0,’.’), ‘hh:mm:ss’)),3600) AS Hour, COUNT(*) AS Messages from *.log wh
    ere (event-id=’RECEIVE’) GROUP BY Hour ORDER BY Hour ASC” <<<< -i:CSV -nSkipLines:4 -rtp:-1
    + CategoryInfo : ParserError: (SELECT QUANTIZE…DER BY Hour ASC:String) [], ParentContainsErrorRecordExc
    eption
    + FullyQualifiedErrorId : UnexpectedToken

    //

    Need your help to fix this issue.

    Thanks,
    Sivakumar

    1. Paul Cunningham

      The problem is most likely that you’re running it from PowerShell. You should run it from CMD prompt or the LogParser prompt.

  10. Nicolas Govaerts

    Hello Paul,
    I am seraching the right command line to know how much email users are sending/receiving for the last 30 days.
    I need to know, for every mailbox, how many emails they have treaten each day.

    I would appreciate your help, i’m getting crazy…

    Thanks

    Nicolas

      1. Nicolas Govaerts

        thanks alot… πŸ˜‰

  11. Ed

    Hi Paul,

    Is there a way to get the amount of data (in MB) the Exchange server sends and receives? Is this information kept anywhere in the logs?

    Thanks for your time and help.

    Regards,
    Ed

  12. n3v3n

    Hi Paul,

    I have problem with Log Parser 2.2

    If I run

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

    then I receive error message

    Error: Error while opening file “X:ZZZZZZfsdfdsfdserwrewE03.log”: The process cannot access the file because it is being used by another process.

    can You help?

    regards,

    n3v3n

    1. Paul Cunningham

      E03.log looks like you’re trying to parse transaction logs. Log Parser won’t work on transaction logs. This article demonstrates using Log Parser with message tracking logs.

  13. Rateb Abu Hawieleh

    Dear Paul,

    I ran the queries for both sent and received separately against the Edge server log parser. the output was way higher than expectations such the max number of emails (in/out) during the last 30 days in peak hours 3500 while the total number of mailboxes is way less than 500.

    the question here, we are only analyzing the log parser on the server we run the query on or this generates the overall email statistics regardless if it was internal or external?

    I would really appreciate your kind response.

    Regards,
    Rateb

  14. Cloom

    Hi. May be you can help me?
    I need ti know, how many the megabyte goes on I pull down in day?

  15. Daniel

    Hi, thanks for your help…
    What can i add a DATE in “WHERE” condintional expression?
    example : WHERE event-id=’RECEIVE’ AND Date ‘X’ or WHERE event-id=’RECEIVE’ AND ‘Date’ BETWEEN ‘X’ AND ‘Y’.

    Thanks a lot
    Daniel

  16. Amit

    Hi Paul,

    Is there any option on powershell cmdlet to generate email traffic reports. Like CSV format.

    let me know if any commands are useful.

    Thanks and Regards,
    Amit

  17. JB

    If I have a set of email addresses of users for eg. 1000 in a CSV file and I would like to pull hourly details of total sent messages, receieved messages and the total message size of sent and received emails for these users. Just the count of these, am not interested in knowing how many emails each user sent. Do you think this can be done using Log Parser? I would really appreciate all your help in this.

    Thanks.
    JB

    1. Paul Cunningham

      Powershell would probably be better for what you’re trying to do there.

      But also at that scale you might be better off looking at a proper reporting tool that can give you all of those stats quickly and easily.

      1. JB

        Paul,

        Thank you for replying. This is just a one time activity since we want to know how many emails and message size of the emails sent by these 1000 to 1500 users in a hourly format for the last couple of weeks so that we can get an estimate on the bandwidth used by these users.

        The example you have given above in the article is excellent and can suit my requirement but I do not know how to modify it to read the email addresses of these 1000 users and then search in the message tracking logs. If you can help in this I would be very greatful.

        Thanks,
        JB

  18. Arivoli Ram

    i am getting the below syntax error..

    error parsing query: SELECT clause:syntax Error: unknown field ‘#Fields:date-time’.The closest match for input format ‘IISW3C’ is ‘date. [record filed does not exist.]’

  19. David K

    I am trying to run just the first step where you use : (i modified my install directory)
    c:downloadslog_parser2_2logparser.exe “Select Quantize(to_localtime(to_timestamp(extract_prefix(to_string(extract_suffix([#fields: date-time],0,’T’)),0,’.’), ‘hh:mm:ss’)),3600) As Hour, Count(*) As Messages from *.log where (event-id=’receive’) group by houdir r order by hour ASC” -i:csv -nskiplines:4 -rtp:-1
    to get the parser to run. It does show the bottom where STatistics:, elements processed 821236, elements out put 0 and execution time 22.25 seconds. However, I dont seem to get the hourly totals.
    What may I be doing wrong??

    1. Paul Cunningham

      Double-check your syntax.

      β€œgroup by houdir r ”

      Looks like a copy/paste issue perhaps?

      1. David K

        Cleaned that up with a correct copy and paste (couldnt do it properly before due to wireless keyboard issue).
        In your syntax above, you show that there is a quote mark prior to the directory – “C:Program Files (x86)Log Parser 2.2logparser.exe”. I dont think I had that when I did get a run. but when I dont use it now, it errors as well with a “Select” is not an internal command.

        Does the database need to be dismounted to use this (with the quote)?? what else may I be doing incorrectly? The only diference between what you have and what I am using is the directory sturcture. everything else was a copy and paste. (unlike last time I typed it all in).
        I have to be missing something easy here….. πŸ™

        dave

        1. JOJO

          Same problem πŸ™

  20. Kevin

    The query to calculate the total email traffic works fine, however when I try the graphic report, I get Error: invalid parameter “chartType” Example is below of error.

    C:Program FilesMicrosoftExchange ServerV14TransportRolesLogsMessageTracki
    ng>”C:Program Files (x86)Log Parser 2.2logparser.exe” “SELECT TO_LOCALTIME(QU
    ANTIZE(TO_TIMESTAMP(TO_STRING(EXTRACT_PREFIX([#Fields: date-time],0,’.’)), ‘yyyy
    -MM-ddThh:mm:ss’),3600)) AS Hour, COUNT(*) AS Messages INTO DailyTrafficPerHour.
    gif from *.log where event-id=’RECEIVE’ GROUP BY Hour ORDER BY Hour ASC” -i:CSV
    -nSkipLines:4 -chartType:Line -groupsize:1024×480
    Error: invalid parameter “chartType”

    1. Paul Cunningham

      Requires Office and/or Office Web Components to be installed on the machine as well. I seem to have missed that detail in the article πŸ™‚

  21. Tim M

    Looks good thanks for the update.
    As always keep up the good work!

  22. Pete

    I’m guessing this would need to be run against logs from all transport servers individually?
    or is there a way to have it get the logs from multiple Hub transport servers at the same time?

  23. David

    May want to write a little powershell script at the bottom that will average the data out and show percentage per day so we can take the data and input it into Microsoft’s Calculator for hourly usage so we can have some more accurate replication bandwidth requirements.

    It’s obviously easy enough to do based on what you provide above. But I think having this in your article would make it that much better.

    Cheers.

  24. Paul Cunningham

    Article updated to demonstrate a per hour per day report as well as the original per hour totals.

  25. CypherBit

    I have the same question as Tim and have configured 60 days worth of message tracking logs.

  26. Tim M

    This looks like it is counts from all the log files on the system?
    How would you get the hourly by day?
    TM

    1. Paul Cunningham

      I’ve got the solution for your question I just need to tidy it up a little and generate some examples. I will update the article this weekend with the details πŸ™‚

Leave a Reply