Exchange Online Mailbox Size Report Using PowerShell (with Power BI Report Template!)

Do you need to get a scheduled report of all the user mailbox sizes in M365? Use this PowerShell script, and attach it to a scheduled task on one of your servers. This exports a CSV file of all mailboxes, including sizes.

The PowerShell Script

Set-ExecutionPolicy RemoteSigned

Enable-PSRemoting

# Add -Force to it when you need to update EXO V1.

Install-Module -Name ExchangeOnlineManagement -Force

$path = "\\contoso.int\shares\IT\MailboxReport"

$Username="admin@contoso.com"

$AdminPassword=ConvertTo-SecureString "adminPassword!" -AsPlainText -Force

$creds=New-Object System.Management.Automation.PSCredential($Username,$AdminPassword)

# Connect-ExchangeOnline -UserPrincipalName $Username

Connect-ExchangeOnline -Credential $creds

# Set the threshold size. Change this to your preferred value.

$size_Threshold_GB = 0.00

# Get a list of all mailboxes

$mailbox_List = Get-EXOMailbox -ResultSize Unlimited | Select-Object DisplayName, PrimarySMTPAddress, UserPrincipalName

# Create an empty array to hold the report

$finalResult = @()

# Loop through each of the mailbox object inside the $mailbox_List variable.

foreach ($mailbox in $mailbox_List) {

    # Get the Mailbox Size in GB, rounded with two-decimal places

    $mailbox_size_GB = [math]::Round(((Get-EXOMailboxStatistics -Identity $mailbox.UserPrincipalName).TotalItemSize.Value.toBytes() / 1GB),5)

    <#

    Compare the mailbox size with the configured threshold.

    If the mailbox size is bigger than the threshold, add the result to the report.

    #>

    if ($mailbox_size_GB -gt $size_Threshold_GB) {

        <#

        Create the object with properties 'Display Name', 'Email Address' and 'Mailbox Size (GB)'

        Then add it to the final report.

        #>

        $finalResult += (

            New-Object psobject -Property @{

                'Display Name'      = $mailbox.DisplayName

                'UPN'               = $mailbox.UserPrincipalName

                'Email Address'     = $mailbox.PrimarySMTPAddress

                'Mailbox Size (GB)' = $mailbox_size_GB

            }

        )

    }

}

$timestamp = Get-Date -Format o | ForEach-Object { $_ -replace ":", "." }

# return the final result

return $finalResult | Export-Csv -NoTypeInformation -Path $path"MailboxSizeReport-"$timestamp".csv"

Disconnect-ExchangeOnline -Confirm:$false

To create a scheduled task, open task scheduler and have it start this program

Program/Script:

"C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe"

Add Arguments:

-NonInteractive -WindowStyle Hidden -ExecutionPolicy RemoteSigned -File "C:\scripts\Office 365 Mailbox Size Report.ps1"

From there, I created a Power BI report to visualize all the changes going on in the environment. 

Click here to download the Power BI template file 

Sources:

• https://adamtheautomator.com/office-365-mailbox-size/
• https://docs.microsoft.com/en-us/powershell/module/exchange/disconnect-exchangeonline?view=exchange-ps
• https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/get-date?view=powershell-7.2

Share on Google Plus

About Tom DeMeulenaere

Highly accomplished information technology professional with extensive knowledge in System Center Configuration Manager, Windows Server, SharePoint, and Office 365.