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.
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
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.
can you specify a single receive connector on all servers also?
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
Hi Paul,
Is it possible the get the message sizes along with message count through log parser?
Regards,
Mohan KV
where is more information about the option “-rpt:-1”,
i fix it , from CMD works perfect .
br
leo
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” <<<
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
What have you tried so far?
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
The problem is most likely that you’re running it from PowerShell. You should run it from CMD prompt or the LogParser prompt.
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
There’s a script written by another person that can do that.
http://blogs.technet.com/b/neiljohn/archive/2011/08/09/user-profile-analysis-for-exchange-server-2010.aspx
thanks alot… 😉
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
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
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.
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
Hi. May be you can help me?
I need ti know, how many the megabyte goes on I pull down in day?
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
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
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.
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
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.]’
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??
Double-check your syntax.
“group by houdir r ”
Looks like a copy/paste issue perhaps?
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
Same problem 🙁
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”
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 🙂
Looks good thanks for the update.
As always keep up the good work!
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?
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.
Article updated to demonstrate a per hour per day report as well as the original per hour totals.
I have the same question as Tim and have configured 60 days worth of message tracking logs.
Stay tuned, will have the solution posted soon 🙂
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 🙂