Retrieve mailbox statistics in PowerShell for a large number of users

I’m currently working on a project where we are going to move 24,000 mailboxes from Exchange 2016 to Exchange Online. For planning purposes we would like to know the basic statistics, like the LastLogonTime and the number of items (both regular and deleted).

To retrieve this for my mailbox, you can use something like this:

[PS] C:\ >Get-Mailbox -Identity wesseliusj | Get-MailboxStatistics | Select DisplayName,LastLogonTime,ItemCount,TotalItemSize,DeletedItemsCount,TotalDeletedItemSize

DisplayName          : Wesselius, J
LastLogonTime        : 28-4-2022 11:24:46
ItemCount            : 1065
TotalItemSize        : 63.95 MB (67,060,296 bytes)
DeletedItemsCount    :
TotalDeletedItemSize : 5.421 MB (5,684,477 bytes)

The TotalItemSize and TotalDeletedItemSize are returned as text and not numeric. Since I want to export everything into a CSV file and import into Excel for further processing, it must be converted to a numeric value. This is called a calculated property and this is possible using the following option:

@{Name="TotalItemSizeMB";Expression={$_.TotalItemSize.Value.ToBytes()}}

ToBytes() can also be ToKB(), ToMB() or ToGB() depending on your situation.

The previous command and output will now be something like this:

[PS] C:\>Get-Mailbox -Identity wesseliusj | Get-MailboxStatistics | Select DisplayName,LastLogonTime,ItemCount,@{Name="TotalItemSizeMB";Expression={$_.TotalItemSize.Value.ToBytes()}},DeletedItemsCount,@{Name="TotalDeletedItemSizeMB";Expression={$_.TotalDeletedItemSize.Value.ToBytes()}}


DisplayName            : Wesselius, J
LastLogonTime          : 28-4-2022 11:24:46
ItemCount              : 1065
TotalItemSizeMB        : 67060293
DeletedItemsCount      :
TotalDeletedItemSizeMB : 5684477

Now use the Export-Csv command and we are good to go (you would hope 😊):

[PS] C:\>get-mailbox -ResultSize unlimited | Get-MailboxStatistics | Select DisplayName,LastLogonTime,ItemCount,@{Name="TotalItemSizeMB";Expression={$_.TotalItemSize.Value.ToBytes()}},DeletedItemsCount,@{Name="TotalDeletedItemSizeMB";Expression={$_.TotalDeletedItemSize.Value.ToBytes()}} | export-csv -Path statistics.csv -nti

Sending data to a remote command failed with the following error message: [ClientAccessServer=EXCH2016,BackEndServer=exch2016.labs.local,RequestId=c0430cd6-6f4d-48a5-8434-d59ebcd91887,TimeStamp=28-4-2022 10:03:54] [FailureCategory=W
SMan-Others] The total data received from the remote client exceeded the allowed maximum. The allowed maximum is 524288000. For more information, see the about_Remote_Troubleshooting Help topic.
    + CategoryInfo          : OperationStopped: (exch2016.labs.local:String) [], PSRemotingTransportException
    + FullyQualifiedErrorId : JobFailure
    + PSComputerName        : exch2016.labs.local

[PS] C:\>

Unfortunately, the Get-Mailbox command retrieves all 24,000 mailboxes in one run and then tries to use this as input for the Get-MailboxStatistics command. While this works for just a few mailboxes, it runs out of memory for a large set of mailboxes.

The solutions here (or one of the solutions) is to import all mailboxes into a variable, and loop through all mailboxes in this variable combined with the Get-MailboxStatistics command:

[PS] C:\> $Mailboxes = Get-Mailbox -Resultsize Unlimited
[PS] C:\> ForEach ($Mailbox in $Mailboxes) {Get-MailboxStatistics $Mailbox | Select  DisplayName,LastLogonTime,ItemCount,@{Name="TotalItemSizeMB";Expression={$_.TotalItemSize.Value.ToBytes()}},DeletedItemsCount,@{Name="TotalDeletedItemSizeMB";Expression={$_.TotalDeletedItemSize.Value.ToBytes()}} | export-csv -Path statistics.csv -nti}

One last question I got is to add a UPN or alias of the user to the CSV file. UPN and alias are not in the object that’s returned by Get-MailboxStatistics, but are returned by the Get-Mailbox command. To get these properties in the output, again a calculated property must be used, similar to the size properties that were used earlier.

To retrieve the UPN, use something similar to the following:

@{Name = "UPN"; Expression={$Mailbox.UserPrincipalName}}

This will take the UserPrincipalName property of the first command (Get-Mailbox) and parse it into the output. The entire command will be:

[PS] C:\>ForEach ($Mailbox in $Mailboxes) {Get-MailboxStatistics $Mailbox | Select  DisplayName, @{Name = "UPN"; Expression={$Mailbox.UserPrincipalName}},LastLogonTime,ItemCount,@{Name="TotalItemSizeMB";Expression={$_.TotalItemSize.Value.To
Bytes()}},DeletedItemsCount,@{Name="TotalDeletedItemSizeMB";Expression={$_.TotalDeletedItemSize.Value.ToBytes()}}}

DisplayName            : Wesselius, J
UPN                    : j.wesselius@Exchangelabs.nl
LastLogonTime          : 29-4-2022 14:22:03
ItemCount              : 1098
TotalItemSizeMB        : 68714749
DeletedItemsCount      :
TotalDeletedItemSizeMB : 5822787

[PS] C:\>

This will create a proper CSV file with all the information I need, ready to import into Excel 😊

More information regarding working with large number of users (in Office 365) please check the following article: Running PowerShell cmdlets for large numbers of users in Office 365 – Microsoft Tech Community

H1 2022 Cumulative Update Exchange 2019 (CU12)

Last week, on April 20, Microsoft released Cumulative Update 12 for Exchange 2019 and Cumulative Update 23 for Exchange 2016.

It took Microsoft six month for the Cumulative Update, that’s because Microsoft is changing from a quarterly release cycle to a bi-annual release cycle. As a result, this will the Cumulative Update of the first half of 2022, so H1 2022 Cumulative Update.

This is only true for Exchange 2019 as there will be no new Cumulative Updates for Exchange 2016 anymore. Exchange 2016 has entered extended support, so Microsoft will deliver Security Updates when needed, but not more CUs. Mainstream support for Exchange 2019 will end on January 9, 2024 and extended support for Exchange 2019 will end on October 14, 2025. This is the same date as for Exchange 2016. What will happen then is still unknown….

Exchange 2019 CU12 now has support for Windows 2022 (finally). Support for Windows 2022 is only true for Exchange 2019 CU12, Exchange 2016 is only supported on Windows 2016.

When it comes to Active Directory, only Exchange 2019 CU12 supports Windows 2022 Domain Controllers. Exchange 2016 and Exchange 2013 do not support Windows 2022 Domain Controllers (despite earlier communication from Microsoft). For more information regarding supportability check the Exchange Supportability Matrix.

One of the new features of Windows 2022 is support for TLS 1.3 and several of my clients have a requirement for this. Unfortunately, Exchange 2019 still does not support TLS 1.3 and support for TLS 1.3 is expected by the end of this year.

With the new Cumulative Update comes a change in (free) licensing. Previously, there was a free license for the ‘hybrid server’ but this was Exchange 2016. Now with Exchange 2016 in extended support and no more CUs for Exchange 2016, the free hybrid license is available for Exchange 2019!

A lot of noise regarding the “remove the last Exchange server” in your organization. When you have all mailboxes in Exchange Online and you have Azure AD Connect running, you need to have one Exchange server, just for management purposes. This is no longer required with Exchange 2019 CU12. You can install the management tools for CU12 and get rid of the Exchange server. Be aware that you must NOT UNINSTALL the Exchange server, but clean up the hybrid configuration using PowerShell, shutdown the Exchange server and run the CleanupActiveDirectoryEMT.ps1 script (released with Exchange 2019 CU12).

Are there reasons to not do this? Yes, think about SMTP relay from on-premises to Exchange Online, or RBAC on-premises (not available with only the Management Tools on-premises), or maybe an offloading possibility when needed. Oh, and if you are not good with PowerShell, be aware that this is a PowerShell only solution. If you are addicted to the Exchange Admin Center, leave the Exchange server running 😉

But nevertheless, it’s good that Microsoft finally heard this feedback and offers a solution for the last Exchange server when all mailboxes are in Exchange Online.

A couple of remarks:

  • Be aware that Microsoft only supports the two latest CUs, i.e. Exchange 2019 CU11/CU12 and Exchange 2016 CU22/CU23.
  • There are no schema changes in these Cumulative Updates, but there are changes in the configuration, so you need to run setup with the /PrepareAD switch.
  • When running a Database Availability Group, do not forget to put the Exchange servers in maintenance mode.
  • As always, test the new CUs in your test environment before installing in your production environment.

More information and downloads:

Exchange versionKB ArticleDownload
Exchange 2019 CU12KB5011156https://www.microsoft.com/en-us/download/details.aspx?id=104131
Exchange 2016 CU23KB5011155https://www.microsoft.com/en-us/download/details.aspx?id=104132
Exchange 2016 CU23 language packshttps://www.microsoft.com/en-us/download/details.aspx?id=104130