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 https://admin.microsoft.com/
  • 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 a 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 https://portal.azure.com  >> 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 the “Register” button at 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 and 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 = "https://login.microsoft.com"
        $ResourceUrl = "https://graph.microsoft.com"
 
        #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
    }
}

#Parameters
$ClientID = "98ax2c6d2-264c-424e-b911-c33x547de5211"
$ClientSecret = "T1ZP3-a_NIoA4cej2xky.2xaDcF..6E5"
$TenantName = "crescentintranet.onmicrosoft.com"
$GraphUrl = "https://graph.microsoft.com/v1.0/reports/getSharePointSiteUsageDetail(period='D30')"

#Call the function to get usage data
$UsageData = Get-UsageReport -ClientID $ClientID -ClientSecret $ClientSecret -TenantName $TenantName -GraphUrl $GraphUrl
$UsageData
$UsageData | Export-Csv "C:\Temp\siteusage.csv" -NoTypeInformation

This PowerShell script generates a CSV report as

sharepoint online usage reports using graph API PowerShell

Microsoft Graph REST API Reference https://docs.microsoft.com/en-us/graph/api/resources/sharepoint-activity-reports?view=graph-rest-1.0

Salaudeen Rajack

Information Technology Professional with Two decades of SharePoint Experience.

2 thoughts on “Generate SharePoint Online Site Usage Reports using Graph API and PowerShell

  • June 24, 2021 at 11:29 AM

    Hi, were you able to pull report for usage on subsite/folder level? I have a similar requirement.

    Reply
  • November 18, 2020 at 2:21 PM

    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?

    Reply

Leave a Reply