Exchange Online mailbox size and archive report

<#
.SYNOPSIS
    Get-CMMailboxSizeReport.ps1

.DESCRIPTION
    Exports Exchange Online mailbox sizes, item counts, archive size, last logon time
    and mailbox type to a CSV file. Read-only: it changes nothing in your tenant.

    Provided by cloud-migrations.au as is; test before use.
    Read the script before you run it and try it in a test tenant first.

.NOTES
    Requirements
      - ExchangeOnlineManagement module:  Install-Module -Name ExchangeOnlineManagement
      - An Exchange Online admin role that can run Get-EXOMailbox and Get-EXOMailboxStatistics
      - Windows PowerShell 5.1 or PowerShell 7
    Commands used (all documented by Microsoft):
      Connect-ExchangeOnline, Get-EXOMailbox, Get-EXOMailboxStatistics, Disconnect-ExchangeOnline

.EXAMPLE
    .\Get-CMMailboxSizeReport.ps1 -AdminUpn admin@contoso.com

.EXAMPLE
    .\Get-CMMailboxSizeReport.ps1 -RecipientTypeDetails SharedMailbox -OutputPath C:\Reports\shared.csv -SkipArchive
#>
[CmdletBinding()]
param(
    [string]$AdminUpn,
    [string]$OutputPath = (Join-Path -Path (Get-Location) -ChildPath ('MailboxSizeReport_{0}.csv' -f (Get-Date -Format 'yyyyMMdd_HHmm'))),
    [string[]]$RecipientTypeDetails = @('UserMailbox', 'SharedMailbox', 'RoomMailbox', 'EquipmentMailbox'),
    [switch]$SkipArchive,
    [switch]$SkipConnect
)

$ErrorActionPreference = 'Stop'

# Sizes come back as text such as "1.2 GB (1,288,490,188 bytes)". Pull out the byte count for sorting in Excel.
function Get-CMBytes {
    param($Size)
    if ($null -eq $Size) { return $null }
    $m = [regex]::Match($Size.ToString(), '\(([\d,]+) bytes\)')
    if ($m.Success) { return [int64]($m.Groups[1].Value -replace ',', '') }
    return $null
}

# 1. Connect
if (-not $SkipConnect) {
    try {
        Write-Host 'Connecting to Exchange Online...'
        if ($AdminUpn) { Connect-ExchangeOnline -UserPrincipalName $AdminUpn }
        else { Connect-ExchangeOnline }
    }
    catch {
        Write-Error "Could not connect to Exchange Online: $($_.Exception.Message)"
        return
    }
}

# 2. Get the mailboxes. The Archive property set adds ArchiveGuid and ArchiveStatus.
try {
    Write-Host 'Reading mailboxes (this can take a while in large tenants)...'
    $mailboxes = @(Get-EXOMailbox -ResultSize Unlimited -RecipientTypeDetails $RecipientTypeDetails -PropertySets Minimum, Archive)
}
catch {
    Write-Error "Get-EXOMailbox failed: $($_.Exception.Message)"
    if (-not $SkipConnect) { Disconnect-ExchangeOnline -Confirm:$false }
    return
}
Write-Host ("Found {0} mailboxes." -f $mailboxes.Count)

# 3. Get statistics for each mailbox
$results = New-Object System.Collections.Generic.List[object]
$i = 0
foreach ($mbx in $mailboxes) {
    $i++
    Write-Progress -Activity 'Reading mailbox statistics' -Status ("{0} of {1}: {2}" -f $i, $mailboxes.Count, $mbx.PrimarySmtpAddress) -PercentComplete (($i / [math]::Max($mailboxes.Count, 1)) * 100)

    $row = [ordered]@{
        DisplayName               = $mbx.DisplayName
        UserPrincipalName         = $mbx.UserPrincipalName
        PrimarySmtpAddress        = $mbx.PrimarySmtpAddress
        RecipientTypeDetails      = $mbx.RecipientTypeDetails
        ItemCount                 = $null
        TotalItemSize             = $null
        TotalItemSizeBytes        = $null
        DeletedItemCount          = $null
        TotalDeletedItemSize      = $null
        LastLogonTime             = $null
        ArchiveEnabled            = ($null -ne $mbx.ArchiveGuid -and $mbx.ArchiveGuid -ne [guid]::Empty)
        ArchiveItemCount          = $null
        ArchiveTotalItemSize      = $null
        ArchiveTotalItemSizeBytes = $null
        Error                     = ''
    }

    try {
        # LastLogonTime is not in the Minimum property set, so ask for it with -Properties.
        $stats = Get-EXOMailboxStatistics -ExternalDirectoryObjectId $mbx.ExternalDirectoryObjectId -Properties LastLogonTime
        $row.ItemCount            = $stats.ItemCount
        $row.TotalItemSize        = "$($stats.TotalItemSize)"
        $row.TotalItemSizeBytes   = Get-CMBytes $stats.TotalItemSize
        $row.DeletedItemCount     = $stats.DeletedItemCount
        $row.TotalDeletedItemSize = "$($stats.TotalDeletedItemSize)"
        $row.LastLogonTime        = $stats.LastLogonTime
    }
    catch {
        $row.Error = "Statistics: $($_.Exception.Message)"
    }

    if ($row.ArchiveEnabled -and -not $SkipArchive) {
        try {
            $arch = Get-EXOMailboxStatistics -ExternalDirectoryObjectId $mbx.ExternalDirectoryObjectId -Archive
            $row.ArchiveItemCount          = $arch.ItemCount
            $row.ArchiveTotalItemSize      = "$($arch.TotalItemSize)"
            $row.ArchiveTotalItemSizeBytes = Get-CMBytes $arch.TotalItemSize
        }
        catch {
            $row.Error = ($row.Error + " Archive: $($_.Exception.Message)").Trim()
        }
    }

    $results.Add([pscustomobject]$row)
}
Write-Progress -Activity 'Reading mailbox statistics' -Completed

# 4. Export
try {
    $results | Export-Csv -Path $OutputPath -NoTypeInformation -Encoding UTF8
    $failed = @($results | Where-Object { $_.Error }).Count
    Write-Host ("Done. {0} mailboxes written, {1} with errors." -f $results.Count, $failed)
    Write-Host "Report saved to: $((Resolve-Path -Path $OutputPath).Path)"
}
catch {
    Write-Error "Could not write the CSV to $OutputPath : $($_.Exception.Message)"
}
finally {
    if (-not $SkipConnect) { Disconnect-ExchangeOnline -Confirm:$false }
}