A Question Provokes Some New Thoughts About How Best to Report Exchange Mailbox Statistics

The roots of Practical365.com lie in ExchangeServerPro.com, a site dedicated to all aspects of Exchange Server management. This means that the site still supports hundreds of posts written in the 2010-2015 period. Questions still arise for the topics covered by those posts, and we do our best to answer them, even if the questions cover issues for long-obsolete servers.

A recent question about an article covering reporting folder statistics for Exchange mailboxes asked how to find the folder with an item with the oldest received date for mailboxes over 50 GB. The PowerShell code written by the questioner is a good example of how complex a set of piped commands can become. Piped one-line commands can be a great way to get things done, but they are also difficult to read and hard to maintain, a point made by Michel de Rooij in his Practical PowerShell column. In any case, I tried to help with a version of the code that worked for me. At least, it works for Exchange Online.

This experience got me thinking about the many scripts written to report Exchange mailbox statistics. This is one of the classic Exchange PowerShell scripts and most Exchange administrators probably have their own version. My last run at the topic used Graph mailbox usage data instead of Exchange cmdlets to gain some speed. Could the world do with yet another version? Well, maybe so.

A New Take on a Classic Script

My new take on this classic script is available from GitHub. The script isn’t fast because it uses the Get-ExoMailboxStatistics and Get-ExoMailboxFolderStatistics cmdlets, neither of which is famed for alacrity. Running the Get-ExoMailbox cmdlet to fetch large quantities of mailboxes to process also takes time. In larger organizations, it would be best to apply a filter with the Get-ExoMailbox cmdlet to find different sets of mailboxes, such as all the mailboxes in a department or office.

The basic outline of the script is:

  • Run Get-ExoMailbox to find the mailboxes to process.
  • For each mailbox, run Get-ExoMailboxStatistics to fetch mailbox statistics. If the mailbox has an archive, the script runs Get-ExoMailboxStatistics to fetch statistics for the archive mailbox.
  • Run Get-ExoMailboxFolderStatistics to fetch details of the mailbox folder. As before, if the mailbox is archive-enabled, the script runs the cmdlet again to fetch details of the folders in the archive mailbox. The commands include a filter to avoid processing folders used for different background operations that are present in mailboxes but not usually accessed by users, like Calendar Logging, Conflicts, PersonMetaData, and Audits.
  • The script captures details of each folder including the oldest and newest items in the folder.
  • The script also captures overall statistics for both the primary and archive mailboxes.
  • Because the Get-ExoMailbox cmdlet does not return details like the user’s city, department, office, or title, the script runs the Get-User cmdlet to fetch this information. Running Get-User adds to the time required to process each mailbox, so you should comment this line out of the script if you don’t want to use this information. Overall, I think the information fetched by Get-User is helpful and that’s why it is in the script.
  • After processing all mailboxes, the script generates an HTML report. Figure 1 shows a typical example of the data reported for a mailbox. The script also generates a CSV file containing details of the mailbox statistics.
 Detail of mailbox statistics for an individual mailbox.

Reporting Exchange mailbox statistics
Figure 1: Detail of mailbox statistics for an individual mailbox

As you can see, the report lists:

  • Overall mailbox statistics.
  • Folders in the primary mailbox.
  • Recoverable Items folders for the primary mailbox (sometimes it’s good to know if these folders are approaching their quota limit).
  • If the mailbox is archive-enabled, the report lists the folders and Recoverable Items folders in the archive mailbox.

Other Ways to Use the Report Data

The data recorded in the two PowerShell lists captured by the script can be sliced and diced to generate other reports. For instance, to list mailboxes in size order, run this command (output shown in Figure 2):

$MbxReport | Sort-Object {$_.'Primary mailbox size bytes' -as [float]} -Descending | Format-Table Mailbox, 'Primary Mailbox Size GB', 'Primary Mailbox total items', 'Archive mailbox size GB' -AutoSize
Slicing and dicing the mailbox statistics data.
Figure 2: Slicing and dicing the mailbox statistics data

Because the $MbxReport list includes details for users like city, department, and office, you can sort and interrogate the date based on these properties. For example:

$MbxReport | Where-Object City -match 'Dublin' | Format-Table Mailbox, 'Primary Mailbox Size GB', 'Primary Mailbox total items', 'Archive mailbox size GB' -AutoSize

The script only reports user mailboxes. Some might like to include shared mailboxes. If so, you can amend the Get-ExoMailbox command to:

[array]$Mailboxes = Get-ExoMailbox -RecipientTypeDetails UserMailbox, SharedMailbox -ResultSize Unlimited `
    -PropertySets Archive -Properties DisplayName, RecipientTypeDetails | Sort-Object DisplayName

The script captures the mailbox type in the $MbxReport list, so if you do add shared mailboxes, it is to filter user and shared mailboxes and report each type separately. For example, this command finds all the user mailboxes in the report:

$MbxReport | Where-Object 'Mailbox type' -match 'Usermailbox'

I haven’t tested the code on an on-premises Exchange Server, but I think it will work if you replace Get-ExoMailbox with Get-Mailbox, Get-ExoMailboxStatistics with Get-MailboxStatistics, and Get-ExoMailboxFolderStatistics with Get-MailboxFolderStatistics. However, I can’t give a guarantee because I don’t have an on-premises server to run the script on.

Perhaps This Script Will Become a Classic

As always, this script illustrates principles and could be improved in many ways, notably through better error handling. I guess I’m waiting for Michel de Rooij to describe the best ways to deal with errors in a future Practical PowerShell column. In the meantime, I had some fun thinking about how to approach a script that so many people have written since 2006. This script might never be a classic, but I think it has some new elements that are of value. But I guess I’d say that after writing 240-odd lines of PowerShell.

About the Author

Tony Redmond

Tony Redmond has written thousands of articles about Microsoft technology since 1996. He is the lead author for the Office 365 for IT Pros eBook, the only book covering Office 365 that is updated monthly to keep pace with change in the cloud. Apart from contributing to Practical365.com, Tony also writes at Office365itpros.com to support the development of the eBook. He has been a Microsoft MVP since 2004.

Comments

  1. Jeremy

    Mine is throwing the same error as John, in older and newer Powershell versions. $Mbx does show as the last mailbox discovered. In my scenario, there’s only 31 and it throws the error on every mailbox it discovers. The .csv output is empty at the end of the run.

    1. Avatar photo
      Tony Redmond

      And same questions that I put to John:

      Did the script find any mailboxes to process?
      What’s the value of the $Mbx variable?

      This is PowerShell, so you’ve got to be prepared to do some debugging if things don’t work as well as expected in your tenant.

      Are you running the latest version of the Exchange Online management module (3.5)?

  2. Ricardo

    Hi Tony
    Thanks for the amazing script. It works like a charm 🙂
    Is it also possible to run this script without having to login with my user account. I know that when you use MsGraph you can use a ClientSecret from a registered Application. But can you also do that with the Connect-Exchangeonline Command?

  3. John

    Hi – ran into an issue your script, hoping you can help. here is what I get when I run it. thoughts?

    You cannot call a method on a null-valued expression.
    At C:\temp\Report-ExoMailboxFolderStats.PS1:138 char:5
    + $MbxReportLine = [PSCustomObject][Ordered]@{
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

    1. Avatar photo
      Tony Redmond

      Did the script find any mailboxes to process?
      What’s the value of the $Mbx variable?

      I’m afraid that I can’t see the data from your tenant so you will have to do some debugging.

Leave a Reply