Exchange Message Tracking Logs export to Excell

This post is also available in: Polish

 

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.

 

Print Friendly
Tagged , , , , , . Bookmark the permalink.

2 Responses to Exchange Message Tracking Logs export to Excell

  1. Brian Rosenkrantz says:

    Hi,

    I really like your script, but when you say that the script finds logs for delivered messages to specified mailbox, you probably mean Email instead of Mailbox.
    I can’t use the script, cause I need the logs for all messages delivered på all SMTP adresses in the mailbox. (haven’t found a solution yet)

    But great script anyway!

    • Remigiusz Szatkowski says:

      Hi
      Thank you for kind words.
      What exactly has to do a script that you need ?

Leave a Reply to Brian Rosenkrantz Cancel reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>