Weekly Microsoft 365 mailbox report

The problem

You want a Monday-morning snapshot of every Microsoft 365 mailbox: owner, size, item count, last logon, archive enabled or not. Useful for licence planning and to spot mailboxes approaching their quota.

This recipe logs into Exchange Online non-interactively (certificate-based app auth), pulls mailbox stats, and emails a CSV to your team DL.

Prerequisites

• An Entra ID app registration with Exchange.ManageAsApp permission granted admin consent
• A self-signed cert uploaded to that app (thumbprint below)
• The ExchangeOnlineManagement module (Install-Module ExchangeOnlineManagement)

The script

param(
  [string] $AppId      = '00000000-0000-0000-0000-000000000000',
  [string] $Tenant     = 'contoso.onmicrosoft.com',
  [string] $Thumbprint = 'AA11BB22...CC33'
)

Import-Module ExchangeOnlineManagement -ErrorAction Stop

Connect-ExchangeOnline `
  -AppId $AppId `
  -Organization $Tenant `
  -CertificateThumbprint $Thumbprint `
  -ShowBanner:$false

$mailboxes = Get-Mailbox -ResultSize Unlimited

$rows = foreach ($m in $mailboxes) {
  $stats   = Get-MailboxStatistics $m.UserPrincipalName
  $archive = try { Get-MailboxStatistics $m.UserPrincipalName -Archive } catch { $null }

  [pscustomobject]@{
    DisplayName       = $m.DisplayName
    UPN               = $m.UserPrincipalName
    SizeGB            = [math]::Round(($stats.TotalItemSize.Value.ToBytes() / 1GB), 2)
    Items             = $stats.ItemCount
    LastLogon         = $stats.LastLogonTime
    ArchiveEnabled    = [bool]$m.ArchiveDatabase
    ArchiveSizeGB     = if ($archive) {
      [math]::Round(($archive.TotalItemSize.Value.ToBytes() / 1GB), 2)
    } else { 0 }
  }
}

$out = "mailbox-report-$(Get-Date -f yyyy-MM-dd).csv"
$rows | Sort-Object SizeGB -Descending | Export-Csv $out -NoTypeInformation -Encoding UTF8

Disconnect-ExchangeOnline -Confirm:$false

# Mail it via Graph — no SMTP creds needed
$body = @{
  message = @{
    subject = "M365 mailbox report — $(Get-Date -f 'yyyy-MM-dd')"
    body    = @{ contentType = 'Text'; content = 'See attachment.' }
    toRecipients = @(@{ emailAddress = @{ address = 'it@contoso.com' } })
    attachments  = @(@{
      '@odata.type' = '#microsoft.graph.fileAttachment'
      name          = (Split-Path $out -Leaf)
      contentBytes  = [Convert]::ToBase64String([IO.File]::ReadAllBytes($out))
    })
  }
  saveToSentItems = $true
}

# Assumes you have an authenticated Graph context (Connect-MgGraph)
Send-MgUserMail -UserId 'reports@contoso.com' -BodyParameter $body

Schedule it

Register as a scheduled task running under gMSA:

$act = New-ScheduledTaskAction -Execute 'pwsh' `
  -Argument '-File C:\scripts\mailbox-report.ps1'
$trg = New-ScheduledTaskTrigger -Weekly -DaysOfWeek Monday -At 6am
Register-ScheduledTask -TaskName 'MailboxReport' -Action $act -Trigger $trg `
  -Principal (New-ScheduledTaskPrincipal -UserId 'contoso\svc_reports$' -LogonType Password)

Why certificate auth?

Passwords or client secrets in a weekly task rotate at exactly the wrong moment. A cert in the machine keystore with a long expiry is boring, safe, and auditable.

Extensions

• Pipe the CSV into Log Analytics for trend charts.
• Flag mailboxes over 90% quota with a red background in the HTML body.
• Merge with Get-CASMailbox to include OWA / mobile access flags.