PowerShell: Export All Office 365 Mailbox Sizes to CSV

<#
.SYNOPSIS
    Export all Office 365 mailbox sizes to CSV.
.DESCRIPTION
    Retrieves mailbox statistics for all user mailboxes in Exchange Online,
    including archive mailboxes, and exports to a timestamped CSV file.
.PARAMETER OutputPath
    Directory to save the CSV. Defaults to the current directory.
.PARAMETER IncludeShared
    Include shared mailboxes in the report.
.PARAMETER IncludeArchive
    Include archive mailbox sizes (slower — requires an extra query per user).
#>

param(
    [string]$OutputPath = ".",
    [switch]$IncludeShared,
    [switch]$IncludeArchive
)

# Connect to Exchange Online
Write-Host "Connecting to Exchange Online..." -ForegroundColor Yellow
Connect-ExchangeOnline -ShowBanner:$false

# Get mailboxes
$recipientTypes = @("UserMailbox")
if ($IncludeShared) {
    $recipientTypes += "SharedMailbox"
}

Write-Host "Fetching mailboxes..." -ForegroundColor Yellow
$mailboxes = Get-Mailbox -ResultSize Unlimited -RecipientTypeDetails $recipientTypes | Select-Object DisplayName, UserPrincipalName, PrimarySmtpAddress, RecipientTypeDetails, ArchiveStatus

$total = $mailboxes.Count
Write-Host "Found $total mailboxes. Collecting statistics..." -ForegroundColor Cyan

$results = [System.Collections.Generic.List[object]]::new()
$i = 0

foreach ($mb in $mailboxes) {
    $i++
    $pct = [math]::Round(($i / $total) * 100)
    Write-Progress -Activity "Collecting mailbox stats" -Status "$i of $total ($pct%)" -PercentComplete $pct

    try {
        $stats = Get-MailboxStatistics -Identity $mb.UserPrincipalName -ErrorAction Stop

        # Parse size string to bytes for sorting
        $sizeString = $stats.TotalItemSize.Value.ToString()
        $sizeBytes = $stats.TotalItemSize.Value.ToBytes()
        $sizeMB = [math]::Round($sizeBytes / 1MB, 2)
        $sizeGB = [math]::Round($sizeBytes / 1GB, 2)

        $record = [PSCustomObject]@{
            DisplayName       = $mb.DisplayName
            Email             = $mb.PrimarySmtpAddress
            Type              = $mb.RecipientTypeDetails
            TotalSizeGB       = $sizeGB
            TotalSizeMB       = $sizeMB
            ItemCount         = $stats.ItemCount
            DeletedItemSizeMB = [math]::Round($stats.TotalDeletedItemSize.Value.ToBytes() / 1MB, 2)
            DeletedItemCount  = $stats.DeletedItemCount
            LastLogonTime     = $stats.LastLogonTime
            ArchiveStatus     = $mb.ArchiveStatus
            ArchiveSizeGB     = ""
            ArchiveItemCount  = ""
        }

        # Get archive stats if requested
        if ($IncludeArchive -and $mb.ArchiveStatus -eq "Active") {
            try {
                $archiveStats = Get-MailboxStatistics -Identity $mb.UserPrincipalName -Archive -ErrorAction Stop
                $record.ArchiveSizeGB = [math]::Round($archiveStats.TotalItemSize.Value.ToBytes() / 1GB, 2)
                $record.ArchiveItemCount = $archiveStats.ItemCount
            } catch {
                $record.ArchiveSizeGB = "Error"
            }
        }

        $results.Add($record)
    } catch {
        Write-Warning "Failed to get stats for $($mb.DisplayName): $_"
        $results.Add([PSCustomObject]@{
            DisplayName = $mb.DisplayName
            Email       = $mb.PrimarySmtpAddress
            Type        = $mb.RecipientTypeDetails
            TotalSizeGB = "Error"
            TotalSizeMB = "Error"
            ItemCount   = ""
            DeletedItemSizeMB = ""
            DeletedItemCount  = ""
            LastLogonTime     = ""
            ArchiveStatus     = $mb.ArchiveStatus
            ArchiveSizeGB     = ""
            ArchiveItemCount  = ""
        })
    }
}

Write-Progress -Activity "Collecting mailbox stats" -Completed

# Sort by size descending
$sorted = $results | Sort-Object { if ($_.TotalSizeGB -is [double]) { $_.TotalSizeGB } else { 0 } } -Descending

# Export to CSV
$timestamp = Get-Date -Format "yyyy-MM-dd_HHmm"
$filename = "MailboxSizes_$timestamp.csv"
$filepath = Join-Path $OutputPath $filename

$sorted | Export-Csv -Path $filepath -NoTypeInformation -Encoding UTF8
Write-Host "`nExported to: $filepath" -ForegroundColor Green

# Summary
$totalSizeGB = ($results | Where-Object { $_.TotalSizeGB -is [double] } | Measure-Object -Property TotalSizeGB -Sum).Sum
$totalItems = ($results | Where-Object { $_.ItemCount -is [int] } | Measure-Object -Property ItemCount -Sum).Sum

Write-Host "`n--- Summary ---" -ForegroundColor Cyan
Write-Host "Mailboxes:    $total"
Write-Host "Total Size:   $([math]::Round($totalSizeGB, 2)) GB"
Write-Host "Total Items:  $($totalItems.ToString('N0'))"
Write-Host "Largest:      $($sorted[0].DisplayName) ($($sorted[0].TotalSizeGB) GB)"

Disconnect-ExchangeOnline -Confirm:$false

.\Export-MailboxSizes.ps1

.\Export-MailboxSizes.ps1 -IncludeShared

.\Export-MailboxSizes.ps1 -IncludeShared -IncludeArchive

.\Export-MailboxSizes.ps1 -OutputPath "C:\Reports"

# Create a scheduled task to run on the 1st of each month
$action = New-ScheduledTaskAction -Execute "powershell.exe" -Argument "-ExecutionPolicy Bypass -File C:\Scripts\Export-MailboxSizes.ps1 -IncludeShared -OutputPath C:\Reports"
$trigger = New-ScheduledTaskTrigger -Monthly -DaysOfMonth 1 -At 6am
Register-ScheduledTask -TaskName "Monthly Mailbox Report" -Action $action -Trigger $trigger -RunLevel Highest