Calculate Hourly Email Traffic using Message Tracking Logs and Log Parser

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.2\logparser.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.2\logparser.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.

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.2\logparser.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.2\logparser.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

 

Comments

  1. Tim M says

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

    • says

      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 :)

  2. CypherBit says

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

  3. David says

    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.

  4. Pete says

    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?

  5. Kevin says

    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 Files\Microsoft\Exchange Server\V14\TransportRoles\Logs\MessageTracki
    ng>”C:\Program Files (x86)\Log Parser 2.2\logparser.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”

  6. David K says

    I am trying to run just the first step where you use : (i modified my install directory)
    c:\downloads\log_parser2_2\logparser.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??

      • David K says

        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.2\logparser.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

  7. JB says

    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

    • says

      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.

      • JB says

        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

  8. Amit says

    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

  9. Daniel says

    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

  10. Rateb Abu Hawieleh says

    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

  11. n3v3n says

    Hi Paul,

    I have problem with Log Parser 2.2

    If I run

    “C:\Program Files (x86)\Log Parser 2.2\logparser.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:\ZZZZZZ\fsdfdsfds\erwrew\E03.log”: The process cannot access the file because it is being used by another process.

    can You help?

    regards,

    n3v3n

    • says

      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.

  12. Ed says

    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

  13. Nicolas Govaerts says

    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

  14. Sivakumar says

    Hi Paul,

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

    //

    [PS] C:\Windows\system32>”C:\Program Files (x86)\Log Parser 2.2\logparser.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.2\logparser.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

  15. Kyle says

    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

Leave a Reply

Your email address will not be published. Required fields are marked *