Message Tracking: Keeping your boss off your back.

I've found the Exchange 2007's Message Tracking to be very helpful in determining the flow of messages. In a recent Exchange migration, there were questions if email was getting to where it was supposed to go . The combined use of Message Tracking, powershell and Excel spreadsheets has provided systematic methods to analyze the message traffic. The following scenario is an example:

A mailbox is set to forward messages to another email address in a separate organization. Your boss is getting heat that these messages are getting dropped. Using the Message Tracking, you can generate a get-messagetrackinglog command to your liking. Then in the Exchange powershell, you can run the command with the export-csv command as below.
Received messages:
[PS] C:\Documents and Settings\administrator.FW>get-messagetrackinglog -Recipients:aperson@domain.com -EventID "RECEIVE" -Start "4/29/2008 7:04:00 AM" -End "5/6/2008 7:14:00 AM" export-csv c:\temp\aperson.ex2k7.receive.csv

Sent messages:
[PS] C:\Documents and Settings\administrator.FW>get-messagetrackinglog -Recipients:a.person@elsewhere.net -EventID "SEND" -Start "4/29/2008 7:04:00 AM" -End"5/6/2008 7:14:00 AM" export-csv c:\temp\a.person.ex2k7.send.csv

With the csv output in hand, I created an Excel workboot with macros. (Excel 2007 requires you to use the .xlsm extension for these type of spread sheets.) The csv output is pasted into two separate spreadsheets within the workbook. The macro below is an example of how to search for messageID of one spreadsheet in the other. If a match was found (indicated by the matchSend boolean variable), the format of each spread sheet was adjusted to provide a visual depiction of the comparison.


Public Sub checkSendMessageID()

Dim recieveEx2k7worksheet As Worksheet
Dim sendEx2k7worksheet As Worksheet
Dim currentMessageID As Range
Dim sendMessageColumn As Range
Dim sendMessageID As Range
Dim receiveMessageID As Range
Dim matchsend As Boolean
Dim matchreceive As Boolean

Set receiveEx2k7Worksheet = Sheets("aperson.Ex2k7.receive")
Set sendEx2k7worksheet = Sheets("a.person.Ex2k7.send")
Set currentMessageID = receiveEx2k7Worksheet.Cells(3, 11)
Set sendMessageID = sendEx2k7worksheet.Cells(3, 11)
Set sendMessageColumn = sendEx2k7worksheet.Columns(11)

matchsend = Falsematchreceive = False
Do Until IsEmpty(currentMessageID.Value)
Set sendMessageID = sendMessageColumn.Find(currentMessageID.Value)
If sendMessageID Is Nothing Then
matchsend = False
Else
matchsend = True
sendMessageID.Font.Bold = True
sendMessageID.Font.Color = RGB(0, 255, 0)
End If
If matchsend Then
currentMessageID.Font.Bold = True
currentMessageID.Font.Color = RGB(0, 255, 0)
End If

matchsend = False
Set currentMessageID = currentMessageID.Offset(1, 0)
Loop
End Sub

In my case, only two messages were dropped. To investigate this, I used the FAIL eventID in the Message Tracking to locate the dropped messages. This indicated that they were dropped by the receiving Exchange server which had SPAM filtering enabled. (BTW, the transport logs "C:\program files\microsoft\Exchange Server\TransportRoles\Logs\AgentLog" on the receiving server can also confirmed this. )