Tag Archives: calculate property

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:


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

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