In Microsoft 365 there are many usage reports that are available to be retrieved in CSV file format via Microsoft Graph. This blog post will show you how to authenticate to Microsoft Graph using Power Automate then download many different M365 usage reports from your tenant to a SharePoint site on a scheduled basis. This could be handy as most of the usage information in M365 is only retained for 30-120 days before being overwritten.

A template Flow is provided which could be adapted to target other workloads for M365 reports i.e. SharePoint, Teams, Yammer etc

Template flow which downloads the reports and reports written to a SharePoint document library
A report downloaded – showing SharePoint site usage.

Microsoft 365 Usage Reports

Using Microsoft Graph, you can access Microsoft 365 usage reports resources to get information about how people in your business are using Microsoft 365 services. A list of all the current reports usage reports available can be found on the Microsoft Graph – M365 Usage Reports section.

There are many different workloads that can be reported on and you can see the bullet point list below of the available reporting sections.

  • Microsoft Teams device usage
  • Microsoft Teams user activity
  • Outlook activity
  • Outlook app usage
  • Outlook mailbox usage
  • Microsoft 365 activations
  • Microsoft 365 active users
  • Microsoft 365 groups activity
  • OneDrive activity
  • OneDrive usage
  • SharePoint activity
  • SharePoint site usage
  • Skype for Business activity
  • Skype for Business device usage
  • Skype for Business organizer activity
  • Skype for Business participant activity
  • Skype for Business peer-to-peer activity
  • Yammer activity
  • Yammer device usage
  • Yammer groups activity

All of these reporting sections have sub sections for the report type i.e. getSharePointSiteUsageDetail and all have a unique HTTP request URL i.e. https://graph.microsoft.com/v1.0/reports/getSharePointSiteUsageDetail(period=’D7′). This url can then be used to make a HTTP request using Power Automate, PowerShell, Python etc. The url must also have a date parameter to specify the date (i.e. YYY-MM-DD) on which to obtain the report from or a period parameter (D7) to specify the length off time in days the report is to be aggregated for.

If the HTTP Request is successful then a 302 Found Response is given that redirects to a preauthenticated download URL for the report – the URL is then found in the response Location header.

This Preauthenticated download URL is only valid for a short period of time (a few minutes) and does not require an Authorisation header so could even be downloaded from putting the url in a web browser.

Create App Registration in your Azure AD

In order to download the reports the following Microsoft Graph permission Reports.Read.All is required to call the API.

This is done by creating an App Registration in your Azure AD – link here.

Ensure the API permissions for the App Registration have at least Reports.Read.All for application permissions and an admin has granted consent for this. You may also want add Reports.Read.All for delegated (user) permissions but this solution does not require these permissions.

Create a new Client Secret for the app and make a note of the created value which you will use in Power Automate.

Finally make a note of the Application (client) ID and Directory (tenant) ID as you will use these in Power Automate to Authenticate to Microsoft Graph.

Finally in my experience these permissions took a couple of hours to apply so if you Power Automate flow doesn’t work immediately – wait a couple of hours for the permissions to propagate.

Create Flow in Power Automate to download M365 reports

You can either use the steps below as guidance or download a Power Automate template Flow of the solution which can then be added to your environment – download link here.

I will then use Power Automate to perform the following:

  1. Trigger once a week to download the past 7 days reports.
  2. Modify the variables for the tenantId, clientid and client secret to reflect the values from YOUR tenant.
  3. Store a JSON array which holds all of the M365 usage report request urls we want to download reports for. Currently configured to download all SharePoint and OneDrive reports but this should be changed to add/remove further reports from Working with Microsoft 365 usage reports in Microsoft Graph on the Microsoft Graph site i.e. add Teams usage reports.

Sample JSON Array – modify to suit your workload/reporting requirements.

[
     "https://graph.microsoft.com/v1.0/reports/getSharePointSiteUsageDetail(period='D7')",
     "https://graph.microsoft.com/v1.0/reports/getSharePointSiteUsageFileCounts(period='D7')",
     "https://graph.microsoft.com/v1.0/reports/getSharePointSiteUsageSiteCounts(period='D7')",
     "https://graph.microsoft.com/v1.0/reports/getSharePointSiteUsageStorage(period='D7')"
 ]
  1. Connect to Microsoft Graph using a HTTP action using the POST method using application permissions and generate an Access Token. This access token will then be used in the Headers of subsequent Microsoft Graph calls to download M365 usage reports. Ensure the variables are used for tenantId, clientId and clientSecret.
  1. Add an Apply to each to loop through the JSON array of M365 usage report request urls – add the following expression to the Apply to each input section
array(outputs('Compose_M365_Report_Urls'))

The loop will the loop through the JSON array and do the following for each url:

  • Request an M365 Usage report from Microsoft Graph with a HTTP GET action using the access token in the header generated in step four.

Add an Autorization header and then add the word “Bearer” then a SPACE then enter the following in the expression builder. Ensure your actions are named the same as mine or change the expression.

body('HTTP_-_Login_Microsoft_Graph_Using_Application')['access_token']
  • The step above will provide on success a pre-authenticated url in the location header which is used to download the CSV file for the report.

In URI enter the following in the expression builder.

outputs('HTTP_-_Request_M365_Usage_Report')['headers']['location']

NOTE this action must also be configured to run after the previous step has failed to allow for the HTTP redirection which Power Automate treats as failing.

  1. Uploads the CSV file to a SharePoint document library and the name format will be in the format yyyyMMddHHmmss-reportname.csv

Ensure that the SharePoint web part connects to a valid site and library.

File Name uses the following string manipulation in Power Automate to generate a logical file name.

concat(
formatDateTime(utcNow(), 'yyyyMMddHHmmss'),
'-',
replace(first(split(item(),'(')),'https://graph.microsoft.com/v1.0/reports/',''),
'.csv'
)

Completed Power Automate Flow:

Summary

This blog post focused on using Power Automate to download M365 usage reports and provides a flow that can be modified to retrieve any of the numerous M365 usage reports another many workloads i.e. Teams, SharePoint, Onedrive, Yammer, Outlook, Microsoft 365 Groups etc.

You could install this flow in your environment and keep a historical record of M365 usage reports which could be handy as most of the usage reports are reset after 30-120 days. You could also manipulate this information in Power Automate further and perhaps extend the workflow to email reports to certain users or even read the reports using Power Automate.

If you are also interested in reporting further on the M365 Audit logs then see my blogs using Power Automate and the the Office 365 Management API here.

Leave a Reply