Wednesday, October 22, 2014

How to export message tracking log results as a CSV file in Exchange Server 2010

I recently had to troubleshoot an issue for a client where their log drive filled up and wanted to identify whether there was a device that was generating excessive logs on the Exchange server or whether they genuinely received that much mail between the last backup and the time when the drive filled up.  To determine whether the Exchange Organization did indeed receive enough mail to fill up the log drive, I directed my attention to the Tracking Log Explorer.  For those who are familiar with the Exchange Server 2010’s Tracking Log Explorer feature located in Microsoft Exchange –> Microsoft Exchange On-Premises –> Toolbox –> Tracking Log Explorer:

image

… would probably have come across this situation where you would like to export the returned table results:

image

… but noticed that there does not appear to be a way to do so via the GUI:

image 

The GUI does not provide a way to export these logs to a text or CSV file and therefore there wasn’t a way for me easily add up the TotalBytes column to get an idea as to how much mail was send and received:

image

I recall asking a Microsoft support engineer years ago about this for Exchange 2007 and the answer was that there was no way but seeing how it has been such a long time, I went ahead and did a quick Google search which returned this Exchange Team Blog post:

How to Easily Export Message Tracking Results in Exchange Server 2007
http://blogs.technet.com/b/exchange/archive/2008/12/01/3406581.aspx

This was exactly what I wanted because I could sum TotalBytes column but the command was a screenshot which meant I had to type it out.  It was a bit annoying to have to type all that out when I was in a rush to get the results to the client at 1a.m. in the morning so to avoid having to do this again in the future, I’m going to paste the command here for reference if I ever needed it again:

get-messagetrackinglog -Server "<replaceWithServerName>" -Start "10/20/2014 10:15:00 PM" -End "10/21/2014 12:35:00 PM" -resultsize unlimited | select timestamp, eventid, source, sourcecontext, messageid, messagesubject, sender, {$_.recipients}, internalmessageid, clientip, clienthostname, serverip, serverhostname, connectorid, {$_.recipientstatus}, totalbytes, recipientcount, relatedrecipientaddress, reference, returnpath, messageinfo | export-csv c:\results.csv

Note that I added the “-resultsize unlimited” switch at the end of get-messagetrackinglog to allow unlimited results to be returned.

In addition to using this export to total up each message size to get an idea of how much logs should be generated, another useful tool for troubleshooting this issue is to download the:

Microsoft Exchange Server User Monitor
http://www.microsoft.com/en-us/download/details.aspx?id=11461

This tool allows you to monitor individual user’s utilization of the Exchange server including the amount of Bytes In and Bytes Out:

image