Searching Exchange Server 2010 Message Tracking Logs with PowerShell

In my introduction to Exchange Server 2010 message tracking I wrote that PowerShell provides one of the most useful and powerful ways to search message tracking logs.

Although the message tracking log explorer is fine for simple searches on a single server, it doesn’t work so well when you want to do wildcard searches, search multiple servers at once, or export data for further analysis.

Message tracking log explorer

For those operations PowerShell is the way to go, and frankly once you’ve seen how powerful PowerShell is for message tracking log searches you’ll probably never use the explorer tool again.

Getting Started with Searching Message Tracking Logs Using PowerShell

Message tracking log searches are performed in the Exchange Management Shell by running the Get-MessageTrackingLog cmdlet. You can run this cmdlet with no parameters on any Edge Transport, Hub Transport or Mailbox server and it will return all of the log entries on that server.

[PS] C:\>Get-MessageTrackingLog

EventId    Source      Sender                                 Recipients                               MessageSubject
-------    ------      ------                                 ----------                               --------------
RECEIVE    SMTP        Ivana.Ferrary@exchangeserverpro.net    {Ana.Williams@exchangeserverpro.net}     Luminary asep...
DELIVER    STOREDRIVER John.Tilleray@exchangeserverpro.net    {Lucy.Spratley@exchangeserverpro.net}    Egress concor...
RECEIVE    STOREDRIVER Ambrine.Berry@exchangeserverpro.net    {Lorraine.Oza@exchangeserverpro.net}     Brazen
DELIVER    STOREDRIVER Ivana.Ferrary@exchangeserverpro.net    {Ana.Williams@exchangeserverpro.net}     Luminary asep...
RECEIVE    SMTP        Donna.A'Bear@exchangeserverpro.net     {Marc.Itoje@exchangeserverpro.net}       Garble expedi...
RECEIVE    SMTP        Priya.Smith@exchangeserverpro.net      {Melanie.Thomas@exchangeserverpro.net}   Enthral boorish
DELIVER    STOREDRIVER Donna.A'Bear@exchangeserverpro.net     {Marc.Itoje@exchangeserverpro.net}       Garble expedi...
RECEIVE    SMTP        Jane.Martin@exchangeserverpro.net      {Charlotte.Bonsey@exchangeserverpro.net} Palate causti...
DELIVER    STOREDRIVER Ambrine.Berry@exchangeserverpro.net    {Lorraine.Oza@exchangeserverpro.net}     Brazen
RECEIVE    SMTP        Yvette.Willis@exchangeserverpro.net    {Jan.Marway@exchangeserverpro.net}       Malign edible
DELIVER    STOREDRIVER Priya.Smith@exchangeserverpro.net      {Melanie.Thomas@exchangeserverpro.net}   Enthral boorish
RECEIVE    STOREDRIVER Joy.Singh@exchangeserverpro.net        {Mary.Friel@exchangeserverpro.net}       Obfuscate
HAREDIRECT ROUTING     Joy.Singh@exchangeserverpro.net        {Mary.Friel@exchangeserverpro.net}       Obfuscate
....

You can also search a remote server using the -Server parameter. This is useful when you are running the search from your own admin workstation or a separate management server.

[PS] C:\>Get-MessageTrackingLog -Server HO-EX2010-MB2

The Get-MessageTrackingLog cmdlet also accepts input from the pipeline. This is a very convenient way to perform searches on multiple servers at once. For example to search all Hub Transport servers at once:

[PS] C:\>Get-TransportServer | Get-MessageTrackingLog

Sometimes you may wish to search the transport servers only within a particular site. Because I might need to work with that list in a few different commands I’ll usually collect those into a variable first, for example all Hub Transport servers in the “HeadOffice” site:

[PS] C:\>$hubs = Get-ExchangeServer | Where {$_.Site -like "*HeadOffice" -and $_.IsHubTransportServer -eq $true}

I can then pipe that array of servers into the Get-MessageTrackingLog cmdlet.

[PS] C:\>$hubs | Get-MessageTrackingLog

Remember Default Resultsize of 1000

Often you will be running message tracking log searches that return a lot of results. However, by default the cmdlet will return only 1000 results.

Because of this you should try to get in to the habit of using the -Resultsize parameter to return unlimited results when running Get-MessageTrackingLog.

[PS] C:\>Get-TransportServer | Get-MessageTrackingLog -Resultsize unlimited

Run Long Queries Once by Collecting Results in a Variable

When you’re performing investigative searches of your message tracking logs, particularly across multiple servers, those queries can take a long time to return the results. If you then found you needed to adjust the query, for example to be more specific, or to format the results in a different way, you have to wait a long time for the query to run a second time as well.

So a good tip is to always collect your query results into a variable, particularly very broad queries that take a long time to run, so that you can pick apart the collected data without having to re-run the query.

For example, if I want to investigate reports of email problems sending to Alan Reid, I can run one broad query across all Hub Transport servers and collect the results in a variable I will call $msgs.

[PS] C:\>$msgs = Get-TransportServer | Get-MessageTrackingLog -Recipients "Alan.Reid@exchangeserverpro.net" -resultsize unlimited

I’ve now got thousands of records that I can begin to filter and dissect in different ways without having to re-run my query.

[PS] C:\>$msgs.count
13297

For example I can find the top 10 senders to Alan Reid within seconds, instead of re-running the entire Get-MessageTrackingLog search again.

[PS] C:\>$msgs | Group-Object -Property Sender | Select-Object name,count | sort count -desc | select -first 10 | ft -auto

Name                                  Count
----                                  -----
Andrea.Sharma@exchangeserverpro.net     110
Richard.Bennett@exchangeserverpro.net   108
Judy.Mollo@exchangeserverpro.net        104
Ferzana.King@exchangeserverpro.net      102
Debra.Lowe@exchangeserverpro.net        100
Nicola.Noad@exchangeserverpro.net       100
Diane.Hall@exchangeserverpro.net         96
Caroline.Ball@exchangeserverpro.net      96
Chris.Majumdar@exchangeserverpro.net     96
Hugh.Sharma@exchangeserverpro.net        96

According to Measure-Command the above command took 1.3 seconds to complete, whereas the re-running the full log search again would take 47.4 seconds. By collecting the results into a variable the first time all of the subsequent analysis of that data is able to be performed much faster.

Each Single Message is Multiple Log Entries

You’ll notice as you begin looking at message tracking logs that each individual email message generates multiple log entries. This is because each message goes through multiple events in the process of getting from sender to recipient, that the number of events will vary depending on how the message needs to be routed throughout your organization, as well as whether it is successfully delivered or not.

So a single email message may record a series of events such as:

EventId  Source   Sender                            Recipients                        MessageSubject
-------  ------   ------                            ----------                        --------------
RECEIVE  SMTP     Famida.Ghtoray@exchangeserverp... {Alan.Reid@exchangeserverpro.net} Prolix apropos embellish
DEFER    STORE... Famida.Ghtoray@exchangeserverp... {Alan.Reid@exchangeserverpro.net} Prolix apropos embellish
DUPLI... STORE... Famida.Ghtoray@exchangeserverp... {Alan.Reid@exchangeserverpro.net} Prolix apropos embellish
RECEIVE  STORE... Famida.Ghtoray@exchangeserverp... {Alan.Reid@exchangeserverpro.net} Prolix apropos embellish
DELIVER  STORE... Famida.Ghtoray@exchangeserverp... {Alan.Reid@exchangeserverpro.net} Prolix apropos embellish
RESUBMIT DUMPSTER Famida.Ghtoray@exchangeserverp... {Alan.Reid@exchangeserverpro.net} Prolix apropos embellish
TRANSFER STORE... Famida.Ghtoray@exchangeserverp... {Alan.Reid@exchangeserverpro.net} Prolix apropos embellish
SEND     SMTP     Famida.Ghtoray@exchangeserverp... {Alan.Reid@exchangeserverpro.net} Prolix apropos embellish

Dealing with System.String[] in Exported Message Tracking Log Data

At some stage you will want to export some message tracking log data to CSV for further analysis in Excel. If you were to pipe the output above into the Export-CSV cmdlet you will notice that some of the fields, such as Recipients, will appear as System.String[] in the output file.

The Export-CSV System.String problem

To resolve this issue you need to first pipe your Get-MessageTrackingLog results into Select-Object and select the Recipients and RecipientStatus fields like this:

... | Select-Object eventid,sender,timestamp,@{Name="Recipients";Expression={$_.recipients}},@{Name="RecipientStatus";Expression={$_.recipientstatus}},messagesubject | Export-CSV filename.csv

This will give you the correct exported data.

Correct Recipients and RecipientStatus output

Examples of Message Tracking Log Searches

In the next part of this article series I’ll cover some specific examples of message tracking log searches using PowerShell.

• Searching Message Tracking Logs by Time and Date Range
• Searching Message Tracking Logs by Sender or Recipient Email Address
• Searching Message Tracking Logs by Email Subject
• Speed Up Multi-Server Message Tracking Log Searches with PowerShell Remoting