PowerShell: Get Exchange Mailboxes Over XXGB

Simple command turned crazy. I ended up coming up with this due to the fact we have duplicate display names and needed to update for Exchange Online to get mailbox sizes.

Requirements

• An Exchange Online PowerShell session, for example from the ExchangeOnlineManagement module with Connect-ExchangeOnline.
• Permission to run Get-Mailbox and Get-MailboxStatistics against every mailbox you want to check.

Parameters

The command has no parameters. The one value you change is the size threshold.

Walkthrough

Here is a breakdown of the code, one stage of the pipeline at a time.

Get-Mailbox

Get-Mailbox gets all the mailboxes available, there’s no search filter on this one.

Get-Mailbox -ResultSize Unlimited

Select-Object for Identity

The Select-Object statement is used for doing a select expression where we can transform the Identity parameter that is being used as pipeline input for the Get-MailboxStatistics. I did this mainly because we have duplicate display names and differing email addresses in this specific tenant.

| Select-Object @{ Name = 'Identity'; Expression = { $_.PrimarySmtpAddress } }

Get-MailboxStatistics

Get-MailboxStatistics accept pipeline input of the default variable Identity. By doing the select statement above, we’re now using -Identity via the pipeline using the primary SMTP email address. This command outputs the data about a mailbox.

| Get-MailboxStatistics

Select-Object for TotalItemSize

This next select statement gives us the TotalItemSize formatted for use with comparison.

| Select-Object DisplayName, @{ Name = "TotalItemSize"; Expression = { [math]::Round($($_.TotalItemSize.Value.ToString().Replace(",", "").Split("(")[1].Split(" bytes")[0]) / 1GB, 2) } }

This part of the select statement is broken down as follows:

The first part is the beginning of a select expression, the { after Expression = is the start of the expression that will now become TotalItemSize.

@{ Name = "TotalItemSize"; Expression = {} }

Next up, we have the [math] function, we use this because by doing the simple math, we’d have a large amount of decimal places, so we round it.

[math]::Round()

Inside the () for the Round, we have this expression

$($_.TotalItemSize.Value.ToString().Replace(",", "").Split("(")[1].Split(" bytes")[0]) / 1GB

The TotalItemSize is modified to become a string

$_.TotalItemSize.Value.ToString()

then we replace the commas in the string

.Replace(",", "")

split it at the first ( and grab the second part of the array

.Split("(")[1]

then split again at bytes and grab the first part of that array

.Split(" bytes")[0]

Finally we finish the Round() statement with a ,2 which gives us 2 decimal places rounded.

Where-Object

The next pipeline sets the Where-Object and we’re only concerned about mailboxes over 45 GB in this example. You can change this to whatever you’d like to filter based on.

| Where-Object { $_.TotalItemSize -gt 45 }

Format-Table

The last stage, Format-Table -AutoSize, prints the result as a table sized to fit its content.

| Format-Table -AutoSize

Usage

Connect to Exchange Online, then run the command from the Script section below.

Connect-ExchangeOnline

It prints each mailbox over the threshold with its display name and size in GB.

DisplayName    TotalItemSize
-----------    -------------
<display-name>         52.31

To list mailboxes over 100 GB instead, change the 45 in the Where-Object stage.

| Where-Object { $_.TotalItemSize -gt 100 }

Script

The full command, expanded across lines. Each line ends with a pipe so it can be pasted straight into an Exchange Online session.

<#
.SYNOPSIS
    Lists Exchange Online mailboxes larger than a size threshold.
.DESCRIPTION
    Gets all mailboxes, passes each primary SMTP address to Get-MailboxStatistics (so
    duplicate display names do not matter), converts TotalItemSize to GB and keeps the
    mailboxes over 45 GB.
.EXAMPLE
    .\Get-MailboxOverSize.ps1
.NOTES
    Author  : Thomas Lasswell (https://www.techcolumnist.com)
    Version : 1.0 (2021-02-18)
    Requires: Exchange Online PowerShell session; permission to run Get-Mailbox and Get-MailboxStatistics
#>

Get-Mailbox -ResultSize Unlimited |
    Select-Object @{ Name = 'Identity'; Expression = { $_.PrimarySmtpAddress } } |
    Get-MailboxStatistics |
    Select-Object DisplayName, @{
        Name       = "TotalItemSize"
        Expression = {
            [math]::Round($($_.TotalItemSize.Value.ToString().Replace(",", "").Split("(")[1].Split(" bytes")[0]) / 1GB, 2)
        }
    } |
    Where-Object { $_.TotalItemSize -gt 45 } |
    Format-Table -AutoSize