This content is part of the Essential Guide: The essential admin's guide to Office 365 PowerShell

Produce mailbox reports with Exchange Online PowerShell scripts

Administrators who are familiar with PowerShell can use their skills to extract user mailbox information for Exchange Online reports.

Administrators need to know how resources are being used in their Exchange Server or Exchange Online subscription....

A number of cmdlets in Exchange Online PowerShell give admins the ability to gather information on the resources that users are consuming. That information is then exported and used in Excel to build a report detailing data usage trends.

Connecting to Exchange Online PowerShell

Administrators using Exchange Online can connect to Office 365 online reporting through either the Exchange Online PowerShell instructions page or the following script:

$UserCredential = Get-Credential

$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri -Credential $UserCredential -Authentication Basic -AllowRedirection

Import-PSSession $session

Exchange Online Reports

Microsoft created a set of prebuilt cmdlets that enable admins to build Exchange Online reports. The commands are run in an Exchange Online remote session. In Figure 1, the Get-MailboxUsageReport cmdlet shows basic size information about the mailboxes in your organization. 

Exchange Online mailbox usage report
Figure 1. The Get-MailboxUsageReport cmdlet creates a basic report on Exchange Online mailboxes.

The cmdlet displays a summary-level report of the metrics in each named column for the tenants. Each row is a snapshot of those metrics for a single tenant on a single date. Because this is PowerShell, this report and its columns of data are just one way to visualize a series of objects.

In Figure 2, the Get-MailboxUsageReport | Get-Member code provides more details about the type of information we can collect and shape.

Exchange object type properties
Figure 2. The code Get-MailboxUsageReport | Get-Member shows the type of data an Exchange administrator can gather.

In this output, each column is the name of one of the properties of the object type; each line in the report is an instance of that type. Because this shows that we are dealing with a collection of objects, we can use PowerShell to do sorting, grouping or display formatting. For example, the following code will export a comma-separated file to build a trend chart in Excel:

Get-MailboxUsageReport | Select-Object Date, TotalMailboxCount, MailboxesOverWarningSize | Export-CSV -Path c:\Mailboxusagereport.csv -NoTypeInformation

Because most organizations have a single tenant, this report is not that useful. For a more comprehensive report that will appeal to most administrators, run Get-MailUsageDetailReport to generate the output in Figure 3.

Get-MailUsageDetailReport cmdlet
Figure 3. The Get-MailUsageDetailReport cmdlet generates more details about user mailboxes.

Each row in Figure 3 is a snapshot of a single user's disk use in bytes on a given date. These reporting cmdlets enable admins to play with the data available through Exchange reporting. Admins can also automate reporting via PowerShell or the reporting web service.

To clean up this output and make it more relevant, the following code narrows the output to a list of the top 10 users for the latest report date:

Get-MailboxUsageDetailReport -StartDate (Get-Date).AddMonths(-1) | Group-Object Date | Select-object -First 1 | Select-Object -ExpandProperty group | Sort-Object CurrentMailboxSize,PercentUsed -Descending | Select-object Date,Username,CurrentMailboxSize,PercentUsed -first 10

This script gives a report of the top mailbox size users. The following code builds on that script and has it gather all the data needed to track the top 10 end users for the past month:

Get-MailboxUsageDetailReport | Group-Object Date | Select-object -First 1 | Select-Object -ExpandProperty group | Sort-Object CurrentMailboxSize,PercentUsed -Descending | Select-object -first 10 | Foreach-Object{Get-MailboxusageDetailReport -StartDate (Get-Date).AddMonths(-1) | Where-Object username -eq $_.username} | Export-CSV -path C:\Mailboxusagereport.csv -NoTypeInformation

When you open the .csv file in Excel, you can make line charts that show trend lines of the top end users. The chart details how fast each user's mailbox is growing, when they crossed certain thresholds or predict when they might pass size thresholds. 

Excel trend chart
Figure 4. Data exported from PowerShell can be imported into Excel to build trend charts.

In Figure 4, we see that John is on pace to increase his disk usage faster than Jane. Notice the trend lines extend beyond the data lines to predict what the space usage might be if current patterns continue.

Now that we have pinpointed which end users take up the most disk space, we can find out which users have the highest and lowest number of items in their mailboxes:

Get-MailboxUsageDetailReport -StartDate (Get-Date).AddMonths(-1) | Group-Object Date | Select-object -First 1 | Select-Object -ExpandProperty group | Sort-Object CurrentMailboxSize -Descending | Select-object -first 10 -ExpandProperty Username | Get-MailboxStatistics | Sort-Object ItemCount

This script shows which users are on the top 10 list for using disk space and sorts that group from fewest items in the mailbox to most. Users taking up the most disk space with the fewest items might have lots of attachments they can delete to free up disk space. You can also sort to determine which end users are taking up space with several items, which means they are probably holding onto numerous messages that can be archived or deleted.

This script gives a snapshot view of usage and pipes that information into a cmdlet that shows details on the current state of those users' email accounts. This output comes courtesy of the -expandProperty parameter. If we pipe the output of Get-MailboxUsageDetailReport into Get-MailboxStatistics, we get an error that the cmdlet doesn't understand the pipeline input.

Expand the username property to see the base system.string object and pipe that into Get-MailboxStatistics instead of the larger parent object. The Get-MailboxStatistics cmdlet knows what to do with a plain collection of user names and it generates the desired data.

This tip just scratches the surface of what is possible with Exchange Online PowerShell reporting. The real value comes when these cmdlets are combined with an automation tool, a dashboard reporting tool or a reporting web service that is combined with Power BI or a custom application. 

Many of the reporting functions and cmdlets available for on-premises Exchange Server also work with Exchange Online. A full list of Exchange Online reports is available here; useful Office 365 reporting cmdlets can be found here.

Next Steps

A guide for Exchange administrators working with PowerShell

The importance of learning PowerShell for managing Exchange

Learn the ins and outs of PowerShell for Power BI

The 10 most helpful cmdlets for Exchange administration

Dig Deeper on Exchange Online administration and implementation