Why the Last Login Date Reported by the Get-MailboxStatistics Cmdlet is so Wrong

GetMBXSTSSplash

Exchange and PowerShell: Better Together

The Exchange developers took a brave decision when choosing to use PowerShell as the basis for the Exchange 2007 administration tools. When that decision happened in 2005, PowerShell was still the “Monad” project, based on the manifesto for next-generation administration published by Jeffrey Snover in August 2002. Exchange 2007 was the first major Microsoft server product to embrace PowerShell, and it set the standard for comprehensive coverage of administrative features still held by Exchange today.

The Latest Statistic: Big Funnel

Get-MailboxStatistics was one of the earliest cmdlets to appear. Over the last 12 years, the statistics reported by the cmdlet have expanded enormously, the latest being information about “big funnel,” the new method of holding content indexes for Exchange mailboxes inside the mailboxes. The intention is that any search performed against a mailbox gets the latest information because up-to-date content indexes are always available.

To see the details about the index held in the mailbox (but not the index itself), use a command like this:

Get-MailboxStatistics -id tredmond  | Format-List *Funnel*

BigFunnelIsEnabled                             : True
BigFunnelUpgradeInProgress                     : False
BigFunnelMaintainRefiners                      : True
BigFunnelFilterTableTotalSize                  : 864 KB (884,736 bytes)
BigFunnelFilterTableAvailableSize              : 0 B (0 bytes)
BigFunnelPostingListTableTotalSize             : 984.7 MB (1,032,552,448 bytes)
BigFunnelPostingListTableAvailableSize         : 2.625 MB (2,752,512 bytes)
BigFunnelLargePOITableTotalSize                : 4.719 MB (4,947,968 bytes)
BigFunnelLargePOITableAvailableSize            : 576 KB (589,824 bytes)
BigFunnelTotalPOISize                          : 478.7 MB (501,951,588 bytes)
BigFunnelMessageCount                          : 321162
BigFunnelIndexedSize                           : 6.065 GB (6,512,255,010 bytes)
BigFunnelPartiallyIndexedSize                  : 130 MB (136,304,982 bytes)
BigFunnelNotIndexedSize                        : 51.85 KB (53,096 bytes)
BigFunnelCorruptedSize                         : 0 B (0 bytes)
BigFunnelStaleSize                             : 61.78 KB (63,263 bytes)
BigFunnelShouldNotBeIndexedSize                : 465.3 MB (487,862,997 bytes)

Of course, stuffing content indexes inside mailboxes is something that you can contemplate today when storage costs are much lower than they were for Exchange 2007 deployments. Microsoft is rolling Big Funnel out within Exchange Online now. The feature is not in Exchange 2016 but might be in Exchange 2019.

Using Get-MailboxStatistics

Over the years, many scripters have used Get-MailboxStatistics to report different aspects of mailboxes like the mailbox size and the number of items in the mailbox. Reporting the last logon date is also popular because it helps administrators know when mailboxes are not used. Something like this reveals popular statistics:

Get-MailboxStatistics -id tredmond | Select DisplayName, LastL*, TotalItemSize, ItemCount

DisplayName             : Tony Redmond
LastLoggedOnUserAccount :
LastLogoffTime          : 23/03/2018 14:56:44
LastLogonTime           : 23/03/2018 14:56:42
TotalItemSize           : 4.034 GB (4,332,000,964 bytes)
ItemCount               : 36892

The Problems with the Last Logon Time

But here’s the rub. Get-MailboxStatistics reports some misleading and inaccurate data. First, Microsoft deprecated the LastLoggedOnUserAccount property in Exchange 2013. The cmdlet still reports the property because if Microsoft removed it, they might break a bunch of scripts used by organizations.

Second (and more important), the LastLogonTime property is not what you think. Most people assume that this value is the timestamp when the mailbox owner or a delegate last logged onto the mailbox. That’s precisely what the property represented in Exchange 2007. It doesn’t mean the same thing now.

Mailbox Assistants are the Root Cause

If you look at the documentation for Get-MailboxStatistics, you see that the cmdlet returns information about a mailbox like “the last time it was accessed.” That doesn’t mean the last time the owner or a delegate accessed the mailbox. It means any access to the mailbox. And because modern versions of Exchange are stuffed full of mailbox assistants (more so for Exchange Online), all doing important work to keep mailboxes healthy, the last logon time often means the last time an assistant accessed the mailbox.

Mailbox assistants, like other computer processes, are persistent and keep on processing mailboxes even if their owners ignore them. So, if you run a quick check against mailboxes, you’ll find that everyone in the tenant apparently logs on around the same time:

Get-Mailbox -RecipientTypeDetails UserMailbox | Get-MailboxStatistics | Format-Table DisplayName, LastLogonTime -AutoSize

DisplayName                            LastLogonTime
-----------                            -------------
Deirdre Smith                          23/03/2018 15:07:36
Tony Redmond                           23/03/2018 14:56:42
TempAdmin                              22/03/2018 19:58:08
Jeff Jones                             23/03/2018 07:58:12
Kevin A. Laahs                         23/03/2018 13:23:33
Administrator                          23/03/2018 03:48:33
Michael Van Hyde                       22/03/2018 22:21:20
Paul Cullinan                          23/03/2018 14:37:12
Kim Akers                              23/03/2018 04:04:38
Ben Owens (Business Director)          22/03/2018 20:09:17
Imran Khan                             23/03/2018 06:58:37

In short, we have rubbish data – unless you really want to report the last time an assistant processed a mailbox. Based on some analysis done by Quadrotech, who discovered and fixed the problem in their Office 365 reporting solution, reports based on LastLogonTime overestimate the number of active users in a tenant by up to 30%.

Problems for Scripts

The consequence is that any script purporting to report last user access based on the LastLogonTime property is inaccurate and misleading. A quick search of the internet reveals many examples, including this script from the TechNet Gallery, which bases its report on this code:

$smtp = $mailbox.primarysmtpaddress 
        $statistics = get-mailboxstatistics -identity "$smtp" 
        $lastlogon = $statistics.lastlogontime 
        if($lastlogon -eq $null) { 
            $lastlogon = "Never Logged In" 
        }

There are many other examples to be found, all of which are wrong.

No Way Back

I’m sure that many people don’t realize that LastLogonTime is so misleading, but the sad fact is that this property started to lose its usefulness after the Mailbox Folder Assistant appeared in Exchange 2010. The situation is not going to get any better either because, as far as I understand, Microsoft has no plans to update the cmdlet to make it more accurate.

Commercial Reporting

Fixing your own scripts is one thing. Fixing a commercial reporting product that bases its assessment of last login time for mailboxes on a flawed property is another. From what I can see, many reporting products assumed that Get-MailboxStatistics was believable and still faithfully output the erroneous data today.

Given that no fix is coming for the cmdlet, the solution is to upgrade code to use the data available through the Microsoft Graph, which is what the last activity date in the Office 365 usage reports use (Figure 1), even if the data available in the Office 365 Admin Center is usually three days old.

Office 365 Last User Activity
Figure 1: Last user activity date reported in the Office 365 Admin Center (image credit: Tony Redmond)

Never Assume

Old code and new systems is often an “interesting” mixture. Sometimes everything works and sometimes you need to rework code before it can cope with a new version of a product. In this case, Get-MailboxStatistics lulled everyone into a false sense of security by working flawlessly since its introduction. You asked for a last login date and the cmdlet returned one. How were you to know that the data was so bad?

Follow Tony on Twitter @12Knocksinna.

Want to know more about how to manage Office 365? Find what you need to know in “Office 365 for IT Pros”, the most comprehensive eBook covering all aspects of Office 365. Available in PDF and EPUB formats (suitable for iBooks) or for Amazon Kindle.