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

# Add -Force to it when you need to update EXO V1.
Install-Module -Name ExchangeOnlineManagement -Force

$path = "\\\shares\IT\MailboxReport"
$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

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 


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.
    Blogger Comment


Post a Comment

Note: Only a member of this blog may post a comment.