Exchange Powershell Question - Last Sent Emails from Mailbox

Question

_{Wednesday, April 25, 2012 8:38 PM}

Hey Guys,

I am trying to determine how to abstract via powershell "last sent" dates
from mailboxes. Here is my current scipt and I am missing this last piece (last
sent):

[PS] C:\Windows\system32>Get-MailboxDatabase "DB_NAME" |
Get-MailboxStatistics | Sort totalitemsize
-desc | ft displayname,
totalitemsize, itemcount, LastLogontime, LastSent

What am I missing to get the last sent information? I can created the column
but cannot get the data.

I am a newbie with Power Shell. Thanks guys

All replies (10)

_{Friday, April 27, 2012 9:34 PM ✅Answered | 1 vote}

This example produces the following output:

If you want to add more fields, follow my instructions here (be sure to read the comments as well).

WARNING: Searching message tracking logs across the organization is very slow.  I specified "resultsize 5" below so you can see how the script works.  Once you're happy, change "5" to "unlimited".

cls

$UserList = Get-mailbox -Resultsize 5

$MasterList = @()

foreach ($User in $UserList) {

$MyObject = New-Object PSObject -Property @{
EmailAddress = $null
MailboxSize = $null
LastSent = $null
}

$ErrorActionPreference = 'SilentlyContinue'

$MyObject.LastSent = ((Get-TransportServer | Get-MessageTrackingLog -Sender $user.PrimarySmtpAddress | sort timestamp)[-1]).timestamp
$MyObject.EmailAddress = ($User).PrimarySmtpAddress
$MyObject.MailboxSize = (Get-MailboxStatistics $User).TotalItemSize

$ErrorActionPreference = 'Continue'

$MasterList += $MyObject
}

$MasterList

Mike Crowley | MVP
My Blog -- Planet Technologies

_{Wednesday, April 25, 2012 10:39 PM}

LastSent isn't a valid value from get-mailboxstatistics as far as I am aware. Where have you seen that value populated?

Simon.

Simon Butler, Exchange MVP
Blog | Exchange Resources | In the UK? Hire Me.

_{Thursday, April 26, 2012 12:08 AM}

Simon,

I added the value as a variable to the string but it is only like saying add a column and this is name for it. Is there any way to get the last sent information about a users mailbox from via Powershell?

_{Thursday, April 26, 2012 12:36 AM}

You can only view information that Exchange records. You can see the full list by doing

get-mailboxstatistics -identity username | fl

Exchange doesn't record the last time an email was sent from an account. Last logon time isn't reliable either, so if you are trying to establish when the account was last used, it isn't going to be very easy. The last logon time can vary depending on which domain controller was used and can be modified by other accounts accessing the mailbox. You would probably have to query AD directory for the last login information, with the query running against all domain controllers.

Simon.

Simon Butler, Exchange MVP
Blog | Exchange Resources | In the UK? Hire Me.

_{Friday, April 27, 2012 7:34 AM}

Hi

Agree with Sembee, we can not find via the command get-mailboxstatistics .

For last sent information for single mailbox, you can try Message Tracking Log. Please refer to the below link

http://technet.microsoft.com/en-us/library/aa997573.aspx 

It has the timestamp that might be help

eg. Get-Messagetrackinglog -Sender [email protected] | Sort Timestamp -desc | Ft Sender, Timestamp

Cheers

Zi Feng

Zi Feng

TechNet Community Support

_{Monday, April 30, 2012 2:08 PM}

Any update here?

Mike Crowley | MVP
My Blog -- Planet Technologies

_{Thursday, July 12, 2012 7:00 AM}

Mike,

I use Office365 and Get-TransportServer and Get-MessageTrackingLog do not appear to be useable OR are not configured in my Enterprise subscription.

But...for those that may dig this up later, Mike's work is dead on, AND...

If you are using Office365 you may want to try the following:

Change Mike's

$MyObject.LastSent = ((Get-TransportServer | Get-MessageTrackingLog -Sender $user.PrimarySmtpAddress | sort timestamp)[-1]).timestamp

to

$MyObject.LastSent = ((Search-MessageTrackingReport -Identity $User.PrimarySmtpAddress -BypassDelegateChecking | Sort SubmittedDateTime)[-1]).SubmittedDateTime

The only drawback may be the speed at which this runs AND the message tracking report does not go back to the begining of time so some dormant mailboxes may not have a value returned. If you keep track of this data (beyond what I have the time to put here) you can then find truly dormant mailboxes.

I am still on the hunt for a good method to see LastSent as well as LastReceived, but this is getting closer!
**

Daniel S. Gurrola II**
No Acronyms, Affiliations, Certifications or other Nausea - it's just...me.

_{Monday, August 26, 2013 11:04 PM}

How would you add the ability to export this to an Excel CSV with headings?

_{Saturday, September 14, 2013 12:53 AM}

You can use a variable to hold the string value for the path and file name...

$CsvFile = C:\Reports\Report.csv$MyObject.LastSent = ((Search-MessageTrackingReport -Identity $User.PrimarySmtpAddress -BypassDelegateChecking | Sort SubmittedDateTime)[-1]).SubmittedDateTime | Export-Csv $CsvFile

Or you can just replace the variable after the Export-Csv with the path and file name directly.

_{Monday, July 16, 2018 2:16 PM}

Cause Get-MessageTrackingLog returns only last 30 days information, better to use Get-MailboxFolderStatistics command with -IncludeOldestAndNewestItems parameter. Something like this:

$MyObject.LastSent = (Get-MailboxFolderStatistics -Identity $user -IncludeOldestAndNewestItems  -FolderScope SentItems).NewestItemLastModifiedDate