Using the Graph API to Generate Mailbox Folder Statistics

Sometimes PowerShell Needs a Little Help

Retrieving information about mailbox folder statistics is a common administrative activity. Exchange Online administrators usually reach for the Get-ExoMailboxStatistics and Get-ExoMailboxFolderStatistics cmdlets and build scripts around their output. This approach works, but it has some downsides:

• The cmdlets are “heavy” – they take a long time to run.
• Some of the output (like folder sizes) need manipulation before they can be used in computations.

Running Get-ExoMailboxStatistics to process a batch of mailboxes fetched using Get-ExoMailbox is not a quick operation. However, it gets the job done in terms of fetching mailbox folder statistics and that’s why you see this approach taken in scripts so often.

The history of these cmdlets goes back to the original implementation of PowerShell in Exchange Server 2007. At the time, the Exchange developers made the brave decision to build all of the Exchange 2007 administrative functionality around PowerShell, including the Exchange Management Console (EMC). The design focus for the cmdlets was on the retrieval and manipulation of data required by the console. No consideration was given to being able to access more than raw data, such as the size of the mailbox or the size of an individual folder and the number of items it contained.

The Role of EWS and the Graph

At the time, Exchange Web Services (EWS) was the public API available to developers who needed to go deeper into mailbox contents. For example, if you want to return both the number of items in the Inbox together with the unread count (here’s a StackOverflow discussion on the topic)

Today, the Microsoft Graph is the preferred option, which is where I headed when a reader noted their frustration at not being able to report the unread count for mailboxes. One reason why you might want to report unread counts is to monitor activity for shared mailboxes used to process customer requests. In any case, some business reason exists, so let’s explore how to respond.

Using the Mail API to Fetch Mailbox Folders

The Microsoft Graph Mail API contains the List Mail Folders call to return a collection of folders from a user’s mailbox. Some properties of interest to calculate mailbox folder statistics are included for each folder. Here’s an example of the data returned for a folder. As you can see, this folder is the Inbox, and the Graph returns an unread count.

id               : AQMkADY5NmJhYzk4LTA2ZGYtNGZkMy05NDJlLThlNDA1ZTMxZTU1AGMALgAAA4-72A-LRNdArLgWql46nuMBAEulavbT-91Jpzh8YFiJOcwAAAIBDAAAAA==
displayName      : Inbox
parentFolderId   : AQMkADY5NmJhYzk4LTA2ZGYtNGZkMy05NDJlLThlNDA1ZTMxZTU1AGMALgAAA4-72A-LRNdArLgWql46nuMBAEulavbT-91Jpzh8YFiJOcwAAAIBCAAAAA==
childFolderCount : 0
unreadItemCount  : 1715
totalItemCount   : 2818
sizeInBytes      : 721593032
isHidden         : False

Equipped with this knowledge, it’s easy to create a PowerShell script to fetch mailbox folder statistics by:

• Use an Azure AD registered app and an app secret to acquire an access token to interact with the Graph. The registered app must have consent to use the application Mail.Read permission (to access user mailboxes).
• Run Get-ExoMailbox to return the set of user mailboxes in the tenant.
• Loop through each mailbox to fetch the set of mail folders. This call doesn’t return folders like Contacts, Calendar, Tasks, and so on.
• Extract information about the Inbox.
• Capture the information in a report.
• Output the report (as a CSV file, Excel file, or other format – I decided to use Excel as described in this post).

The main loop is shown below:

ForEach ($M in $Mbx) {
    Write-Host ("Processing mailbox {0} of {1}: {2}" -f $i, $Mbx.Count, $M.DisplayName); $i++
    $Uri = "https://graph.microsoft.com/v1.0/users/" + $M.ExternalDirectoryObjectId + "/mailFolders?`$top=250"    
    $FolderData = Invoke-RestMethod -Headers $Headers -Uri $Uri -UseBasicParsing -Method "GET" -ContentType "application/json"
    $InboxData = $FolderData.Value | ? {$_.displayname -eq "Inbox"}
    $TotalMbxItems = ($FolderData.Value.totalitemcount | Measure-Object -Sum | Select -ExpandProperty Sum)
    $TotalMbxSize = ($FolderData.Value.SizeInBytes | Measure-Object -Sum | Select -ExpandProperty Sum)
    $ReportLine = [PSCustomObject][Ordered]@{  # Write out details of the mailbox
       "User"              = $M.DisplayName
       UPN                 = $M.UserPrincipalName
       InboxCount          = $InboxData.totalItemCount
       UnreadCount         = $InboxData.unreadItemCount
       TotalMbxFolders     = $FolderData.Value.Count
       TotalMbxItems       = $TotalMbxItems
       TotalMbxFolderSize  = [math]::Round($TotalMbxsize/1Mb,2)  }
      $Report.Add($ReportLine) 
}

By default, a call to retrieve mailbox folders returns the first 10 folders matching the query. Microsoft Graph queries limit the amount of data they return to minimize demand on services. A process called pagination allows developers to fetch successive pages of data until they exhaust all available data. In this case, we can either instruct the Graph to return more than the default amount (done here by using the Top parameter to specify that the script will accept up to mail 250 folders), or use a nextlink to fetch the next page of data until a nextlink is no longer available.

Figure 1 shows the output generated from the mailbox folder data returned by the Graph.

Graph is No Panacea

Because it includes only mail folders, the total mailbox data reported by the Graph API is not the same as returned by the Get-ExoMailboxStatistics cmdlet. The difference is accounted for by folders like Calendar, Contacts, and Tasks.

The Graph is not a universal panacea for access to mailbox data. It’s a tool that adds to the capabilities available to tenant administrators. In this instance, the combination of PowerShell and the Graph allowed us to find the unread count for the Inbox folder in mailboxes. It’s nice to have an additional method to get at data.

You can download the script I used from GitHub.

---

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.