Exchange Online Exposes New Mailbox Activity Data

chrome 2017 10 05 09 06 39

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.

User Mailbox Report
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.