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.
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.
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.
Figure 1: Mailbox folder statistics retrieved by the Microsoft Graph shown in Excel
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.
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.
3 Replies to “Using the Graph API to Generate Mailbox Folder Statistics”
Looks like a really nice script. This will be a huge improvement on how we collect this data now. I am amazed that Microsoft does provide more information to end users regarding deleted item size. For companies who are using infinite retention polices as mailbox backups/audit protection, deleted item retention is a very important number to monitor. I hope this number can be extracted using this technique.
I’m encountering an error when I try to run this script, specifically on line 46, it returns {“error”:{“code”:”ErrorAccessDenied”,”message”:”Access is denied. Check credentials and try again.”}}.
I’ve created the app registration, verified the AppID, TenantID, and AppSecret are correct, and I have the Mail.Read Delegated Microsoft Graph and Office 365 Exchange Online permissions set, and granted admin consent. Any other thoughts?
I also notice when expanding the content of the $tokenRequest variable, and parsing it through jwt.ms, it doesn’t contain a roles claim? Should it?
{"id":null,"mode":"button","open_style":"in_modal","currency_code":"EUR","currency_symbol":"\u20ac","currency_type":"decimal","blank_flag_url":"https:\/\/office365itpros.com\/wp-content\/plugins\/tip-jar-wp\/\/assets\/images\/flags\/blank.gif","flag_sprite_url":"https:\/\/office365itpros.com\/wp-content\/plugins\/tip-jar-wp\/\/assets\/images\/flags\/flags.png","default_amount":100,"top_media_type":"featured_image","featured_image_url":"https:\/\/office365itpros.com\/wp-content\/uploads\/2022\/11\/cover-141x200.jpg","featured_embed":"","header_media":null,"file_download_attachment_data":null,"recurring_options_enabled":true,"recurring_options":{"never":{"selected":true,"after_output":"One time only"},"weekly":{"selected":false,"after_output":"Every week"},"monthly":{"selected":false,"after_output":"Every month"},"yearly":{"selected":false,"after_output":"Every year"}},"strings":{"current_user_email":"","current_user_name":"","link_text":"Virtual Tip Jar","complete_payment_button_error_text":"Check info and try again","payment_verb":"Pay","payment_request_label":"Office 365 for IT Pros","form_has_an_error":"Please check and fix the errors above","general_server_error":"Something isn't working right at the moment. Please try again.","form_title":"Office 365 for IT Pros","form_subtitle":null,"currency_search_text":"Country or Currency here","other_payment_option":"Other payment option","manage_payments_button_text":"Manage your payments","thank_you_message":"Thank you for supporting the work of Office 365 for IT Pros!","payment_confirmation_title":"Office 365 for IT Pros","receipt_title":"Your Receipt","print_receipt":"Print Receipt","email_receipt":"Email Receipt","email_receipt_sending":"Sending receipt...","email_receipt_success":"Email receipt successfully sent","email_receipt_failed":"Email receipt failed to send. Please try again.","receipt_payee":"Paid to","receipt_statement_descriptor":"This will show up on your statement as","receipt_date":"Date","receipt_transaction_id":"Transaction ID","receipt_transaction_amount":"Amount","refund_payer":"Refund from","login":"Log in to manage your payments","manage_payments":"Manage Payments","transactions_title":"Your Transactions","transaction_title":"Transaction Receipt","transaction_period":"Plan Period","arrangements_title":"Your Plans","arrangement_title":"Manage Plan","arrangement_details":"Plan Details","arrangement_id_title":"Plan ID","arrangement_payment_method_title":"Payment Method","arrangement_amount_title":"Plan Amount","arrangement_renewal_title":"Next renewal date","arrangement_action_cancel":"Cancel Plan","arrangement_action_cant_cancel":"Cancelling is currently not available.","arrangement_action_cancel_double":"Are you sure you'd like to cancel?","arrangement_cancelling":"Cancelling Plan...","arrangement_cancelled":"Plan Cancelled","arrangement_failed_to_cancel":"Failed to cancel plan","back_to_plans":"\u2190 Back to Plans","update_payment_method_verb":"Update","sca_auth_description":"Your have a pending renewal payment which requires authorization.","sca_auth_verb":"Authorize renewal payment","sca_authing_verb":"Authorizing payment","sca_authed_verb":"Payment successfully authorized!","sca_auth_failed":"Unable to authorize! Please try again.","login_button_text":"Log in","login_form_has_an_error":"Please check and fix the errors above","uppercase_search":"Search","lowercase_search":"search","uppercase_page":"Page","lowercase_page":"page","uppercase_items":"Items","lowercase_items":"items","uppercase_per":"Per","lowercase_per":"per","uppercase_of":"Of","lowercase_of":"of","back":"Back to plans","zip_code_placeholder":"Zip\/Postal Code","download_file_button_text":"Download File","input_field_instructions":{"tip_amount":{"placeholder_text":"How much would you like to tip?","initial":{"instruction_type":"normal","instruction_message":"How much would you like to tip? Choose any currency."},"empty":{"instruction_type":"error","instruction_message":"How much would you like to tip? Choose any currency."},"invalid_curency":{"instruction_type":"error","instruction_message":"Please choose a valid currency."}},"recurring":{"placeholder_text":"Recurring","initial":{"instruction_type":"normal","instruction_message":"How often would you like to give this?"},"success":{"instruction_type":"success","instruction_message":"How often would you like to give this?"},"empty":{"instruction_type":"error","instruction_message":"How often would you like to give this?"}},"name":{"placeholder_text":"Name on Credit Card","initial":{"instruction_type":"normal","instruction_message":"Enter the name on your card."},"success":{"instruction_type":"success","instruction_message":"Enter the name on your card."},"empty":{"instruction_type":"error","instruction_message":"Please enter the name on your card."}},"privacy_policy":{"terms_title":"Terms and conditions","terms_body":null,"terms_show_text":"View Terms","terms_hide_text":"Hide Terms","initial":{"instruction_type":"normal","instruction_message":"I agree to the terms."},"unchecked":{"instruction_type":"error","instruction_message":"Please agree to the terms."},"checked":{"instruction_type":"success","instruction_message":"I agree to the terms."}},"email":{"placeholder_text":"Your email address","initial":{"instruction_type":"normal","instruction_message":"Enter your email address"},"success":{"instruction_type":"success","instruction_message":"Enter your email address"},"blank":{"instruction_type":"error","instruction_message":"Enter your email address"},"not_an_email_address":{"instruction_type":"error","instruction_message":"Make sure you have entered a valid email address"}},"note_with_tip":{"placeholder_text":"Your note here...","initial":{"instruction_type":"normal","instruction_message":"Attach a note to your tip (optional)"},"empty":{"instruction_type":"normal","instruction_message":"Attach a note to your tip (optional)"},"not_empty_initial":{"instruction_type":"normal","instruction_message":"Attach a note to your tip (optional)"},"saving":{"instruction_type":"normal","instruction_message":"Saving note..."},"success":{"instruction_type":"success","instruction_message":"Note successfully saved!"},"error":{"instruction_type":"error","instruction_message":"Unable to save note note at this time. Please try again."}},"email_for_login_code":{"placeholder_text":"Your email address","initial":{"instruction_type":"normal","instruction_message":"Enter your email to log in."},"success":{"instruction_type":"success","instruction_message":"Enter your email to log in."},"blank":{"instruction_type":"error","instruction_message":"Enter your email to log in."},"empty":{"instruction_type":"error","instruction_message":"Enter your email to log in."}},"login_code":{"initial":{"instruction_type":"normal","instruction_message":"Check your email and enter the login code."},"success":{"instruction_type":"success","instruction_message":"Check your email and enter the login code."},"blank":{"instruction_type":"error","instruction_message":"Check your email and enter the login code."},"empty":{"instruction_type":"error","instruction_message":"Check your email and enter the login code."}},"stripe_all_in_one":{"initial":{"instruction_type":"normal","instruction_message":"Enter your credit card details here."},"empty":{"instruction_type":"error","instruction_message":"Enter your credit card details here."},"success":{"instruction_type":"normal","instruction_message":"Enter your credit card details here."},"invalid_number":{"instruction_type":"error","instruction_message":"The card number is not a valid credit card number."},"invalid_expiry_month":{"instruction_type":"error","instruction_message":"The card's expiration month is invalid."},"invalid_expiry_year":{"instruction_type":"error","instruction_message":"The card's expiration year is invalid."},"invalid_cvc":{"instruction_type":"error","instruction_message":"The card's security code is invalid."},"incorrect_number":{"instruction_type":"error","instruction_message":"The card number is incorrect."},"incomplete_number":{"instruction_type":"error","instruction_message":"The card number is incomplete."},"incomplete_cvc":{"instruction_type":"error","instruction_message":"The card's security code is incomplete."},"incomplete_expiry":{"instruction_type":"error","instruction_message":"The card's expiration date is incomplete."},"incomplete_zip":{"instruction_type":"error","instruction_message":"The card's zip code is incomplete."},"expired_card":{"instruction_type":"error","instruction_message":"The card has expired."},"incorrect_cvc":{"instruction_type":"error","instruction_message":"The card's security code is incorrect."},"incorrect_zip":{"instruction_type":"error","instruction_message":"The card's zip code failed validation."},"invalid_expiry_year_past":{"instruction_type":"error","instruction_message":"The card's expiration year is in the past"},"card_declined":{"instruction_type":"error","instruction_message":"The card was declined."},"missing":{"instruction_type":"error","instruction_message":"There is no card on a customer that is being charged."},"processing_error":{"instruction_type":"error","instruction_message":"An error occurred while processing the card."},"invalid_request_error":{"instruction_type":"error","instruction_message":"Unable to process this payment, please try again or use alternative method."},"invalid_sofort_country":{"instruction_type":"error","instruction_message":"The billing country is not accepted by SOFORT. Please try another country."}}}},"fetched_oembed_html":false}
Looks like a really nice script. This will be a huge improvement on how we collect this data now. I am amazed that Microsoft does provide more information to end users regarding deleted item size. For companies who are using infinite retention polices as mailbox backups/audit protection, deleted item retention is a very important number to monitor. I hope this number can be extracted using this technique.
I’m encountering an error when I try to run this script, specifically on line 46, it returns {“error”:{“code”:”ErrorAccessDenied”,”message”:”Access is denied. Check credentials and try again.”}}.
I’ve created the app registration, verified the AppID, TenantID, and AppSecret are correct, and I have the Mail.Read Delegated Microsoft Graph and Office 365 Exchange Online permissions set, and granted admin consent. Any other thoughts?
I also notice when expanding the content of the $tokenRequest variable, and parsing it through jwt.ms, it doesn’t contain a roles claim? Should it?
You need application (not delegate) permissions to access a mailbox other than your own.