Practical Graph: Create a Mailbox Contents Report

List Mailbox Items to Know What’s There

In August, I wrote about how to write a PowerShell script to clean-up mailboxes. I wrote the script for my own purposes to demonstrate how powerful it can be when you mix cmdlets from the Exchange Online management module and Microsoft Graph API queries. However, not everyone is looking for a script to remove a bunch of messages, especially one that can process every mailbox in an organization. Some are wary about any code (including the Search-Mailbox cmdlet) where the potential exists for unexpected results if an incorrect query is used.

I needed another way to demonstrate the power of the Exchange/Graph combination and ended up writing a script to report mailbox contents. After all, everyone likes a report and most people begin their PowerShell coding life by writing a script to generate a report, like all the user accounts in Active Directory, all the mailboxes in Exchange, or sites in SharePoint. So let’s talk about how to write a script to report mailbox contents.

Why is the Graph Needed to Report Mailbox Contents?

The Exchange Online PowerShell module contains hundreds of cmdlets to allow developers to automate administrative operations. You can list mailboxes in a tenant and report all the folders, including their sizes and the number of items in each folder. You can even report the hidden folders in a mailbox, such as those used by apps like Teams and Yammer to store compliance records. But you can’t report anything about the items in the folders. That’s because Microsoft designed the Exchange Online module for administrative operations and not to interact with mailbox contents.

Exchange Web Services (EWS) has long been the designated API for mailbox interaction. Clients, like Outlook for Mac, use EWS to access Exchange and list, read, send messages, create folders, and so on. EWS is a powerful API, but it takes time to master, and Microsoft has made it quite clear that the Microsoft Graph API is the way forward.

Steps in the Mailbox Contents Script

Being practical and pragmatic, I started off by repurposing some of the code from the mailbox cleanup script. There’s no point in recreating a wheel when some perfectly good code is available. I then created code to do the following.

Ask for a mailbox to process. Because of the size of some mailboxes, I didn’t think it a good idea to create a report of items from all mailboxes. The script asks for a name to process and checks that name to make sure that it is a valid mailbox. You can use a mailbox alias, display name, user principal name, or primary SMTP address to identify the mailbox.

• Creates a list of all the folders in the mailbox (including sub-folders). Several utility folders (like Sync Issues and Conversation History) exist in every mailbox. The script removes these folders from the list of folders to process. An array defines the set of folders to ignore. Even though it usually holds many items, I choose to ignore the Deleted Items folder too. You can update the array to determine the folders to include or exclude.
• For test purposes, I applied a filter to fetch mailbox items that are more than a year old. Remove the filter if you want to fetch all items from the folder.
• Because some folders hold thousands of items, this is usually the longest part of the script. For example, depending on the service load, a folder holding 10,000 items can take up to a minute to process.
• The Graph returns message recipient information in a set of three arrays (for To, Cc, and Bcc recipients). Some manipulation is necessary to extract recipients from the arrays to make them easier to report.
• By default, the Graph messages API does not return the size of an item. The item size available in the Graph API refers to a single value extended property, which must be explicitly included in the set of properties to fetch. The code to fetch the extended property is slightly complicated because you must refer to the property using its MAPI identifier with URL encoding.

The script uses a registered Entra ID app that’s assigned the Mail.Read.All permission. This permission allows the app to read messages from any mailbox in the tenant. It’s one of those powerfull app permissions that great care should be taken with.

After extracting all data, I use the ImportExport PowerShell module to output the results in an Excel workbook (Figure 1).

You can download the complete script from GitHub.

Using the Mailbox Contents Report

My personal purpose for the report is to allow me to review the contents of folders and select items to delete. The way I approach this is to use the items in the workbook as targets to delete. Everything I want to keep, I remove, and the items left behind become the input to another script to delete the items from the mailbox. At least, that’s the theory. I haven’t written the second script yet, but I don’t anticipate it will be difficult.

The steps are:

• Read the items from the workbook.
• Use the identifier captured for each message to delete the message (probably using the code from the script to clean-up a mailbox).
• Iterate until the removal of the final item.

Of course, some more code might be needed to round out the script, but one of the delights of PowerShell is that you can automate operations in a relatively small amount of code. At least, that’s what I keep on telling myself.