Managing mailbox sizes is critical to maintaining an efficient and organized email system in an Office 365 environment. As an Office 365 administrator, it’s crucial to have visibility into mailbox sizes to identify potential storage issues, enforce mailbox size policies, and optimize resource allocation. Thankfully, PowerShell provides a powerful toolset that enables us to effortlessly generate comprehensive mailbox size reports.
This blog post will guide you through generating an Office 365 mailbox size report using PowerShell. Whether you need to analyze the sizes of individual mailboxes or obtain an overview of all mailboxes in your organization, this step-by-step guide will help you extract the necessary data and present it in a user-friendly format.
By the end of this blog post, you’ll be equipped with the knowledge and tools necessary to generate Office 365 mailbox size reports using PowerShell. So let’s dive in and unlock the power of PowerShell to gain valuable insights into your users’ mailbox sizes.
Table of Contents
Requirements
To effectively generate the Exchange Online Mailbox Size Report, you must meet the following prerequisites.
- Access to an Exchange Online organization.
- Windows PowerShell 5.1 or PowerShell 7+ (recommended).
- The Exchange Online PowerShell V3 module is installed on your computer.
- A code editor, like Visual Studio Code.
Step 1: Connect to Exchange Online PowerShell
Before we can run commands against Exchange, we have to connect to a PowerShell session. It’s up to you how you want to connect to Exchange, such as using your admin credentials or app-only authentication.
In this example, I will use app-only authentication, recommended for automation.
Connect-ExchangeOnline ` -AppId '8d7e4dd3-df34-4416-a51d-7822d1ea1c65' ` -CertificateThumbprint '8AD9C4DD09CBCD20A3A8D78F10A78D0068294643' ` -Organization 'org870b.ga' -ShowBanner:$false
To test whether the connection is successful, run the below command to get your Exchange organization’s display name.
Get-OrganizationConfig | Select-Object DisplayName
Step 2: Get All Exchange Online Mailbox
The next step is to get all mailboxes we will process for the report later. You may include all mailboxes if needed, but I will only include user mailboxes in this example.
$allMailbox = Get-EXOMailbox ` -ResultSize Unlimited ` -RecipientTypeDetails UserMailbox ` -Properties 'DisplayName', 'PrimarySmtpAddress', 'IssueWarningQuota', 'ProhibitSendQuota', 'ProhibitSendReceiveQuota' | ` Select-Object 'DisplayName', 'PrimarySmtpAddress', 'IssueWarningQuota', 'ProhibitSendQuota', 'ProhibitSendReceiveQuota'
This PowerShell code retrieves information about user mailboxes in Exchange Online (Microsoft 365). Let’s break down the code and explain each part:
- $allMailbox = Get-EXOMailbox: This line initiates a variable named $allMailbox and assigns it the result of the Get-EXOMailbox cmdlet. Get-EXOMailbox is a PowerShell cmdlet designed explicitly to retrieve mailbox information for Exchange Online.
- ResultSize Unlimited: This parameter retrieves all mailboxes without any size limitation. It ensures that all mailboxes are included in the result.
- RecipientTypeDetails UserMailbox: This parameter filters the mailboxes to include only user mailboxes. It excludes other types of mailboxes, such as shared or resource mailboxes. Do not use this parameter should you prefer to include all mailbox types.
- Properties ‘DisplayName’, ‘PrimarySmtpAddress’, ‘IssueWarningQuota’, ‘ProhibitSendQuota’, ‘ProhibitSendReceiveQuota’: This parameter specifies the properties of the mailboxes that should be included in the output. The properties listed are ‘DisplayName’, ‘PrimarySmtpAddress’, ‘IssueWarningQuota’, ‘ProhibitSendQuota’, and ‘ProhibitSendReceiveQuota’. These properties provide information about the mailbox display name, primary SMTP address, warning quota, send quota, and send/receive quota, respectively.
- | Select-Object ‘DisplayName’, ‘PrimarySmtpAddress’, ‘IssueWarningQuota’, ‘ProhibitSendQuota’, ‘ProhibitSendReceiveQuota’: This part of the code uses the pipeline operator (|) to pass the output of the Get-EXOMailbox cmdlet to the Select-Object cmdlet. Select-Object is used to choose specific properties from the input objects. In this case, it selects the properties mentioned earlier from the mailbox objects retrieved by Get-EXOMailbox.
The command may take several minutes, depending on how many mailboxes are in the organization. As you can see below, this organization only has 17 user mailboxes, so the runtime only took a few seconds.
Why did we include the mailbox quota limits? Because we’ll use them to calculate the usage percentage and status in the succeeding steps.
But first, notice the quota limit format is not suitable for calculation. For example, the value ‘480 MB (503,316,480 bytes)’ is a string, not a numeric value, and cannot be used in a mathematical operation.
In this case, we must modify the previous command to include string manipulation and convert the quota limit values to numbers. Below is the modified command.
$allMailbox = Get-EXOMailbox `
-ResultSize Unlimited `
-RecipientTypeDetails UserMailbox `
-Properties 'DisplayName', 'PrimarySmtpAddress', 'IssueWarningQuota', 'ProhibitSendQuota', 'ProhibitSendReceiveQuota' |
Select-Object 'DisplayName', 'PrimarySmtpAddress',
@{n = 'IssueWarningQuotaMB'; e = {
[int64](([regex]::Match($_.IssueWarningQuota, "\((.*?)\)")).Groups[1].Value -replace ',', '' -replace ' bytes', '') / 1MB }
},
@{n = 'ProhibitSendQuotaMB'; e = {
[int64](([regex]::Match($_.ProhibitSendQuota, "\((.*?)\)")).Groups[1].Value -replace ',', '' -replace ' bytes', '') / 1MB }
},
@{n = 'ProhibitSendReceiveQuotaMB'; e = {
[int64](([regex]::Match($_.ProhibitSendReceiveQuota, "\((.*?)\)")).Groups[1].Value -replace ',', '' -replace ' bytes', '') / 1MB }
}
Pay attention to the IssueWarningQuotaMB, ProhibitSendQuotaMB, and ProhibitSendReceiveQuotaMB. They included a RegEx pattern “\((.*?)\)” matching statement to:
- Extract the string in between the “(” and “)” characters (e.g., “480 MB (503,316,480 bytes)” = “503,316,480”)
- Replace every “,” character and the word “bytes” with an empty string (e.g., “503,316,480 bytes” = “503316480”)
- Convert the value to MB (e.g., “503316480” (bytes) = “480” (MB)).
The result shows the quota limits are now displayed as numbers.
Step 3: Retrieve the Exchange Mailbox Size
Now that we have the mailbox list and their respective quota limits, let’s gather their current mailbox size and calculate whether their mailbox usage is normal or above the quota.
First, let’s add three new properties to the $allMailbox object. The properties are as follows.
- MailboxSizeMB — The mailbox size property holder. The initial value is 0.
- PercentUsed — The mailbox usage percentage is calculated against the ProhibitSendReceiveQuotaMB value. The initial value is 0.
- Status — The mailbox size status. The initial value is empty.
Run the following command to add those new properties.
$allMailbox | Add-Member -MemberType NoteProperty -Name 'MailboxSizeMB' -Value 0 $allMailbox | Add-Member -MemberType NoteProperty -Name 'PercentUsed' -Value 0 $allMailbox | Add-Member -MemberType NoteProperty -Name 'Status' -Value ''
As you can see below, the new placeholder properties were added.
We’ll loop through the mailbox list to get their mailbox size and update the MailboxSizeMB, PercentUsed, and Status properties.
foreach ($mailbox in $allMailbox) {
$MailboxSize = Get-EXOMailboxStatistics -Identity $mailbox.PrimarySmtpAddress | Select-Object TotalItemSize
$mailbox.MailboxSizeMB = $MailboxSize.TotalItemSize.Value.ToMB()
$mailbox.PercentUsed = [math]::Round(($mailbox.MailboxSizeMB / $mailbox.ProhibitSendReceiveQuotaMB) * 100, 2)
$mailbox.Status = $(
if ($mailbox.MailboxSizeMB -ge $mailbox.ProhibitSendReceiveQuotaMB) {
'Send Receive Disabled'
}
if ($mailbox.MailboxSizeMB -ge $mailbox.ProhibitSendQuotaMB -and $mailbox.MailboxSizeMB -lt $mailbox.ProhibitSendReceiveQuotaMB) {
'Send Disabled'
}
if ($mailbox.MailboxSizeMB -ge $mailbox.IssueWarningQuotaMB -and $mailbox.MailboxSizeMB -lt $mailbox.ProhibitSendQuotaMB) {
'Warning'
}
if ($mailbox.MailboxSizeMB -lt $mailbox.IssueWarningQuotaMB) {
'Normal'
}
)
}
This script iterates through a collection of mailboxes, retrieves their size and quota information, calculates the usage percentage, and assigns a status based on their size and quota thresholds.
Let’s break down the code step by step:
- foreach ($mailbox in $allMailbox) { … }: This line sets up a loop that iterates through each mailbox in the $allMailbox collection.
- $MailboxSize = Get-EXOMailboxStatistics -Identity $mailbox.PrimarySmtpAddress | Select-Object TotalItemSize: This line retrieves the mailbox statistics for the current mailbox using the Get-EXOMailboxStatistics cmdlet. The Identity parameter specifies the primary SMTP address of the mailbox. The Select-Object cmdlet extracts the TotalItemSize property from the mailbox statistics and assigns it to the variable $MailboxSize.
- $mailbox.MailboxSizeMB = $MailboxSize.TotalItemSize.Value.ToMB(): This line converts the TotalItemSize value to megabytes (MB) and assigns it to the MailboxSizeMB property of the current mailbox object. The ToMB() method is a method that converts the value to MB.
- $mailbox.PercentUsed = ($mailbox.MailboxSizeMB / $mailbox.ProhibitSendReceiveQuotaMB) * 100: This line calculates the percentage of mailbox usage by dividing the MailboxSizeMB by the ProhibitSendReceiveQuotaMB property of the current mailbox object and multiplying it by 100. The result is assigned to the PercentUsed property of the mailbox object.
- $mailbox.Status = $( … ): This line assigns the mailbox’s status based on size and quota. It uses a series of nested if statements to determine the status.
- if ($mailbox.MailboxSizeMB -ge $mailbox.ProhibitSendReceiveQuotaMB) { … }: If the mailbox size is greater than or equal to the ProhibitSendReceiveQuotaMB property, the status is set to ‘Send Receive Disabled’.
- if ($mailbox.MailboxSizeMB -ge $mailbox.ProhibitSendQuotaMB -and $mailbox.MailboxSizeMB -lt $mailbox.ProhibitSendReceiveQuotaMB) { … }: If the mailbox size is greater than or equal to the ProhibitSendQuotaMB property and less than the ProhibitSendReceiveQuotaMB property, the status is set to ‘Send Disabled’.
- if ($mailbox.MailboxSizeMB -ge $mailbox.IssueWarningQuotaMB -and $mailbox.MailboxSizeMB -lt $mailbox.ProhibitSendQuotaMB) { … }: If the mailbox size is greater than or equal to the IssueWarningQuotaMB property and less than the ProhibitSendQuotaMB property, the status is set to ‘Warning’.
- if ($mailbox.MailboxSizeMB -lt $mailbox.IssueWarningQuotaMB) { … }: If the mailbox size is less than the IssueWarningQuotaMB property, the status is set to ‘Normal’.
- The closing curly brace } marks the end of the foreach loop.
Enough talking, and let’s run the code.
Now, display the result ordered by percentage.
$allMailbox | Sort-Object PercentUsed -Descending | Format-Table
Now you can spot the mailbox size and status.
Step 4: Generate the Office 365 Mailbox Size Report
Now that you have a script to generate a mailbox size report, you can export results to different formats depending on your requirement.
PowerShell supports the conversion of objects to CSV, JSON, XML, and HTML. Here are examples of exporting the report in different file formats.
Export to CSV
# Export to CSV file $allMailbox | Sort-Object PercentUsed -Descending | Export-Csv -Path mailboxReport.csv -NoTypeInformation
Export to XML
# Export to raw XML
($allMailbox | Sort-Object PercentUsed -Descending | ConvertTo-Xml).Save("mailboxReport.xml")
Export to JSON
# Export to JSON file $allMailbox | Sort-Object PercentUsed -Descending | ConvertTo-Json | Out-File mailboxReport.json
Export to HTML
# Export to HTML $allMailbox | ConvertTo-Html | Out-File .\mailboxReport.html
Export to HTML with Custom Formatting
No one likes an ugly HTML report, so let’s spruce it up with some CSS magic. This example uses basic CSS formatting. Run the below code in PowerShell to export the Office 365 Mailbox Size Report with custom formatting.
$css = @'
<title>Office 365 Mailbox Size Report</title>
<style>
table {
font-family: "Gill Sans Extrabold", sans-serif;
font-size: 12px;
width: 100%;
background-color: #FFFFFF;
border-collapse: collapse;
border-width: 2px;
border-color: #7ea8f8;
border-style: solid;
color: #000000;
}
table td, table th {
border-width: 2px;
border-color: #7ea8f8;
border-style: solid;
padding: 5px;
}
table th {
background-color: #7ea8f8;
}
</style>
'@
$allMailbox | Sort-Object PercentUsed -Descending |
ConvertTo-Html `
-Head $css `
-Property `
@{n = 'User'; e = { $_.DisplayName } },
@{n = 'Email'; e = { $_.PrimarySmtpAddress } },
@{n = 'Mailbox Size (MB)'; e = { $_.MailboxSizeMB } },
@{n = 'Used Quota'; e = { "$($_.PercentUsed) %" } },
Status |
Out-File .\mailboxReport.html
And the result is now aesthetically better than the previous one.
Conclusion
Using PowerShell to generate an Office 365 mailbox size report can provide valuable insights into mailbox usage and help administrators manage their organization’s email storage effectively. Following the step-by-step guide outlined in this blog post, you can easily retrieve mailbox size information for individual users or all mailboxes within your Office 365 environment.
First, we discussed the prerequisites for running PowerShell commands, including installing the required modules and connecting to your Office 365 account. This initial setup is essential to ensure a smooth and successful mailbox size report generation process.
Next, we explored the various PowerShell commands and techniques to retrieve mailbox size data. We examined the Get-MailboxStatistics cmdlet, which allows us to gather information such as total size, item count, and storage limits. Additionally, we explored different parameters and filters that can be used to customize the report based on specific requirements.
Furthermore, we discussed how to format the generated report for better readability and functionality. By leveraging PowerShell’s capabilities, we can export the data into various formats, including CSV, HTML, and Excel, making it easier to analyze and share the report with relevant stakeholders.
Generating an Office 365 mailbox size report using PowerShell empowers administrators to make informed decisions and take necessary actions to ensure efficient email management. Following the steps outlined in this blog post, you can streamline the process and gain valuable insights into mailbox sizes within your Office 365 environment. Stay organized, manage storage effectively, and optimize your email infrastructure with the power of PowerShell and mailbox size reports.
