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?

If you are a SharePoint administrator, you may find it useful to generate reports about how your users use the sites and content. Microsoft has made it easy for you to generate a usage report that includes statistics such as Total Files, Storage Used, Pages Views, etc. This blog post will show you how to generate the usage reports for all SharePoint Online sites in your tenant. These reports can be used to help you track and monitor user activity on your SharePoint sites.

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:

1. Navigate to Microsoft 365 Admin Center at https://admin.microsoft.com/
2. Click on Reports >> Usage in the left navigation.
3. 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.

How to Generate Usage Reports using Graph API and PowerShell?

Microsoft Graph API is a communication service that connects and handles data between Azure and 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.

1. Login to Azure Active Directory at https://portal.azure.com  >> Open  “Azure Active Directory” >> Click on “App Registrations” from the left navigation
2. Click on “New Registration” >> Provide a Name for your application, select “Who can use this application or access this API?”, Click on the “Register” button at the bottom to add a new app.

   

Step 2: Grant Permissions to the App

Once the App is created, the next step is to provide the necessary permissions to read usage reports.

1. From the App Registrations page, Open the App you just created >> Click on “API Permissions”
2. Click on “Add a Permission” >>  Select “Microsoft Graph” >> Choose “Application Permissions” >> Under Reports, Select “Reports.Read.All”>> Click on “Add Permissions”.

   

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 requires 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 the “Grant admin consent for <Your-Tenant>” button once.

Step 3: Add the Client Secret

As a final step, we need a client secret key associated with the App.

1. From the App page, click on the “Certificates & secrets” link in the left navigation. Click on the “New client secret” button. Choose the validity duration for the app and create a client secret key.

   

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. Let’s generate usage reports for your SharePoint Online sites using the Graph API and PowerShell:

#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:

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