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.2logparser.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.2logparser.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
世界一流スーパーコピー 時計
※弊社は海外安心と信頼のスーパーコピー 時計です。
※2018 新作が満載！皆様を歓迎して当店をご光臨賜ります。
ロレックススーパーコピー,パネライスーパーコピー,ウブロスーパーコピー,シャネル スーパーコピー等。
サイトは世界一流スーパーコピー 時計専門店です。
ぜひ一度当店の商品をお試しください。
驚きと満足を保証致します。ご利用をお待ちしております。
Very useful script, thanks. Is there anything that needs to be modified in this string in order for it to process all of logs in the MessageTracking directory. Currently it will only process 19 out of 83 logs on one sever I have. My reading of the stings says it should parse all of the logs based on “*.log” being in the string.
Any ideas on why it’s not?
The Real Person!
Author Paul Cunningham acts as a real person and passed all tests against spambots. Anti-Spam by CleanTalk.
No idea. I would assume *.log will get them all.
Hi Paul,
The script works just fine, i was wondering if we can tweak this script for a particular exchange database, your help is appreciated.
Regards
-Inderjit
Hi paul thanks again for this useful article,just one question,how can i make this amazing report for a specific mailbox?
Paul,
I am looking to have this script run everyday via task scheduler. It is looking for messages sent to each DL every day. So I manually ran this one with todays date, need to have it run each day with that days date and time
I m stuck on how to get working, any ideas?
get-TransportServer | Get-MessageTrackingLog -Resultsize unlimited -EventID Expand -Start “10/5/2016 12:01AM” -end “10/5/2016 11:59PM” | ? {$_.RelatedRecipientAddress -like “DistroList@abc.com”} | ft Timestamp,Sender,MessageSubject -Autosize
Has to check these DL as well every day
DistroList1@abc.com
DistroList2@abc.com
DistroList3@abc.com
Paul,
I am looking to have this script run everyday via task scheduler. It is looking for messages sent to each DL every day. So I manually ran this one with todays date, need to have it run each day with that days date and time. Save the results as .csv And email that csv to Me@abc.com
I m stuck on how to get working, any ideas?
get-TransportServer | Get-TransportServer | Get-MessageTrackingLog -Resultsize unlimited -EventID Expand -Start “10/5/2016 12:01AM” -end “10/5/2016 11:59PM” | ? {$_.RelatedRecipientAddress -like “DistroList@abc.com”} | ft Timestamp,Sender,MessageSubject -Autosize
Has to check these DL as well every day
DistroList1@abc.com
DistroList2@abc.com
DistroList3@abc.com
Is it possible to get this report for subdomains? Company X hosts emails for 10 different domains. Would like to get a report of total mail volume for each domain.
The Real Person!
Author Paul Cunningham acts as a real person and passed all tests against spambots. Anti-Spam by CleanTalk.
You can create any query you like. Look at the log file that is being queried by Log Parser, and you’ll see which fields are available. You can then modify the Log Parser query to filter to specific domains.
But I suspect you’ll find it far more cost effective to buy a reporting solution that can do this for you.
when I copy n paste the query from within the log parser studio, it says to select log type and so far I tried a few doesn’t seem to work ..
the default queries use type EELLOG.
Hi Paul, Just wanted to know, I am running Exchange 2010 and wanted to get the report of outgoing emails with or without TLS.
Can I get that report in Exchange 2010?
Thank you,
Adnan
The Real Person!
Author Paul Cunningham acts as a real person and passed all tests against spambots. Anti-Spam by CleanTalk.
It isn’t a built-in report. You could possibly infer it from the protocol logs.
Pingback: Gathering Exchange 2010 mail flow statistics
Great post!!! Works like a champ on my 2013 Edge servers!
how can I count smime messages sent
I receive the following error when trying to run the reports:
Error: WHERE Clause; Syntax Error: unknown field ‘event-id’
It should be “event” only not “event-id”.
Hi Paul
as i am planning to migrate from exchange 2010 to 2013 on premises. how can i calculate daily send item in exchange server 2010. for receiving messages i am using iGetmail. need your guidance.
Thanks
Hi Paul
When i run your group command below. i am getting error like “Error: invalid parameter “chartType””
One more how do i get the sending message count
Your command:
“C:Program Files (x86)Log Parser 2.2logparser.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
I am getting error below, can you please suggest me.
Error: invalid parameter “chartType”
Thanks.
The Real Person!
Author Paul Cunningham acts as a real person and passed all tests against spambots. Anti-Spam by CleanTalk.
Are you running it from the Log Parser prompt?
Yes.. i have office 2007, is need to install office web components.
Paul
how do i take send mail count ?
check path in script, missing \ sign
paul, can reports of incoming/outgoing be specific to activesync?
The Real Person!
Author Paul Cunningham acts as a real person and passed all tests against spambots. Anti-Spam by CleanTalk.
No I don’t think message tracking logs contain any data that would help you separate that out.
You can use the IIS logs to look at ActiveSync traffic in general. Log Parser Studio comes with some preset reports for that.
First of all, this is fantastic stuff – I was able to get this working on my Win7 Management workstation with no issues
minor challenge though : I keep my Exchange 2010 Tracking Logs for 6 months in the Exchange Tracking Logs DIR so the resulting .GIF is cluttered. When I copy some logs to another folder say Z:TrackingLogs the CD to that DIR I get no input in my statistics from Log Parser CMD and an empty GIF file. Any thought as to why this happens?
e.g.
Statistics:
————-
Elements processed: 124415
Elements output: 0
Execution time: 0.89 seconds
Hi Paul,
I’ve installed the log parser on a Exchange machine and copied the Message tracking logs to a folder in that machine and running the above command giving me following error.
PS E:KishoreMessage_track_logs_HUB01> “C:Program Files (x86)Log Parser 2.2logparser.exe” “SELECT TO_LOCALTIME(TO_TI
MESTAMP(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
Error message
———————
Unexpected token ‘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’ in expression or sta
tement.
At line:1 char:255
+ “C:Program Files (x86)Log Parser 2.2logparser.exe” “SELECT TO_LOCALTIME(TO_TIMESTAMP(EXTRACT_PREFIX(TO_STRING([#Fi
elds: 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
+ CategoryInfo : ParserError: (SELECT TO_LOCAL…DER BY Date ASC:String) [], ParentContainsErrorRecordExc
eption
+ FullyQualifiedErrorId : UnexpectedToken
—————————————————–
The Real Person!
Author Paul Cunningham acts as a real person and passed all tests against spambots. Anti-Spam by CleanTalk.
Run Logparser from a command prompt instead of from Powershell.
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
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?
The Real Person!
Author Paul Cunningham acts as a real person and passed all tests against spambots. Anti-Spam by CleanTalk.
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.
The Real Person!
Author Paul Cunningham acts as a real person and passed all tests against spambots. Anti-Spam by CleanTalk.
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.
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.
Pingback: Exploring a New Exchange Server Environment
Whenever I run the following command
“C:Program Files (x86)Log Parser 2.2logparser.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
Can you have the results written out to a file?
I think you could use after the command the following
” | export-csv NameOfFile.csv”
or
” > NameOfFile.txt ”
It will be saved in the directory in which you run the command.
Don’t use the double quotes it is just to indicate that you should copy in between the quotes.
This is great Paul! Just a quick question, is it safe to install Office on a production HT server?
The Real Person!
Author Paul Cunningham acts as a real person and passed all tests against spambots. Anti-Spam by CleanTalk.
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.
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”.
Paul, did you compare the output from message tracking to the ones that exchange provide automatically (transportroleslogsserverstats), do they match ?
Great report out and super easy! Thanks!
One more question regarding this post. Is this just received messages are send/receive?
The Real Person!
Author Paul Cunningham acts as a real person and passed all tests against spambots. Anti-Spam by CleanTalk.
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.
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:logsservera
c:logsserverb
c:logsserverc
The Real Person!
Author Paul Cunningham acts as a real person and passed all tests against spambots. Anti-Spam by CleanTalk.
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 🙂
Any news on this?
Did you ever find out how to deal with multiple log sources?
With logparser you can point to different log locations separating them by “coma” “,” for example:
logparser -i:csv -nSkipLines:4 “Select * from \Server1d$*log, \Server2d$*log,\Server3d$*log ” -o:datagrid