How to Export Exchange Message Tracking Logs to Excel

/ April 5, 2011

imageFrom 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

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

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

image

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

image

Script Code

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
<#
    .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

Related posts

20 thoughts on “How to Export Exchange Message Tracking Logs to Excel”

  1. Is it possible to use above script for extracting read delivery report of the users..
    I enabled read tracking in my organization…
    I want to extract report type…

  2. Hi Steve .I have checked this script on Exchange 2013 and unfortunately it does not fetch the data.do we need to make any change in this script.

  3. Hi Steve,

    Great script. Worked fine on Exchange 2010. Do you have anything similar to this for mails sent rather than received?

    Garreth

  5. Hi Steve awesome script…do you know if it is possible to export to csv a list of when last (date and time) Distribution lists received email? I need to do this for all the DL’s in a organization….there are about 720 Dl’s.
    This is on a Exchange 2007 environment.

  6. Dear stevie, thanks for great script .Please advise if i can change something in this script so that it can show me the output as the email provided as sender.thanks

  7. Pingback: Export Exchange Message Tracking Logs to Excel « MidThought's

  8. Hi Steve

    First off great script. It works for me on Exchange 2010 but not on 2007 is this something I`m doing wrong or is not compatible?

    Cheers in advance
    Jon

  9. @Steve Goodman I’m looking for a script which export admin audit log to Excel (with audit on cmdlets like new-mailbox, remove-mailbox,….)

    Thanks !

  10. Hi Steve,

    Great article. Do you have anything similar for Exchange 2003? I need to be able to query all of the headers from the mailboxes on the exchange server/db. Any ideas?

    Thanks in advance,
    Connor

  11. Thanks for you great job !
    Is it possible to make the same script but with admin audit log message ?
    I’m verry newbie in powershell scripting…

    Thanks for all.

  14. Very useful. Do you by any chance have anything similar that provides a report for received and sent e-mail in a certain timeframe (can be as far as Message Tracking Logs exist) for a particular user, that includes the size of the e-mails.

    I always get, “my mailbox is too small” and just believing the user that they receive 200 and send 100 e-mails (that are all large) a day is a bit much.

    I’d like to get some kind of report perhaps for a monthly/daily basis.

  15. Pingback: How to Export Exchange Message Tracking Logs to Excel « Steve Goodman’s Exchange Blog « JC’s Blog-O-Gibberish

Comments are closed.