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.2\logparser.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.2\logparser.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
About Paul Cunningham
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:\logs\servera
c:\logs\serverb
c:\logs\serverc
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
One more question regarding this post. Is this just received messages are send/receive?
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.
Great report out and super easy! Thanks!
Paul, did you compare the output from message tracking to the ones that exchange provide automatically (transportroles\logs\serverstats), do they match ?
Nice tip, thanks!
I had some difficulty getting the charting to work on a Win7 64bit machine with Office 2010, but with the help of the info at this link (http://markedeyoung.blogspot.ca/2010/11/log-parser-charts-on-64-bit-windows.html), I got a copy of Office Web Components working with Log Parser.
For different chart types that Log Parser supports, run “LogParser -h -o:CHART”.
This is great Paul! Just a quick question, is it safe to install Office on a production HT server?
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.
Can you have the results written out to a file?
Whenever I run the following command
“C:\Program Files (x86)\Log Parser 2.2\logparser.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
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.
Possible, yes I imagine so. But I don’t have a code sample for you sorry
Of cause I don’t ask you to write script for me
But theoretically what you think how I can do that? We have edge servers, so if I will calculate ‘send’ and ‘recieve’ event on them – this will be it?
I found this script http://blog.dargel.at/2012/01/26/viewing-total-incomingoutgoing-email-statistics/, so I think it’s something to start with.
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.
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?
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