How to Get Microsoft 365 Usage Reports Using Microsoft Graph in Power Automate?

In Power Automate, I received a requirement from a client asking if it’s possible to generate detailed Microsoft 365 usage reports daily, weekly, or monthly, specifically for SharePoint Online sites.

While SharePoint provides a ‘Site Usage’ feature to view usage reports, they wanted to export this information into an Excel or CSV file. The report would track which users accessed the site, usage statistics, total number of files, visits, views, and more.

After researching various approaches, I found that Microsoft Graph API can be used to achieve this. In this tutorial, I will show how to get Microsoft 365 usage reports using Microsoft Graph in Power Automate.

Additionally, we will discuss how to download the microsoft 365 usage reports using Microsoft Graph Api in Power Automate.

Get Microsoft 365 Usage Reports Using Microsoft Graph API in Power Automate

Before creating the flow, you must create an app registration in your Azure AD. I have already created the app in Azure AD.

To make sure the App Registration has the right access, follow these steps:

• Check that the API permissions include Reports.Read.All under Application permissions.
• Ensure that an admin has approved these permissions.

Under Certificates & secrets, click on + New client secret. Provide a description, choose an expiration period (e.g., six months, one year), then click Add. A Value for the new client secret will be generated. Copy and save this value right away because you will need it in Power Automate, and you won’t be able to see it again after leaving the page.

Note:

It can take a couple of hours for these permissions to take effect. So if your Power Automate flow doesn’t work right away, give it a few hours for the permissions to fully apply.

Now follow the below steps:

1. Navigate to the Power Automate Home page, click + Create, and select the Scheduled Cloud Flow. Then provide the following information:

• Starting: Provide on which date you want to run your flow.
• at: Provide the time you want to run the flow, in my case, at 10:00 AM.
• Repeat every: We want to run the flow on Friday every week.

2. Next, I created three Initialize variable actions to store the Tenant ID, Client ID, and Client Secret Value. All three variables are of type String. In the Value field of each variable, enter the corresponding values:

• Tenant ID – Provide your Tenant ID.
• Client ID – Provide your Client ID.
• Client Secret Value – Provide your Client Secret Value.

3. Add a compose action and provide all of the M365 usage report request urls you want to download reports for:

[
"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')",
  "https://graph.microsoft.com/v1.0/reports/getSharePointSiteUsagePages(period='D7')",
  "https://graph.microsoft.com/v1.0/reports/getSharePointActivityUserDetail(period='D7')",
  "https://graph.microsoft.com/v1.0/reports/getSharePointActivityFileCounts(period='D7')",
  "https://graph.microsoft.com/v1.0/reports/getSharePointActivityUserCounts(period='D7')",
  "https://graph.microsoft.com/v1.0/reports/getSharePointActivityPages(period='D7')"
]

4. Next, I added an HTTP action (Premium Connector) using the POST method to generate an Access Token with application permissions. This access token will be used in the headers of subsequent Microsoft Graph API calls to download Microsoft 365 usage reports.

Provide the below parameter in the HTTP action:

• URI: In the URI field, use the following format:

https://login.microsoftonline.com/@{variables('varTenantID')}/oauth2/token

• Method: Select POST from the drop-down
• Headers: Add the following key-value pair:

Key: Content-Type
Value: application/x-www-form-urlencoded

• Body: provide the following:

client_id=@{variables(' varClientID')}&client_secret=@{variables('varClientSecretValue')}&resource=https://graph.microsoft.com&grant_type=client_credentials&

5. Add an Apply to each action. In the ‘Apply to each’ input field, use the following expression:

array(outputs('Compose'))

6. Next, inside the loop, add an HTTP action using the below parameter:

• URI: provide the current item from dynamic content (which will be the URL for the usage report)
• Method: Select GET from the drop-down
• Headers: Add the following key-value pair:

Key: Authorization
Value: "Bearer @{body('HTTP_|_Login_Microsoft_Graph')['access_token']}"

This will use the access token generated earlier to authenticate the request to Microsoft Graph.

7. Add a new HTTP action after the HTTP | Usage Report action within the Apply to each loop. Then provide the below parameter:

• URI: Put the below expression:

@{outputs('HTTP_|_Usage_Report')['headers']['location']}

• Method: Select GET from the drop-down

8. Select the HTTP action, open its settings, and expand Run after. Check the box for ‘has failed.’

9. Then I add the create file action and provide the below parameter:

• Site Address: Choose the SharePoint site from the dropdown.
• Folder Path: Select the folder from the Show Picker where you want to store the copied files.
• File Name: provide below expression:

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

• File Content: add body from the HTTP dynamic content

Run the Flow to Get Microsoft 365 Usage Reports Using Microsoft Graph

Now, it’s time to save the flow. Click on Test -> Select Manually -> click Test. After the flow runs successfully, go to the SharePoint library, and you will see that the file has been successfully created.

You can download microsoft 365 usage reports using microsoft graph api in Power Automate.

I hope you follow all the steps using Power Automate and Microsoft Graph API. You can automate downloading Microsoft 365 usage reports; using this flow, you can schedule, saving you time and effort in manually retrieving usage data.

Also, you may like some more Power Automate tutorials:

• Create SharePoint Online List Using Power Automate Rest API
• Convert SharePoint List Item to PDF Using Power Automate
• Update SharePoint Multiselect Column in Power Automate
• Restore Deleted Flow in Power Automate
• Save SharePoint List Attachments to a SharePoint Library Using Power Automate

Bijay Kumar

After working for more than 18 years in Microsoft technologies like SharePoint, Microsoft 365, and Power Platform (Power Apps, Power Automate, and Power BI), I thought will share my SharePoint expertise knowledge with the world. Our audiences are from the United States, Canada, the United Kingdom, Australia, New Zealand, etc. For my expertise knowledge and SharePoint tutorials, Microsoft has been awarded a Microsoft SharePoint MVP (12 times). I have also worked in companies like HP, TCS, KPIT, etc.