Get Exchange Online Mailbox Sizes – with UPN!

There are times you need to extract the current mailbox sizes from a 365 tenant, such as when preparing for a migration, or perhaps reviewing licensing assignments. Many one-liner examples exist online which is great, but I like to rewrite them or add something I find useful. In this case I want the UPN of the user (along with the Display Name). The Get-MailboxStatistics command only reveals the Display Name, therefore a loop is required so that we can get the UPN using Get-Mailbox and add it to the output. I also want the data in a CSV file, and to open the file with Excel at the end of the script.

To use the script, replace the variables at the top with the tenant name (or a custom domain) you want to connect to, and the full path to the output file. Example values are in bold. This will output “Username, DisplayName, MailboxType, TotalSizeGB, ItemCount, LastLogon” columns and detail to the CSV file, and then open it in Excel. Enjoy & improve! Bye bye… Simon. 👀🍻

While processing you will see the following:

Followed by the data presented in Excel:

$tenantname = 'mydomain.co.nz'
$csvfile    =  'c:\temp\MailboxDetails.csv'

if (-not(Get-InstalledModule *exchange*)) {
    Install-Module exchangeonlinemanagement -Force
}

Connect-ExchangeOnline -DelegatedOrganization $tenantname

$upns = (Get-Mailbox).UserPrincipalName
$mbxcount = $upns.count
$x = 1

foreach ($upn in $upns) {

    Clear-Host
    Write-Output ""
    Write-Output "Processing mailbox $x of $mbxcount ($upn)..."

    $username   = (Get-Mailbox $upn).UserPrincipalName
    $stats      = Get-MailboxStatistics $upn | Select-Object DisplayName,MailboxTypeDetail,@{Name="TotalSizeGB"; Expression={[math]::Round(($_.TotalItemSize.ToString().Split("(")[1].Split(" ")[0].Replace(",","")/1GB),0)}},ItemCount,LastLogonTime

    $dispname       = $stats.DisplayName
    $mbxtype        = $stats.MailboxTypeDetail.Value
    $mbxsizegb      = $stats.TotalSizeGB
    $mbxitemcount   = $stats.ItemCount
    $mbxlastlogon   = $stats.LastLogonTime

    $output = $username + "," + $dispname + "," + $mbxtype + "," + $mbxsizegb + "," + $mbxitemcount + "," + $mbxlastlogon

    If ($x -eq 1) {
        $header = "Username,DisplayName,MailboxType,TotalSizeGB,ItemCount,LastLogon"
        $header | Out-File -Path $csvfile -Force
        $output | Out-File -Path $csvfile -Force -Append
    }
        else {
            $output | Out-File -Path $csvfile -Force -Append
        }
$x = $x + 1
}

Write-Output ""
Write-Output "Opening $csvfile..."
Start-Process "excel.exe" -ArgumentList "$csvfile"