In this post, I’ll demonstrate how you can use the Get-Mailbox cmdlet with the Get-MessageTrackingLog cmdlet to get the last email received for a mailbox or many mailboxes.
How it works
You can skip this section and scroll down to the instructions section below if you just need to run the report. For those who are interested in how it works, keep reading. As you know, you get the mailbox details using the Get-Mailbox cmdlet:
Get-Mailbox Administrator
Nothing special here so we’ll move on. To get the last received email for each mailbox, we first need to identify all the servers that will be involved with mail flow and collect the message tracking logs. First, create an empty array for the ‘Hub Transport’-like servers:
$HTServers = @()
Then get all the Exchange 2007 and 2010 HT servers and the Exchange 2013 and 2016 mailbox servers and add them to the $HTServers array:
Get-ExchangeServer | ? { `
($_.AdminDisplayVersion.Major -eq “15” -and $_.ServerRole -match “Mailbox”) -or `
($_.AdminDisplayVersion.Major -eq “8” -and $_.ServerRole -match “HubTransport”) -or `
($_.AdminDisplayVersion.Major -eq “14” -and $_.ServerRole -match “HubTransport”) `
} | % {$HTServers += $_.Name}
Once done, we need to get the message tracking logs for each HT server. First create an empty array for the message tracking logs as below:
$MessageTrackingLog = @()
Then cycle through each HT server, get the message tracking logs and add to the $MessageTrackingLog array:
foreach($HTServer in $HTServers)
{
$MessageTrackingLog += Get-MessageTrackingLog -ResultSize Unlimited -Server $HTServer
}
The next step is to create an empty array which will include our output:
$CSV = @()
Next up, we will cycle through each mailbox that’s piped into the function, get the last received email time stamp, create a new object to hold the mailbox name and last received email time stamp, add to our new array and then move to the next mailbox. (Agreed, a bit much for one sentence). First things first: get the data from the pipeline and assign to a new array for the mailboxes:
$mailboxes = @(input)
To cycle through each mailbox in $mailboxes, we run this command:
foreach ($mailbox in $mailboxes)
{
#Do something!
}
In the middle of the foreach curly brackets, we need to get the message tracking log where the recipients match the email address for our mailbox, then sort by the timestamp with the most recent first, select the first one in the list (i.e. the most recent email) then get just the TimeStamp property and simply set that as the value for the $LastEmailReceived variable:
$LastEmailReceived = ($MessageTrackingLog | ? {$_.Recipients -match $mailbox.WindowsEmailAddress} | sort TimeStamp -Descending | select -First 1).TimeStamp
Next, we need to create a new system object which will act as a line in our output array with two columns, DisplayName and LastEmailReceived. We’ll set DisplayName to be the mailbox display name, i.e. $mailbox.DisplayName and we’ll set the LastEmailReceived to be the value we have from the command above, i.e. $LastEmailReceived. It all looks like below:
$CSVLine = New-Object System.Object
$CSVLine | Add-Member -Type NoteProperty -Name DisplayName -Value $mailbox.DisplayName
$CSVLine | Add-Member -Type NoteProperty -Name LastEmailReceived -Value $LastEmailReceived
Our next step is to add this line to our output array, $CSV:
$CSV += $CSVLine
This concludes the $mailboxes foreach section. We only want the next part run once and not for each mailbox. This next part just outputs the data which is held in the $CSV array:
$CSV
Get-LastReceivedEmail function
Now, if we put it all together, we get the full function below. You can copy and paste this into your Exchange Mangement Shell window to import the function.
function Get-LastReceivedEmail
{
$mailboxes = @($input)
$HTServers = @()
Get-ExchangeServer | ? { `
($_.AdminDisplayVersion.Major -eq “15” -and $_.ServerRole -match “Mailbox”) -or `
($_.AdminDisplayVersion.Major -eq “8” -and $_.ServerRole -match “HubTransport”) -or `
($_.AdminDisplayVersion.Major -eq “14” -and $_.ServerRole -match “HubTransport”) `
} | % {$HTServers += $_.Name}
$MessageTrackingLog = @()
foreach($HTServer in $HTServers)
{
$MessageTrackingLog += Get-MessageTrackingLog -ResultSize Unlimited -Server $HTServer
}
$CSV = @()
foreach ($mailbox in $mailboxes)
{
$LastEmailReceived = ($MessageTrackingLog | ? {$_.Recipients -match $mailbox.WindowsEmailAddress} | sort TimeStamp -Descending | select -First 1).TimeStamp
$CSVLine = New-Object System.Object
$CSVLine | Add-Member -Type NoteProperty -Name DisplayName -Value $mailbox.DisplayName
$CSVLine | Add-Member -Type NoteProperty -Name LastEmailReceived -Value $LastEmailReceived
$CSV += $CSVLine
}
$CSV
}
Instructions
1) Copy the function just above and paste into your Exchange Management Shell. This imports the function.
2) To get the last received email for a single mailbox, such as the administrator mailbox, run this command:
Get-Mailbox administrator | Get-LastReceivedEmail
3) To get the last received email for multiple mailboxes (e.g. administrator and finance2), run this command:
“administrator”,”finance2″ | % {Get-Mailbox $_} | Get-LastReceivedEmail
4) To get the last received email for all mailboxes in your organization, run this command:
Get-Mailbox -ResultSize Unlimited | Get-LastReceivedEmail
5) To export results to CSV:
Get-Mailbox -ResultSize Unlimited | Get-LastReceivedEmail | Export-Csv -NoTypeInformation C:Mailboxes.csv
You should now see a CSV like this when viewed in Excel:
If there are any empty values for LastEmailReceived then this means that there was no record of an email sent to that user in the message tracking logs – i.e. either the message tracking logs don’t go back that far or the user has never received an email.
Hopefully this should help with your email troubleshooting and email reporting.