Register for Semperis' Hybrid Identity Protection (HIP) Conference - June 30 - July 1 Register for Semperis' Hybrid Identity Protection (HIP) Conference - June 30 - July 1
Exchange Online|Exchange Server|Office|Office 365

Exchange Online Exposes New Mailbox Activity Data

Reporting Busy Exchange Online Mailboxes

It’s common for Exchange administrators to generate reports about user activity. In the past, people depended on the LastLogonTime property returned by the Get-MailboxStatistics cmdlet to know the last time a mailbox’s owner logged on. However, as I pointed out last year, the values returned by the cmdlet are usually misleading because of the number of background assistants that access mailboxes for different purposes.

New Assistant, New Mailbox Data

Although it doesn’t make LastLogonTime any more accurate, A new background assistant is designed to give more information about user behavior within mailboxes. The assistant examines signals generated by user activity to figure out the last time a mailbox owner performed actions in different categories. The information is not updated in real time and can be up to three days old. Even so, the new information gives extra insight into mailbox activity. Table 1 lists the last user activity properties. One interesting omission is that delete actions are not included in the calculation of last activity in any category.

Property Meaning
LastLogonTime Last time a process signed onto the mailbox. [Warning: background assistants often influence this data]
LastEmailTime Last time an email was sent from the mailbox or marked as read in the mailbox.
LastInteractionTime Last time the mailbox owner performed an action in the mailbox.
LastContactsTime Last time a contact was added, updated, or viewed.
LastCalendarTime Last time a calendar entry was added, updated, or a calendar invite was accepted/declined, or the user viewed the calendar.
LastTasksTime Last time a task was added, updated, or removed.
LastFileTime Last time the user accessed, modified, downloaded, renamed, or deleted a file in a SharePoint Online or OneDrive for Business document library.
LastProfileTime Last time the user’s profile was updated (for example, adding a photo).
LastUserActionWorkloadAggregateTime Last time for a recorded action across all workloads.
LastUserActionUpdateTime Last time the assistant processed the mailbox to calculate times from signals stored in the mailbox. The data might lag real-time information by up to 3 days.
LastModernGroupsTime Only generated for Office 365 Group mailboxes.

Table 1: Properties recording user activities in Exchange Online mailboxes

Accessing Activity Data with PowerShell

In May 2019, you could access the properties using PowerShell in two ways. First, you can run the Get-MailboxStatistics cmdlet, which has been updated to support the new information. For example, here’s how to retrieve last activity information for a mailbox:

# Fetch mailbox activity data from mailbox statistics
Get-MailboxStatistics -Identity TRedmond | Select Last*

LastEmailTime                       : 14 May 2019 23:50:17
LastContactsTime                    : 31 Mar 2019 18:55:43
LastCalendarTime                    : 14 May 2019 09:41:38
LastTasksTime                       : 7 May 2019 10:11:57
LastFileTime                        : 14 May 2019 22:27:40
LastProfileTime                     : 16 Feb 2019 19:20:25
LastModernGroupsTime                :
LastInteractionTime                 : 14 May 2019 23:50:17
LastLoggedOnUserAccount             :
LastLogoffTime                      :
LastLogonTime                       : 16 May 2019 23:55:52
LastUserAccessTime                  :
LastUserActionTime                  : 14 May 2019 23:50:17
LastUserActionUpdateTime            : 15 May 2019 06:56:25
LastUserActionWorkloadAggregateTime : 14 May 2019 23:50:17

Alternatively, you can access the properties from the diagnostic data generated by the Export-MailboxDiagnosticLogs cmdlet. Here’s how:
# Grab mailbox activity data from diagnostic info
$Log = Export-MailboxDiagnosticLogs -Identity TRedmond -ExtendedProperties
$xml = [xml]($Log.MailboxLog)
$xml.Properties.MailboxTable.Property | ? {$_.Name -like "Last*"}

Name                                Value
----                                -----
LastLogonTime                       16/05/2019 22:55:52
LastUserActionTime                  14/05/2019 22:50:17
LastUserActionUpdateTime            15/05/2019 05:56:25
LastContactsTimeCurrentValue        31/03/2019 17:55:43
LastEmailTimeCurrentValue           14/05/2019 22:50:17
LastFileTimeCurrentValue            14/05/2019 21:27:40
LastCalendarTimeCurrentValue        14/05/2019 08:41:38
LastTasksTimeCurrentValue           07/05/2019 09:11:57
LastProfileTimeCurrentValue         16/02/2019 19:20:25
LastUserActionWorkloadAggregateTime 14/05/2019 22:50:17

You can see that different property names are used and that the dates are reported in UTC instead of local time. LastinteractionTime is missing from the list of properties, but the others are available.

Summer Woes

And then the summer happened and the Exchange developers were sunburned. Or something else happened and they removed the activity reporting properties from Get-MailboxStatistics in July. And despite many protests, Microsoft has persisted with their view that Get-MailboxStatistics shouldn’t display these properties. Apart that is for LastInteractionTime and LastUserActionTime, both of which

Rather bizarrely, the properties are still available through the Export-MailboxDiagnosticLogs cmdlet. I guess I should be happy that the data is still available, but I have no idea why it was removed from Get-MailboxStatistics.

Generating a Mailbox Activity Report

Now that we understand how to access the last activity properties, we can generate reports. Here’s a simple script to grab the data for all user mailboxes, including some information about the size of each mailbox together with the number of items. I’ve also included LastLogon because it’s available in the full understanding that the data is likely incorrect for some mailboxes. After generating the report data, it’s exported to a CSV file.

# Generate Mailbox Activity Report
$Mbx = (Get-Recipient -RecipientTypeDetails UserMailbox -ResultSize Unlimited | Select DisplayName, DistinguishedName) 
  $Report = @() 
  ForEach ($M in $Mbx) { 
   Write-Host "Processing" $M.DisplayName 
   $Log = Export-MailboxDiagnosticLogs -Identity $M.DistinguishedName -ExtendedProperties 
   $xml = [xml]($Log.MailboxLog) 
   $LastEmail = ($xml.Properties.MailboxTable.Property | ? {$_.Name -like "LastEmailTimeCurrentValue"}).Value
   $LastCalendar = ($xml.Properties.MailboxTable.Property | ? {$_.Name -like "LastCalendarTimeCurrentValue"}).Value
   $LastContacts = ($xml.Properties.MailboxTable.Property | ? {$_.Name -like "LastContactsTimeCurrentValue"}).Value
   $LastFile = ($xml.Properties.MailboxTable.Property | ? {$_.Name -like "LastFileTimeCurrentValue"}).Value
   $Stats = (Get-MailboxStatistics -Identity $M.DistinguishedName) 
   $MbxSize = ($Stats.TotalItemSize.Value.ToString()).Split("(")[0] 
   $ReportLine = [PSCustomObject][Ordered]@{ 
        Mailbox = $M.DisplayName 
        Items = $Stats.ItemCount 
        Size = $MbxSize 
        LastLogon = Get-Date($Stats.LastLogonTime) -Format g
        LastActive = Get-Date($Stats.LastInteractionTime) -Format g
        LastEmail  = Get-Date($LastEmail) -Format g
        LastCalendar = Get-Date($LastCalendar) -Format g
        LastContacts = Get-Date($LastContacts) -Format g
        LastFile = Get-Date($LastFile) -Format g} 
   $Report += $ReportLine } 
$Report | Export-csv -NoTypeInformation Users.csv

Once it’s generated, the CSV file can be opened in Excel (Figure 1) or imported into another tool like Power BI for analysis.

Figure 1: A User Activity Report Generated using the new properties (image credit: Tony Redmond)

You can grab a copy of a script to retrieve this data from GitHub.

Insights are Valuable

In the “old days” mailbox activity reports were a valuable way of assessing if an account was used. This became more important when Office 365 arrived because underused accounts consumed monthly license fees. It’s still important to keep an eye on licenses, but the usage of an account is now split across many more workloads than just email. The fact that someone is not sending much email might be easily explained if they use Teams for internal communications and only ever send email when they need to contact an external person.

In any case, insights are valuable and it’s good to have this data available (even if some twists are needed to get it). The only problem now is to figure out what to do with it.

BECOME A PETRI MEMBER:

Don't have a login but want to join the conversation? Sign up for a Petri Account

Register
Comments (4)

4 responses to “Exchange Online Exposes New Mailbox Activity Data”

  1. misquote

    Super handy; thank you for sharing! Line 6 of the last script is missing "$M." right before DistinguishedName.

  2. [email protected]

    Perfect! I would request if we could also get Delgation date, in case where we have delegated user on a mailbox and we need to know who is and when it was.

  3. TheBigBear

    Hi Tony, thanks this was/is very helpful. But have you had a chance or occasion to update your findings using the new V2 Exchange cmdlets? ( they have finally been released 1 July '20) The new V2 Get-EXOMailboxstatistics cmdlet does NOT give access to some of those more helpful properties like 'LastUserActionTime' etc. As we are all being shoe-horned into using MS modern authentication and oauth2 and the old methods won't work ll that much longer ...

Leave a Reply

Tony Redmond has written thousands of articles about Microsoft technology since 1996. He covers Office 365 and associated technologies for Petri.com and is also the lead author for the Office 365 for IT Pros eBook, updated monthly to keep pace with change in the cloud.

Register for the Hybrid Identity Protection (HIP) Europe Conference!

Hybrid Identity Protection (HIP) Europe 2021 - Virtual Conference

Mobile workforces, cloud applications, and digitalization are changing every aspect of the modern enterprise. And with radical transformation come new business risks. Hybrid Identity Protection (HIP) is the premier educational forum for identity-centric practitioners. At the inaugural HIP Europe, join your local IAM experts and Microsoft MVPs to learn all the latest from the Hybrid Identity world.