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.

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.

Teams and Compliance Records

When people use Teams to communicate, Office 365 captures compliance records in Exchange Online mailboxes. Records for conversations in channels are captured in the group mailbox belonging to the team while records for personal and group chats are in the mailboxes of all chat participants. Teams supports the capture of compliance records for on-premises users in hybrid organizations and for guest accounts using special “phantom” mailboxes. Generally, the scheme works well and the compliance records are available for eDiscovery.

That is, unless your account has a frontline (Office 365 F1) license. In this case, your mailbox quota is 2 GB, which seems enough if it’s only going to be used for an occasional email. But Teams compliance records are charged against the mailbox quota and if the user is involved in many personal or group chats, their quota might be substantially eroded, especially if chat participants share graphics. One of our readers reported that they’ve seen instances where a quarter of the mailbox quota was absorbed by Teams compliance records.

Team Chat Folder

Teams compliance records are stored in the hidden Team Chat sub-folder under Conversation History. Because Team Chat is a system folder that isn’t available to users, you’d assume that its contents would not count against mailbox quota. And for most Office 365 accounts the fact that Team Chat is charged against mailbox quota isn’t an issue because of the standard 100 GB quota.

But the situation is different when a mailbox has a 2 GB quota. I say this in the knowledge that 2 GB is still a big amount. In 1996, original Exchange 4.0 mailboxes enjoyed quotas of between 20 MB and 50 MB. But things are different now. Messages are much larger, we keep everything, and Exchange Online stuffs all manner of data into user mailboxes. In this case, the net result is that a frontline user could find themselves running out of mailbox quota because of compliance records, which doesn’t seem like a good thing.

What You Can Do

Unless Microsoft changes the way they measure usage against mailbox quota, the only thing you can do is to periodically check the consumption of frontline mailboxes to ensure that none exhausts their quota. As usual, PowerShell comes in handy. Here’s a code snippet to process all user mailboxes and report the user, total items in mailbox, total size of mailbox, and the amount taken up by Teams compliance items.

# Find out what Teams compliance record are in user mailboxes
$Mbx = Get-Mailbox -RecipientTypeDetails UserMailbox -ResultSize Unlimited | Select Alias, DisplayName, UserPrincipalname
$Report = @()
ForEach ($M in $Mbx) {
 # Retrieve message stats
   Write-Host "Processing" $M.DisplayName
   $MbxStatus = Get-MailboxStatistics -Identity $M.Alias | Select ItemCount, TotalItemSize
   $ConvHistory = Get-MailboxFolderStatistics -Identity $M.Alias -FolderScope ConversationHistory
   $ReportLine = [PSCustomObject]@{
           User          = $M.DisplayName
           UPN           = $M.UserPrincipalName
           MbxSize       = $MbxStatus.TotalItemSize
           MbxItems      = $MbxStatus.ItemCount
           TeamsItems    = $ConvHistory[1].ItemsInFolder
           TeamsSize     = $ConvHistory[1].FolderSize }
   $Report += $ReportLine }
$Report | Sort MbxItems -Desc | Export-CSV -NoTypeInformation c:\Temp\TeamsComplianceItems.csv

The output is a CSV file (Figure 1), as it’s usually easier to analyze this information in Excel.

To improve the script, you could:

• Only process mailboxes that have frontline licenses.
• Convert the mailbox size data returned by PowerShell into numeric values (to make it sortable).
• Add extra mailbox properties to the output.
• Allocate a higher license to any mailbox approaching their quota.
• Go wild with your imagination.

Of course, you could also deploy a Teams retention policy for frontline workers to remove items after a shorter period.

In the meantime, we’ve reported the issue to Microsoft and they are looking into the matter.

For more information about managing Teams, including Teams compliance records, read the Office 365 for IT Pros eBook.