Office 365 / Exchange Online Mailbox Size Report Including Mailbox Free Space with PowerShell

Office 365 / Exchange Online Mailbox Size Report Including Mailbox Free Space with PowerShell

I wanted to create an automated alert that would notify me of Office 365 mailboxes that were approaching full, and found a terrific PowerShell reporting script by Ruud (LazyAdmin.nl). If you have not checked out his blog, stop reading now and head on over there. There is a ton of great scripts and write ups! The script connects to your Office 365 environment and exports the following:

  • Display name
  • Primary Email address
  • Mailbox type
  • Last user action time
  • Total mailbox size
  • Mailbox Free Space (NEW!)
  • 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 Mailbox Free Space (NEW!)
  • Archive Deleted Item Count
  • Archive warning quota
  • Archive quota

 

Shared Mailboxes

It also allows you to filter the mailbox type that you want to gather results for. By default, Shared Mailboxes are included, but you can filter this by calling the -SharedMailboxes parameter.

The code block below will only gather results for Shared Mailboxes

.\MailboxSizeReport.ps1 -adminUPN [email protected] -sharedMailboxes only

The code block below will exclude shared mailboxes

.\MailboxSizeReport.ps1 -admunUPN [email protected] -sharedMailboxes none

The code block below will include both user and shared mailboxes. (Default)

.\MailboxSizeReport.ps1 -admunUPN [email protected] -sharedMailboxes include

Archive Mailboxes

Archive Mailboxes are included by default. By calling the -Archive parameter you can exclude them.

.\MailboxSizeReport.ps1 -admunUPN [email protected] -Archive:$False

New Additions

I went to his GitHub and forked his repo and made the following changes:

  • All values are now int32 instead of strings. Some values would be “30 GB” while others were integers like “30”
  • Exporting the results to CSV file is now optional, this allows an administrator to view the report straight in the shell
  • I added ‘Mailbox Free Space’ so we can quickly view how much free space each mailbox currently has
  • I added ‘Archive Mailbox Free Space*’ but added an asterisk as many Office 365 licenses have ‘soft limits’ on the archive mailbox and as you approach that limit it will auto-expand itself. I myself don’t have any value on viewing the archive mailbox free space size due to that reason but I wanted to include it as some administrators may need reporting on this.

Code

The code can be found on GitHub but as always, I will also paste it here. To ensure that you are using the latest version its recommended to always get the code via GitHub. I also submitted a Pull Request to the original repo.

<#
.SYNOPSIS
  Create report of all mailbox and archive sizes
.DESCRIPTION
  Collects all the mailbox and archive stats from Exchange Online users. By default it will also
  include the Shared Mailboxes. 
.EXAMPLE
  Get-MailboxSizeReport.ps1
  Generate the mailbox size report with Shared mailboxes, mailbox archive.
.EXAMPLE
  Get-MailboxSizeReport.ps1 -sharedMailboxes only
  Get only the shared mailboxes
.EXAMPLE
  Get-MailboxSizeReport.ps1 -sharedMailboxes no
  Get only the user mailboxes
.EXAMPLE
  Get-MailboxSizeReport.ps1 -archive:$false
  Get the mailbox size without the archive mailboxes
.EXAMPLE
  Get-MailboxSizeReport.ps1 -CSVpath c:\temp\report.csv
  Store CSV report in c:\temp\report.csv
.EXAMPLE
  Get-MailboxSizeReport.ps1 | Format-Table
  Print results for mailboxes in the console and format as table
.NOTES
  Version:        1.3
  Author:         R. Mens - LazyAdmin.nl
  Modified By:    Bradley Wyatt - The Lazy Administrator
  Creation Date:  23 sep 2021
  Modified Date:  26 aug 2022
  Purpose/Change: Check if we have a mailbox, before running the numbers
  Link:           https://lazyadmin.nl/powershell/office-365-mailbox-size-report
#>

param(
  [Parameter(
    Mandatory = $true,
    HelpMessage = "Enter the Exchange Online or Global admin username"
  )]
  [string]$adminUPN,

  [Parameter(
    Mandatory = $false,
    HelpMessage = "Get (only) Shared Mailboxes or not. Default include them"
  )]
  [ValidateSet("no", "only", "include")]
  [string]$sharedMailboxes = "include",

  [Parameter(
    Mandatory = $false,
    HelpMessage = "Include Archive mailboxes"
  )]
  [switch]$archive = $true,

  [Parameter(
    Mandatory = $false,
    HelpMessage = "Enter path to save the CSV file"
  )]
  [string]$CSVpath
)

Function ConnectTo-EXO {
  <#
    .SYNOPSIS
        Connects to EXO when no connection exists. Checks for EXO v2 module
  #>
  
  process {
    # Check if EXO is installed and connect if no connection exists
    if ($null -eq (Get-Module -ListAvailable -Name ExchangeOnlineManagement))
    {
      Write-Host "Exchange Online PowerShell v2 module is required, do you want to install it?" -ForegroundColor Yellow
      
      $install = Read-Host Do you want to install module? [Y] Yes [N] No 
      if($install -match "[yY]") 
      { 
        Write-Host "Installing Exchange Online PowerShell v2 module" -ForegroundColor Cyan
        Install-Module ExchangeOnlineManagement -Repository PSGallery -AllowClobber -Force
      } 
      else
      {
	      Write-Error "Please install EXO v2 module."
      }
    }


    if ($null -ne (Get-Module -ListAvailable -Name ExchangeOnlineManagement)) 
    {
	    # Check if there is a active EXO sessions
	    $psSessions = Get-PSSession | Select-Object -Property State, Name
	    If (((@($psSessions) -like '@{State=Opened; Name=ExchangeOnlineInternalSession*').Count -gt 0) -ne $true) {
		    Connect-ExchangeOnline -UserPrincipalName $adminUPN
	    }
    }
    else{
      Write-Error "Please install EXO v2 module."
    }
  }
}

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-Object UserPrincipalName, DisplayName, PrimarySMTPAddress, RecipientType, RecipientTypeDetails, IssueWarningQuota, ProhibitSendReceiveQuota, ArchiveQuota, ArchiveWarningQuota, ArchiveDatabase
  }
}

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)
    }
  }
}


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

    $mailboxes | ForEach-Object {

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

      if ($null -ne $mailboxSize) {
      
        # Get archive size if it exists and is requested
        $archiveSize = 0
        $archiveResult = $null

        if ($archive.IsPresent -and ($null -ne $_.ArchiveDatabase)) {
          $archiveResult = Get-EXOMailboxStatistics -UserPrincipalName $_.UserPrincipalName -Archive | Select-Object ItemCount,DeletedItemCount,@{Name = "TotalArchiveSize"; Expression = {$_.TotalItemSize.ToString().Split("(")[0]}}
          if ($null -ne $archiveResult) {
            $archiveSize = ConvertTo-Gb -size $archiveResult.TotalArchiveSize
          }else{
            $archiveSize = 0
          }
        }  
    
        [pscustomobject]@{
          "Display Name" = $_.DisplayName
          "Email Address" = $_.PrimarySMTPAddress
          "Mailbox Type" = $_.RecipientTypeDetails
          "Last User Action Time" = $mailboxSize.LastUserActionTime
          "Total Size (GB)" = ConvertTo-Gb -size $mailboxSize.TotalItemSize.ToString().Split("(")[0]
          "Deleted Items Size (GB)" = ConvertTo-Gb -size $mailboxSize.TotalDeletedItemSize.ToString().Split("(")[0]
          "Item Count" = $mailboxSize.ItemCount
          "Deleted Items Count" = $mailboxSize.DeletedItemCount
          "Mailbox Warning Quota (GB)" = ($_.IssueWarningQuota.ToString().Split("(")[0]).Split(" GB") | Select-Object -First 1
          "Max Mailbox Size (GB)" = ($_.ProhibitSendReceiveQuota.ToString().Split("(")[0]).Split(" GB") | Select-Object -First 1
          "Mailbox Free Space (GB)" = (($_.ProhibitSendReceiveQuota.ToString().Split("(")[0]).Split(" GB") | Select-Object -First 1) - (ConvertTo-Gb -size $mailboxSize.TotalItemSize.ToString().Split("(")[0])
          "Archive Size (GB)" = $archiveSize
          "Archive Items Count" = $archiveResult.ItemCount
          "Archive Mailbox Free Space (GB)*" = (ConvertTo-Gb -size $_.ArchiveQuota.ToString().Split("(")[0]) - $archiveSize
          "Archive Deleted Items Count" = $archiveResult.DeletedItemCount
          "Archive Warning Quota (GB)" = ($_.ArchiveWarningQuota.ToString().Split("(")[0]).Split(" GB") | Select-Object -First 1
          "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++;
      }
    }
  }
}

# Connect to Exchange Online
ConnectTo-EXO

If ($CSVpath) {
    # Get mailbox status
    Get-MailboxStats | Export-CSV -Path $CSVpath -NoTypeInformation -Encoding UTF8
    if ((Get-Item $CSVpath).Length -gt 0) {
        Write-Host "Report finished and saved in $CSVpath" -ForegroundColor Green
    } 
    else {
        Write-Host "Failed to create report" -ForegroundColor Red
    }
}
Else {
    Get-MailboxStats
}

Leave a Reply

Your email address will not be published.