How to Export Exchange Message Tracking Logs to Excel

From time to time you may have a user request to find out what messages they should have received - perhaps they have marked them as Junk Email in Outlook, or deleted them accidentally. Whatever the reason, you might find it useful to be able to quickly pull the information for the user out of the message tracking logs and into a file quickly so they can have a look for themselves.

What this script does is searches for messages delivered to a particular user across all Hub Transport servers, sorts the information by the date and time and saves it to a convenient CSV file ready to open in Microsoft Excel.

How to use the script

Export-MessageTrackingLogsForRecipient.ps1 -Recipient steve -OutputCSV .\output.csv

Whilst the script is in use, you'll see progress of each log search:


After completion you should find your resulting CSV file similar to this:


Script Code

<#
    .SYNOPSIS
    Generates a CSV file containing sender, data, recipients and subjects of messages received by a particular recipient.
 
    Steve Goodman
    .DESCRIPTION
    Searches all Hub Transport servers using the Get-MessageTrackingLog command to find all available logs for received messages to a particular recipient
 
    .PARAMETER Recipient
    Username, Mailbox or Email Address of the Recipient
 
    .PARAMETER OutputCSVFile
    File to write CSV output to
 
    .EXAMPLE
    Exports message logs for one recipient
    Export-MessageTrackingLogsForRecipient.ps1 -Recipient steve -OutputCSV .\output.csv
   
    Example CSV file:
    "Sender","Timestamp","Recipients","MessageSubject"
    "phil@rootuk.net","05/04/2011 15:50:12","steve@goodman.net","Hello, how are you!"
 
    #>
param(
    [parameter(Position=0,Mandatory=$true,ValueFromPipeline=$false,HelpMessage="Recipient")]$Recipient,
    [parameter(Position=1,Mandatory=$true,ValueFromPipeline=$false,HelpMessage="Output CSV File Name")][string]$OutputCSVFile
    )

# Check EMS
if (!(Get-Command Get-ExchangeServer -ErrorAction SilentlyContinue))
{
    throw "Please launch the Exchange Management Shell"
}

# Ensure input is correct format
$Recipient = Get-Recipient $Recipient
if (!$Recipient)
{
    throw "Recipient not found"
}
$Recipient = $Recipient.PrimarySMTPAddress

# Check file doesn't exist
if (Test-Path $OutputCSVFile)
{
    throw "File may already exist"
}

# Get Hub Transport Servers
$HubTransports = Get-ExchangeServer | Where {$_.ServerRole -like "*Hub*"}

# Get all logs with "Deliver" event
foreach ($HubTransport in $HubTransports)
{
    $logs += Get-MessageTrackingLog -Server $HubTransport -Recipients $Recipient -EventId DELIVER -ResultSize Unlimited  
}

# Output logs to file after sorting
$logs | Sort-Object -Unique -Property Timestamp -Descending | select Sender,Timestamp,@{Name='Recipients';Expression={[string]::join(";", ($_.Recipients))}},MessageSubject | Export-Csv $OutputCSVFile -NoTypeInformation

Script Download
Download Export-MessageTrackingLogsForRecipient.zip