Exchange Message Tracking Logs export to Excell

Very often we have to find emails sent from one mailbox to another.
Informations about sent messages in our Microsoft Exchange Server environment we can find in Message Tracking Logs which are located on Hub Transport servers

Mostly to explore Message Tracking Log we use GUI tool Tracking Log Explorer from Exchange Management Console. We can also use powershell to track messages which is more helpful. We can create powershell script to track messages using CMDlet Get-MessageTrackingLog.

How to use Get-MessageTrackingLog we will write next time.

Now we want to describe how to find informations about delivered messages to specified mailbox and how to easly export those informations to Excell where we can sort and filter them.

To find informations about sent or received message we have to check all Message Tracking Logs on all Hub Transport Servers in our Exchange environment.

When we want to get informations about messages delivered to mailbox we need to use Get-MessageTrackingLog with -EventId Deliver options, then we will receive only informations about messages with status delivered.

This link describes Get-MessageTrackingLog CMDlet and how to use it.

Before you will start looking for message, you need to know what is your message tracking logs configuration for Hub Transports Servers. You have to check where and how long those logs are held.

Below you can find script which will search all Hub Transport Servers and message transport logs for delivered messages to specified mailbox. This mailbox is defined in variable Recipient.

#get recipient from parameters
param(
[parameter(Position=0,Mandatory=$true,ValueFromPipeline=$false,HelpMessage="Recipient")]$Recipient,
)
#output file
$OUTFile = "D:\scripts\Deliver.csv"
$Recipient = [string]$(Get-Recipient $Recipient).PrimarySMTPAddress
if(!$Recipient){
 Write-Host "No such Recipient"
} 

#get hub transport servers
$HTServers = Get-TransportServer  

#search message tracking logs on each HT server
ForEach($HTsrv in $HTServers){
 $msgtrack += Get-MessageTrackingLog -server $HTsrv -Recipients $Recipient -EventId Deliver -ResultSize Unlimited
} 

#save logs to .csv file 
$msgtrack | Sort-Object -Unique -Property Timestamp -Desc | select Sender, Timestamp,@{Name='Recipients';Expression={[string]::join(";", ($_.Recipients))}},MessageSubject | Export-Csv $OUTFile -NoTypeInformation

This powershell code you can copy to file and save as Export-MSGTrack.ps1, then you can run it with parameter (mailbox name or alias):

Export-MSGTrack.ps1 JSmith

Out .csv file we can sort and filter and send to user.

The important thing is how long we held Message Tracking Logs. If you held logs for 2 months then this script will search all logs and it will take long time to finish.

When you want to search logs only for specified date you have to modify below line with Get-MessageTrackingLog :

$msgtrack += Get-MessageTrackingLog -server $HTsrv -Recipients $Recipient -EventId Deliver -ResultSize Unlimited

and add dates between we want to find messages:

$msgtrack += Get-MessageTrackingLog -server $HTsrv -Recipients $Recipient -EventId Deliver -start "2012-10-01 8:00:00 AM" -end "2012-10-31 8:00:00 PM" -ResultSize Unlimited

Date format depends on your regional settings and sometimes it’s difficult to find correct format. You can try to find correct format using below CMDlet :

Get-MailboxRegionalConfiguration JSmith

When we run Get-MailboxRegionalConfiguration we should get output like below, where we can find DateFormat, TimeZone.

This should be more helpful to resolve problems with DateFormat.