Generate SharePoint Online Site Usage Reports using Graph API and PowerShell

Requirement: Generate usage reports for SharePoint Online sites.

How to Generate Usage Reports for All SharePoint Online Sites in the Tenant?
From Microsoft 365 admin portal you can export the usage data in CSV format. To get usage and activity reports for all SharePoint Online sites, follow these steps:
  • Navigate to Microsoft 365 Admin Center at
  • Click on Reports >> Usage in the left navigation
  • Choose the required report. E.g. SharePoint Site Usage
The SharePoint Site usage reports are available for the last 7 days, 30 days, 90 days, and 180 days. You can also export the report data into an CSV file, by selecting the Export link.
sharepoint online site usage report

How to Generate Usage Reports using Graph API and PowerShell?
Microsoft Graph API is a communication service that connects and handles data between Azure or Microsoft 365 services. To call Microsoft Graph API methods we need an App. Follow these steps to add a new App.

Step 1: Create New App Registration in Azure AD:
To use the Microsoft Graph APIs you first have to register an application.
  • Login to Azure Active Directory at  >> Open  "Azure Active Directory" >> Click on "App Registrations" from the left navigation
  • Click on "New Registration" >> Provide a Name to your application, Select "Who can use this application or access this API?", Click on "Register" button in the bottom to add new app.
    register application in Azure AD
Step 2: Grant Permissions to the App
Once the App is created, the next step is to provide necessary permissions to read usage reports.
  • From the App Registrations page, Open the App you just created >> Click on "API Permissions"
  • Click on "Add a Permission" >>  Select "Microsoft Graph" >> Choose "Application Permissions" >> Under Reports, Select "Reports.Read.All">> Click on "Add Permissions".
    add permissions to App
You may need to select different permission depending on what you want to access. Microsoft Graph API can query almost all details from Azure AD or other Office 365 services.

Consent to the application
Application permission require admin consent in a tenant before it can be used. Create a consent URL like the following:
  • Select the "Reports.Read.All" and Click on "Grant admin consent for <Your-Tenant>" button once.

Step 3: Add Client Secret
As a final step, we need client secret key associated with the App.
  • From the App page, Click on "Certificates & secrets" link in left navigation.. Click on "New client secret" button. Choose the validity duration for the app and create client secret key.
    add client secret

PowerShell to Call Graph API method to get Usage Reports
Once we have the App ready, Copy Paste the Client ID, Secret and Other Parameters to this script and execute.
#Function to Call Graph API
Function Get-UsageReport {
param (
    [parameter(Mandatory = $true)] [string]$ClientID,
    [parameter(Mandatory = $true)] [string]$ClientSecret,
    [parameter(Mandatory = $true)] [string]$TenantName, 
    [parameter(Mandatory=$true)] [string]$GraphUrl
    Try {
        #Graph API URLs
        $LoginUrl = ""
        $ResourceUrl = ""
        #Compose REST request
        $Body = @{ grant_type = "client_credentials"; resource = $ResourceUrl; client_id = $ClientID; client_secret = $ClientSecret }
        $OAuth = Invoke-RestMethod -Method Post -Uri $LoginUrl/$TenantName/oauth2/token?api-version=1.0 -Body $Body
        #Perform REST call
        $HeaderParams = @{ 'Authorization' = "$($OAuth.token_type) $($OAuth.access_token)" }
        $Result = (Invoke-WebRequest -UseBasicParsing -Headers $HeaderParams -Uri $GraphUrl)

        #Format Microsoft Graph Output
        (($Result).RawContent -Split "\?\?\?")[1] | ConvertFrom-Csv
    Catch {
        Write-host "Error: $($_.Exception.Message)" -foregroundcolor Red

$ClientID = "98ax2c6d2-264c-424e-b911-c33x547de5211"
$ClientSecret = "T1ZP3-a_NIoA4cej2xky.2xaDcF..6E5"
$TenantName = ""
$GraphUrl = "'D30')"

#Call the function to get usage data
$UsageData = Get-UsageReport -ClientID $ClientID -ClientSecret $ClientSecret -TenantName $TenantName -GraphUrl $GraphUrl
$UsageData | Export-Csv "C:\Temp\siteusage.csv" -NoTypeInformation
This PowerShell script generates a CSV report as
sharepoint online usage reports using graph API PowerShell

1 comment:

  1. Hello, this tutorial is great, im on my way to getting some dashboards created, however, this report does not include subsites. Is there a way to include them or do I need to iterate through all the sites and look them up individually?


Please Login and comment to get your questions answered!

Powered by Blogger.