Home Β» Exchange Server Β» Calculate Hourly Email Traffic using Message Tracking Logs and Log Parser

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.

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

This will return results similar to these:

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

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.

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

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

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.

 

Paul is a Microsoft MVP for Office Servers and Services. He works as a consultant, writer, and trainer specializing in Office 365 and Exchange Server. Paul is a co-author of Office 365 for IT Pros and several other books, and is also a Pluralsight author.
Category: Exchange Server

34 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

    • 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. 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.

  3. 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?

  4. 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 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”

  5. David K says:

    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??

      • 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.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

  6. 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

    • 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

  7. 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

  8. 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

  9. 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

  10. n3v3n says:

    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

  11. 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

  12. 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

  13. Sivakumar says:

    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

  14. 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

  15. Leonard says:

    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” <<<

Leave a Reply

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