Exploit Graph Usage Data Instead of PowerShell Cmdlets

The first report generated by Exchange administrators as they learn PowerShell is often a list of mailboxes. The second is usually a list of mailboxes and their sizes. A modern version of the code used to generate such a report is shown below.

Get-ExoMailbox -RecipientTypeDetails UserMailbox -ResultSize Unlimited | Sort-Object DisplayName | Get-ExoMailboxStatistics | Format-Table DisplayName, ItemCount, TotalItemSize -AutoSize

I call the code “modern” because it used the REST-based cmdlets introduced in 2019. Many examples persist across the internet that use the older Get-Mailbox and Get-MailboxStatistics cmdlets.

Instead of piping the results of Get-ExoMailbox to Get-ExoMailboxStatistics, a variation creates an array of mailboxes and loops through the array to generate statistics for each mailbox.

[array]$Mbx = Get-ExoMailbox -RecipientTypeDetails UserMailbox -ResultSize Unlimited
Write-Host ("Processing {0} mailboxes..." -f $Mbx.count)

$OutputReport = [System.Collections.Generic.List[Object]]::new()

ForEach ($M in $Mbx) {
  $MbxStats = Get-ExoMailboxStatistics -Identity $M.ExternalDirectoryObjectId -Properties LastUserActionTime
  $DaysSinceActivity = (New-TimeSpan $MbxStats.LastUserActionTime).Days
  $ReportLine = [PSCustomObject]@{
    UPN               = $M.UserPrincipalName
    Name              = $M.DisplayName
    Items             = $MbxStats.ItemCount
    Size              = $MbxStats.TotalItemSize.Value.toString().Split("(")[0]
    LastActivity      = $MbxStats.LastUserActionTime
    DaysSinceActivity = $DaysSinceActivity
   } 
   $OutputReport.Add($ReportLine)
 }
$OutputReport | Format-Table Name, UPN, Items, Size, LastActivity

In both cases, the Get-ExoMailboxStatistics cmdlet fetches information about the number of items in a mailbox, their size, and the last recorded user interaction. There’s nothing wrong with this approach. It works (as it has since 2007) and generates the requested information. The only downside is that it’s slow to run Get-ExoMailboxStatistics for each mailbox. You won’t notice the problem in small tenants where a script only needs to process a couple of hundred mailboxes, but the performance penalty mounts as the number of mailboxes increases.

Graph Usage Data and Microsoft 365 Admin Center Reports

Microsoft 365 administrators are probably familiar with the Reports section of the Microsoft 365 admin center. A set of usage reports are available to help organizations understand how active their users are in different workloads, including email (Figure 1).

The basis of the usage reports is the Graph Reports API, including the email activity reports and mailbox usage reports through Graph API requests and Microsoft Graph PowerShell SDK cmdlets. Here are examples of fetching email activity and mailbox usage data with the SDK cmdlets. The specified period is 180 days, which is the maximum:

Get-MgReportEmailActivityUserDetail -Period 'D180' -Outfile EmailActivity.CSV
[array]$EmailActivityData = Import-CSV EmailActivity.CSV
Get-MgReportMailboxUsageDetail -Period 'D180' -Outfile MailboxUsage.CSV
[array]$MailboxUsage = Import-CSV MailboxUsage.CSV

I cover how to use Graph API requests in the Microsoft 365 user activity report. This is a script that builds up a composite picture of user activity across different workloads, including Exchange Online, SharePoint Online, OneDrive for Business, and Teams. One difference between the Graph API requests and the SDK cmdlets is that the cmdlets download data to a CSV file that must then be imported into an array before it can be used. The raw API requests can fetch data and populate an array in a single call. It’s just another of the little foibles of the Graph SDK.

The combination of email activity and mailbox usage allows us to replace calls to Get-ExoMailboxStatistics (or Get-MailboxStatistics, if you insist on using the older cmdlet). The basic idea is that the script fetches the usage data (as above) and references the arrays that hold the data to fetch the information about item count, mailbox size, etc.

You can download a full script demonstrating how to use the Graph usage data for mailbox statistics from GitHub.

User Data Obfuscation

To preserve user privacy, organizations can choose to obfuscate the data returned by the Graph and replace user-identifiable data with MD5 hashes. We obviously need non-obfuscated user data, so the script checks if the privacy setting is in force. If this is true, the script switches the setting to allow the retrieval of user data for the report.

$ObfuscatedReset = $False
If ((Get-MgBetaAdminReportSetting).DisplayConcealedNames -eq $True) {
    $Parameters = @{ displayConcealedNames = $False }
    Update-MgBetaAdminReportSetting -BodyParameter $Parameters
    $ObfuscatedReset = $True
}

At the end of the script, the setting is switched back to privacy mode.

Faster but Slightly Outdated

My tests (based on the Measure-Command cmdlet) indicate that it’s much faster to retrieve and use the email usage data instead of running Get-ExoMailboxStatistics. At times, it was four times faster to process a set of mailboxes. Your mileage might vary, but I suspect that replacing cmdlets that need to interact with mailboxes with lookups against arrays will always be faster. Unfortunately the technique is not available for Exchange Server because the Graph doesn’t store usage data for on-premises servers.

One downside is that the Graph usage data is always at least two days behind the current time. However, I don’t think that this will make much practical difference because it’s unlikely that there will be much variation in mailbox size over a couple of days.

The point is that old techniques developed to answer questions in the earliest days of PowerShell might not necessarily still be the best way to do something. New sources of information and different ways of accessing and using that data might deliver a better and faster outcome. Always stay curious!

Insight like this doesn’t come easily. You’ve got to know the technology and understand how to look behind the scenes. Benefit from the knowledge and experience of the Office 365 for IT Pros team by subscribing to the best eBook covering Office 365 and the wider Microsoft 365 ecosystem.

Sixty Times Larger Than in 1996

Browsing the pages of my 1996 book on Microsoft Exchange Server 4.0, I discovered that the average size of an email 25 years ago was in the 2-4 KB range. This got me thinking about what the current average might be, so I checked my Inbox by running the Get-ExoMailboxFolderStatistics cmdlet to retrieve the total size of the folder and the number of items in it. The code I used is as follows:

$CheckName = Read-Host "What User Mailbox Shall I Check?"
If (Get-ExoMailbox -Identity $CheckName) {
   $Stats = $Null
   Write-Host "Checking Mailbox for" $CheckName...
   $Stats = Get-EXOMailboxFolderStatistics -Identity $CheckName -FolderScope Inbox | Select Name, ItemsInFolder, FolderSize ,@{Name="FolderSizeBytes"; expression={((($_.FolderSize -replace '^(.*\()(.*)(\sbytes\))$','$2').Replace(',','')) -as [int])}} 

    Write-Host ("Results for mailbox {0}: {1} Inbox items {2} folder size with an average message size of {3} KB" -f $CheckName, $Stats.ItemsInFolder, $Stats.FolderSize.Split("(")[0], [math]::round(($Stats.FolderSizeBytes/$Stats.ItemsInFolder)/1KB,2))}

The code looks complicated, but it’s not too bad. We fetch some of the properties returned by Get-ExoMailboxFolderStatistics and put them into a variable. One of the properties is calculated to return the total number of bytes in the folder as a integer variable. We then calculate the average size of a message and write out what we found.

In any case, the result is that the average size of messages in my Inbox is sixty times larger than they were in 1996. Other mailboxes will likely show different results (run the code against your Inbox to see what it reports) as it all depends on the volume of traffic and the type of messages you receive. However, I think everyone can agree that the size of messages sent and received today are larger than they were in the past.

Reasons for Swelling Messages

The reasons why today’s messages are much larger vary across organizations. However, I think four core reasons exist:

• More Headers: Today’s SMTP traffic include more message headers than ever before.
• Rich Editors: Old text-only messages are now jazzy HTML rich-text messages.
• Fat Attachments: Capable networks make users think nothing about sending a 25 MB PowerPoint presentation in email. They wouldn’t do this if connected on a dial-up telephone link.
• User Habits: We’re lazy and reply-all to messages which include all previous replies.

SMTP messages used to include just enough headers to get the email through. Now, a bunch of new headers exist, some of which are essential to authenticate that email comes from the right source and isn’t spam (DMARC, ARC, SPF, etc.). Other x-headers are added by servers like Exchange to track the progress of messages, include anti-spam reports, and measure latency. Fire up Outlook’s Message Header Analyzer add-in to examine the headers in a message to see what I mean.

Rich Format Email

Rich editors mean that text and formatting instructions must be sent where plain text was the norm. Embedded graphics drive up message sizes, including the autosignatures inserted by users and organizations. Complex autosignatures with corporate logos and social networking links add even more to message sizes, especially when organizations lose control and insist that every message goes out complete with graphic-intense marketing messages.

It’s lucky for everyone that servers are so fast and networks so capable that they can deal with the processing overhead required to transport messages in and out of Exchange Online and between Microsoft’s datacenters and email servers elsewhere across the internet. I’m not sure that Exchange’s original IMC (Internet Mail Connector) would have been up to the task.

The Size and Intelligence of Notification Email

Even relatively simple notification messages generated by applications can be huge. For instance, a brief review of notifications from different Microsoft 365 apps telling me that people are trying to contact me resulted in the following sizes:

• LinkedIn: 174 KB
• Teams: 265 KB,
• Yammer: 472 KB (Figure 1).

To be fair to Yammer, the latest generation of notification email are graphically rich and interactive to allow recipients to respond within the email client. The code necessary to enable this functionality increases the amount of HTML carried around in messages. It’s a price we must pay for intelligent messages.

Cloudy Attachments and Bad Habits

Microsoft’s efforts to convince users of the goodness of sending “cloudy attachments” (links to files in OneDrive for Business and SharePoint Online) are showing signs of progress, especially within Office 365. Consistent sharing links, the ability to co-author (now even when documents are protected with sensitivity labels), and easy access to files stored in other tenants mean that users send an increasing number of links where files were once attached. However, the default option for many is still to attach files (or rather, a copy of a file), and once that happens, the average size of message swells quickly.

People are creatures of habit, which is one reason why we continue to send traditional attachments. We also tend to take the path of least resistance, which is why we reply to messages without thinking about the long trail of previous replies sent along with our response. The outcome is that a message at the end of a thread might be 1 MB, roughly 10% of which is useful non-repetitive information.

Large Cloud Mailboxes Required

The world of email is very different to what it was when Exchange 4.0 appeared in 1996. I doubt anyone today could cope with the 20 MB mailbox quota often assigned to users in those days. Given the growing size of messages, it’s a good thing that Microsoft makes generous mailbox quotas available for Exchange Online users. Even so, if the trend of ever-swelling messages continues, we’ll all need 200 GB mailboxes soon.

The Office 365 for IT Pros eBook hasn’t covered developments in Exchange mailbox technology since 1996. Some of our authors remember back that far, but mostly we live in the modern world and track new developments as they happen inside Office 365.

Use PowerShell to Create the Exchange Mailbox Quota Report

Updated 24 September 2023

One of the first PowerShell scripts created after the launch of Exchange 2007 was to report the quotas assigned to mailboxes and the amount of quota consumed by each mailbox. Scripts of this type are relatively simple and rely on the Get-ExoMailbox and Get-ExoMailboxStatistics cmdlets to provide data about quotas and usage.

Over time, many variations on this report have appeared. The variant shown here is a response to a request in a Facebook group about Office 365 for a script to identify mailboxes whose quota is nearly exhausted. In this case, the Office 365 tenant has many frontline accounts whose Exchange Online mailbox quota are 2 GB instead of the much more generous 100 GB assigned to enterprise accounts. It’s obviously much easier to fill a 2 GB quota, especially if you use Teams and share images in personal chats. The idea therefore is to scan for mailboxes whose usage exceeds a threshold of quota used (expressed as a percentage). Mailbox owners who fall into this category might need to remove some items (and empty the Deleted Items folder) or receive a larger quota.

Exchange Online Mailbox Numbers and PowerShell

Two things are notable. First, if you want to do comparisons with the information returned by the cmdlets, you should convert the returned values into numbers (byte count), which is what is done here. This is because the Get-ExoMailbox and Get-ExoMailboxStatistics cmdlets return values like:

Get-ExoMailboxStatistics -Identity Kim.Akers | Format-List TotalItemSize                                    

TotalItemSize : 3.853 GB (4,136,899,622 bytes)

It’s hard to do computations on these values, so some processing is needed to ensure that calculations proceed smoothly.

Dealing with the Exchange Mailbox Quota Report Output

Second, the output is a CSV file sorted by mailbox display name. You could use the output in different ways. For instance, you could use the incoming webhook connector to post information about users whose mailboxes need some attention to Teams or Microsoft 365 Groups (here’s an example).

Here’s the script. As always, no claims are made that this is perfect PowerShell code. It’s entirely up to the reader to improve, enhance, or debug the script to match their needs. You can download the script from GitHub.

# Set threshold % of quota to use as warning level
$Threshold = 85
# Get all user mailboxes
Clear-Host
Write-Host "Finding mailboxes..."
[array]$Mbx = Get-ExoMailbox -ResultSize Unlimited -RecipientTypeDetails UserMailbox -Properties ProhibitSendReceiveQuota | Select-Object DisplayName, ProhibitSendReceiveQuota, DistinguishedName
$Report = [System.Collections.Generic.List[Object]]::new()
ForEach ($M in $Mbx) {
    # Find current usage
    Write-Host "Processing" $M.DisplayName
    $Mailbox = $M.DisplayName
    $ErrorText = $Null
    $MbxStats = Get-ExoMailboxStatistics -Identity $M.DistinguishedName | Select ItemCount, TotalItemSize
    # Return byte count of quota used
    [INT64]$QuotaUsed = [convert]::ToInt64(((($MbxStats.TotalItemSize.ToString().split("(")[-1]).split(")")[0]).split(" ")[0]-replace '[,]',''))
    # Byte count for mailbox quota
    [INT64]$MbxQuota = [convert]::ToInt64(((($M.ProhibitSendReceiveQuota.ToString().split("(")[-1]).split(")")[0]).split(" ")[0]-replace '[,]',''))
    $MbxQuotaGB = [math]::Round(($MbxQuota/1GB),2)
    $QuotaPercentUsed = [math]::Round(($QuotaUsed/$MbxQuota)*100,2)
    $QuotaUsedGB = [math]::Round(($QuotaUsed/1GB),2)
    If ($QuotaPercentUsed -gt $Threshold) {
       Write-Host $M.DisplayName "current mailbox use is above threshold at" $QuotaPercentUsed -Foregroundcolor Red
       $ErrorText = "Mailbox quota over threshold" }
    # Generate report line for the mailbox
    $ReportLine = [PSCustomObject]@{ 
        Mailbox          = $M.DisplayName 
        MbxQuotaGB       = $MbxQuotaGB
        Items            = $MbxStats.ItemCount
        MbxSizeGB        = $QuotaUsedGB
        QuotaPercentUsed = $QuotaPercentUsed
        ErrorText        = $ErrorText} 
   $Report.Add($ReportLine)
} 
# Export to CSV
$Report | Sort-Object Mailbox | Export-csv -NoTypeInformation MailboxQuotaReport.csv

Figure 1 shows an example of the kind of report that the script generates.

Tailoring the Exchange Mailbox Quota Report

The script is reasonably simple PowerShell code so there shouldn’t be much difficulty in tailoring it to fit the needs of a specific organization. The nice thing about PowerShell is that it’s easy to customize and easy to stitch bits of code from different scripts together to create a new solution. Hopefully you’ll be able to use the code presented here for your purposes.

Need more information about how to manage Exchange Online mailboxes? Look no further than the Office 365 for IT Pros eBook, which is filled with practical ideas, suggestions, and lots of PowerShell examples.