Sort Exchange and Office 365 mailboxes by size with PowerShell

• Author
• Recent Posts

Jeff Brown

Jeff is a systems administrator and cloud engineer with over a decade of experience in server and application administration. He has managed a wide range of technologies in his career, including Windows Server, Microsoft 365. and Azure. Jeff has also created video-based training for Cybrary and PluralSight. You can follow Jeff on his blog at jeffbrown.tech, or feel free to contact him on Twitter at @jeffwbrown.

Latest posts by Jeff Brown (see all)

• Manage Azure Policy using Terraform - Tue, Aug 2 2022
• Getting started with Terraform in Azure - Tue, Jul 12 2022
• Azure Bicep: Getting started guide - Fri, Nov 19 2021

One common Exchange administrative task is to find the total size of mailboxes in the environment. This can be useful to find the biggest mailboxes or ones that are (nearly) empty as targets for removal due to inactivity. However, mailbox sizes are only available via PowerShell, and depending on where you run the Get-MailboxStatistics command, the options for sorting the output may require additional work.

First, what exactly is the problem with the default output? When viewing the size of multiple mailboxes through the TotalItemSize property, each mailbox does not have a standard unit. Instead, it converts mailboxes to a byte unit that best represents their sizes, like so:

Get MailboxStatistics default formatting

We have different mailboxes with KB, MB, and GB listed. If you tried to sort the mailboxes by their TotalItemSize, the Exchange PowerShell module could do so, but if you exported this data to Excel, you couldn't sort it.

So what are the options if you need to export the data to another format? The Exchange PowerShell module also includes the ability to convert these values to a common byte unit. The TotalItemSize has built-in methods to convert to bytes, kilobytes (KB), megabytes (MB), gigabytes (GB), and terabytes (TB). You can view these methods by passing the value of TotalItemSize to Get-Member:

Available members, properties, and methods for Get MailboxStatistics

To apply these methods to the TotalItemSize property and display the data along with other mailbox statistics, PowerShell allows creating a calculated property in the output. This lets you create a custom property name and use an expression to display its value. Here are some examples for a single mailbox and multiple mailboxes. Notice that in the Expression property, the ToMB() method serves to calculate each mailbox's size to megabytes:

Get-MailboxStatistics -Identity gglass | Select-Object DisplayName, @{Name="TotalItemSizeMB";Expression={$_.TotalItemSize.Value.ToMB()}}, ItemCount
Get-Mailbox | Get-MailboxStatistics | Select-Object DisplayName, @{Name="TotalItemSizeMB";Expression={$_.TotalItemSize.Value.ToMB()}}, ItemCount

Examples of calculated properties

Now this is great if you run these commands in an Exchange PowerShell module window. But these methods are not available when using a remote PowerShell session connected to the Exchange server (for example, this would apply to Exchange Online in Office 365). The command will not return an error, but the calculated property would simply be blank.

In this case, we would need to manipulate the TotalItemSize value manually to extract the raw bytes in the string and convert them to the desired byte unit. The calculated property formula I have always used comes from this Scripting Guy! blog post from Brian Jackett. Here is an example of the formula he uses in a calculated property:

Get-MailboxStatistics -Identity gglass | Select-Object DisplayName, @{Name="TotalItemSizeMB"; Expression={[math]::Round(($_.TotalItemSize.ToString().Split("(")[1].Split(" ")[0].Replace(",","")/1MB),0)}}, ItemCount

Let's break out his conversion:

[math]::Round(($_.TotalItemSize.ToString().Split("(")[1].Split(" ")[0].Replace(",","")/1MB),0)

First, he converts the TotalItemSize property to a string using a built-in method on the TotalItemSize property. Then he splits it on any opening parentheses and keeps the element at index 1. At this point, this is everything after the first parentheses in TotalItemSize:

Manual string conversion of TotalItemBytes

Next, he splits the string again on any spaces and keeps the element at index 0. This should get just the number portion of the string. After that, he removes any commas:

Getting the number portion of the string

Finally, he converts the byte count to megabytes by using the built-in PowerShell converter /1MB. Additional, he uses the .NET framework method [math]::Round to round the value to an integer with the option to specify the number of decimals to use (in this case, "0" is specific to give a nice round number):

Getting the output in megabytes

The final output then looks like this:

Final output using manual string conversion

While a little complicated and messy, this example does show the power of what's possible using different methods to manipulate strings into meaningful values. Now we can export this to CSV or a similar format and use the calculated property TotalItemSizeMB to sort the values as needed.