Blog

Office 365 Mailbox Size Report with PowerShell

Source: https://lazyadmin.nl/powershell/office-365-mailbox-size-report/

Keeping track of your Office 365 Mailbox sizes is important. You don’t want the mailboxes of your users to reach their send and receive quota, to prevent errors like “Mailbox size limit Exceeded”.

With the help of PowerShell, we can create an Office 365 Mailbox Size Report that gives you all the info you need.

The PowerShell script below exports the following data from user, shared, and archive mailboxes to a CSV file:

  • Display name
  • Primary Emailadres
  • Mailbox type
  • Last user action time
  • Total mailbox size
  • Deleted item size
  • Item Count
  • Deleted Item Count
  • Issue Warning Size
  • Prohibit Send Receive Quota (max mailbox size)
  • Archive size (if the user has an archive)
  • Archive Item Count
  • Archive Deleted Item Count
  • Archive warning quota
  • Archive quota
office 365 mailbox size report
Example office 365 mailbox size report export

I can just simply share the script with you, but it’s always good to understand how a script works. So we are first going to walk through the script, while I explain the different options. At the end of the article, you will find a link to the script in my Github.

How to use the Office 365 Mailbox Size Report

The Office 365 Mailbox size report script can be run with a couple of different parameters. You always need to supply your email address for the authentication with the -adminUPN parameter. Without any further parameters, the script will generate a report with:

  • Shared mailboxes
  • Archive mailboxes
  • Store the report in the script root location ( .\mailboxsizereport-sep-23-2021.csv)
.\MailboxSizeReport.ps1 -adminUPN [email protected]
get-mailboxstatistics powershell

Shared Mailboxes

You have a couple of options for the shared mailboxes. By default, they are included, but you can leave them out of the report or get only the shared mailboxes with the -sharedMailboxes parameter:

# Get only the shared mailboxes
.\MailboxSizeReport.ps1 -adminUPN [email protected] -sharedMailboxes only

# Get only the user mailboxes
.\MailboxSizeReport.ps1 -adminUPN [email protected] -sharedMailboxes no

# (Default) Get user and shared mailboxes
.\MailboxSizeReport.ps1 -adminUPN [email protected] -sharedMailboxes include

Archive Mailboxes

By default, we also gather the statistics of the archive mailboxes if the user has an archive mailbox. If you don’t want the archive mailboxes (they make the script a bit slower) then use the -archive:$false parameter:

# Don't get the archive mailboxes
.\MailboxSizeReport.ps1 -adminUPN [email protected] -archive:$false

CSV Export Path

You can define an output file and path with the -path parameter.

# Define path for CSV file
.\MailboxSizeReport.ps1 -adminUPN [email protected] -path c:\temp\mailboxsizereport.csv

The Mailbox Size Report Script

To get the Office 365 mailbox size we first need to connect to Exchange Online. Then we need both the mailbox properties and the mailbox statistics to create the complete report.

I have used the Exchange Online v2 Module for the script. The advantage of the new Exchange Online module is that it supports MFA and SSON. This way you only have to enter your email address and we can connect to Exchange Online without any user interaction.

To make the script easier to use on other computers I have added a check for the Exchange Online Management module and the option to install it when it’s not available. We also check for an existing Exchange Online connection to prevent multiple connections.

You can find a sample of the code here in this article.

Get Mailbox Size with PowerShell

To get the mailbox statistics we first need to gather all the mailboxes. The Get-Mailbox cmdlet in PowerShell returns all the mailbox properties, where the Get-MailboxStatistics returns the information about the mailbox usage.

So first we get all the mailboxes that we want for our report

Function Get-Mailboxes {
  <#
    .SYNOPSIS
        Get all the mailboxes for the report
  #>
  process {
    switch ($sharedMailboxes)
    {
      "include" {$mailboxTypes = "UserMailbox,SharedMailbox"}
      "only" {$mailboxTypes = "SharedMailbox"}
      "no" {$mailboxTypes = "UserMailbox"}
    }

    Get-EXOMailbox -ResultSize unlimited  -RecipientTypeDetails $mailboxTypes -Properties IssueWarningQuota, ProhibitSendReceiveQuota, ArchiveQuota, ArchiveWarningQuota, ArchiveDatabase | 
      select UserPrincipalName, DisplayName, PrimarySMTPAddress, RecipientType, IssueWarningQuota, ProhibitSendReceiveQuota, ArchiveQuota, ArchiveWarningQuota, ArchiveDatabase
  }
}

Based on the SharedMailboxes parameter we determine if we want to include them or not. From the Get-EXOMailbox cmdlet we need to get a couple of extra properties:

  • IssueWarningQuota
  • ProhibitSendReceiveQuota
  • ArchiveQuota
  • ArchiveWarningQuota
  • ArchiveDatabase

With the ArchiveDatabase property, we can determine if the user mail also has an archive mailbox. By using the select we only return the fields that we need in the mailbox report.

Get-MailboxStatistics in PowerShell

For each mailbox, we are going to get the mailbox statistics. Now we also want to include the archive mailbox of the users. For this, we will need to run the Get-MailboxStatistics cmdlet twice. Once for the normal mailbox, and once for the archive mailbox.

Now I have noticed that the latter is a lot slower. I don’t know exactly why, but requesting the stats from the archive mailbox takes noticeably longer than the normal mailbox.

For the normal mailbox we can simply use the following cmdlet in PowerShell:

# Get mailbox size
$mailboxSize = Get-MailboxStatistics -Identity $_.UserPrincipalName | Select TotalItemSize,TotalDeletedItemSize,ItemCount,DeletedItemCount,LastUserActionTime

For the archive mailbox, we first check if we want the archive mailbox and then if the ArchiveDatabase property is set:

# Get archive size if it exists and is requested
$archiveSize = 0

if ($archive.IsPresent -and ($_.ArchiveDatabase -ne $null)) {
        $result = Get-EXOMailboxStatistics -UserPrincipalName $_.UserPrincipalName -Archive | Select ItemCount,DeletedItemCount,@{Name = "TotalArchiveSize"; Expression = {$_.TotalItemSize.ToString().Split("(")[0]}}
        if ($result -ne $null) {
          $archiveSize = ConvertTo-Gb -size $result.TotalArchiveSize
        }else{
          $archiveSize = 0
        }
}

You might have noticed that I used the Get-EXOMailboxStatistics for the archive mailbox and the older cmdlet, Get-MailboxStatistics for the normal mailbox. I always try to use the latest modules/cmdlets, but the LastUserActionTime is only available in the older cmdlet.

For the mailboxes without Archive, we set the archiveSize to 0 and reset the archiveResult.

All the data is put into a PSCustomObject allowing us to easily export it later to a CSV file.

Function Get-MailboxStats {
  <#
    .SYNOPSIS
        Get the mailbox size and quota
  #>
  process {
    $mailboxes = Get-Mailboxes
    $i = 0

    $mailboxes | ForEach {

      # Get mailbox size     
      $mailboxSize = Get-MailboxStatistics -identity $_.UserPrincipalName | Select TotalItemSize,TotalDeletedItemSize,ItemCount,DeletedItemCount,LastUserActionTime
      
      # Get archive size if it exists and is requested
      $archiveSize = 0
      $archiveResult = $null

      if ($archive.IsPresent -and ($_.ArchiveDatabase -ne $null)) {
        $archiveResult = Get-EXOMailboxStatistics -UserPrincipalName $_.UserPrincipalName -Archive | Select ItemCount,DeletedItemCount,@{Name = "TotalArchiveSize"; Expression = {$_.TotalItemSize.ToString().Split("(")[0]}}
        if ($archiveResult -ne $null) {
          $archiveSize = ConvertTo-Gb -size $archiveResult.TotalArchiveSize
        }else{
          $archiveSize = 0
        }
      }  
    
      [pscustomobject]@{
        "Display Name" = $_.DisplayName
        "Emailaddress" = $_.PrimarySMTPAddress
        "Mailbox type" = $_.RecipientTypeDetails
        "Last user action time" = $mailboxSize.LastUserActionTime
        "Total size (Gb)" = ConvertTo-Gb -size $mailboxSize.TotalItemSize.ToString().Split("(")[0]
        "Delete item size (Gb)" = ConvertTo-Gb -size $mailboxSize.TotalDeletedItemSize.ToString().Split("(")[0]
        "Item Count" = $mailboxSize.ItemCount
        "Deleted Item Count" = $mailboxSize.DeletedItemCount
        "Mailbox Warning quota (GB)" = $_.IssueWarningQuota.ToString().Split("(")[0]
        "Max mailbox size (Gb)" = $_.ProhibitSendReceiveQuota.ToString().Split("(")[0]
        "Archive size (Gb)" = $archiveSize
        "Archive Item Count" = $archiveResult.ItemCount
        "Archive Deleted Item Count" = $archiveResult.DeletedItemCount
        "Archive Warning quota (GB)" = $_.ArchiveWarningQuota.ToString().Split("(")[0]
        "Archive quota (Gb)" = ConvertTo-Gb -size $_.ArchiveQuota.ToString().Split("(")[0]
      }

      $currentUser = $_.DisplayName
      Write-Progress -Activity "Collecting mailbox status" -Status "Current Count: $i" -PercentComplete (($i / $mailboxes.Count) * 100) -CurrentOperation "Processing mailbox: $currentUser"
      $i++;
    }
  }
}

You may have noticed that we convert all the sizes to GB. The reason for this is that the Get-MailboxStatistics returns the size in KB, MB, or GB, depending on the size. But in a report, it’s much more convenient to have a single unit.

So with the function below we can convert all the sizes to GB and rounds them into two decimals:

Function ConvertTo-Gb {
  <#
    .SYNOPSIS
        Convert mailbox size to Gb for uniform reporting.
  #>
  param(
    [Parameter(
      Mandatory = $true
    )]
    [string]$size
  )
  process {
    if ($size -ne $null) {
      $value = $size.Split(" ")

      switch($value[1]) {
        "GB" {$sizeInGb = ($value[0])}
        "MB" {$sizeInGb = ($value[0] / 1024)}
        "KB" {$sizeInGb = ($value[0] / 1024 / 1024)}
      }

      return [Math]::Round($sizeInGb,2,[MidPointRounding]::AwayFromZero)
    }
  }
}

Download the Complete Script

You can download the complete script from my GitHub page. I recommend you test it first on a small set of mailboxes. Change the ResultSize in line 122 (Get-EXOMailboxes) from unlimited to 10 for example.

# Change this line
Get-EXOMailbox -ResultSize unlimited -RecipientTypeDetails $mailboxTypes -Properties IssueWarningQuota, ProhibitSendReceiveQuota, ArchiveQuota, ArchiveWarningQuota, ArchiveDatabase | 
      select UserPrincipalName, DisplayName, PrimarySMTPAddress, RecipientType, RecipientTypeDetails, IssueWarningQuota, ProhibitSendReceiveQuota, ArchiveQuota, ArchiveWarningQuota, ArchiveDatabase

# To 
Get-EXOMailbox -ResultSize 10 -RecipientTypeDetails $mailboxTypes -Properties IssueWarningQuota, ProhibitSendReceiveQuota, ArchiveQuota, ArchiveWarningQuota, ArchiveDatabase | 
      select UserPrincipalName, DisplayName, PrimarySMTPAddress, RecipientType, RecipientTypeDetails, IssueWarningQuota, ProhibitSendReceiveQuota, ArchiveQuota, ArchiveWarningQuota, ArchiveDatabase

Wrapping Up

To run this script easily from your PowerShell command line, you can add use an alias in your profile that points to the script. Read everything about settings up your PowerShell profile in this article or how to run PowerShell scripts in general in this article.

I hope you find this script useful. If you have any questions just drop a comment below.

You may also like one of the following PowerShell report scripts:

Tags: , ,
Close Menu