How to Report SharePoint Online Site Usage Data with PowerShell and the Graph

Remove the Need for Administrator Access

Following an article I wrote about using PowerShell to report SharePoint Online site storage usage, a reader asked if it is possible to create such a report without needing to sign in with a SharePoint Online administrator account. They’d like non-admins to be able to report data but don’t want them to run PowerShell scripts or access the admin center.

Global Reader Role and Usage Reports

The Global Reader role allows non-privileged access to reporting data exposed in some interfaces like the Microsoft 365 Admin Center. The reports section of the admin center (Figure 1) includes reports on SharePoint user and site activity.

The Admin Center includes an export option to download data as a CSV file. If you select the site usage report, this seems promising, until you realize that the report includes redirect sites and even the SharePoint Online Tenant Fundamental Site created when the tenant is initialized. Some of the fields output could be better formatted too.

Usage Reports Based on the Graph

The usage reports available in the Microsoft 365 Admin Center use the Graph reports API to generate their data. It’s very easy to write a quick and dirty PowerShell script to call the SharePoint Site Storage Usage API to return the same data as you see in the usage reports. Once the data is downloaded, you can manipulate it to meet your needs.

Granting Permissions for the Graph

To gain access to the Graph, PowerShell needs to use an app to authenticate with the Reports.Read.All permission. The basic idea is that you register an app for your tenant to use with PowerShell and generate an app secret to use for OAuth authentication. You then assign the necessary Graph permissions to the app. The request to add the permissions to the app must be approved by an administrator before the permissions can be used. You can then use the app identifier and secret to generate an access token for the Graph which contains the permission. Read this post for detailed steps of creating such an app for PowerShell to use.

Accessing the Graph

After the app is authorized with the necessary permissions, we can use it with PowerShell. This snippet:

• Populates variables with the app identifier, secret, and tenant identifier. These values are unique to an app and to a tenant. You’ll have to change them for your tenant for this code to work.
• Builds a request to get an access token.
• Parses the returned token.

$AppId = "e716b32c-0edb-48be-9385-30a9cfd96155"
$TenantId = "c662313f-14fc-43a2-9a7a-d2e27f4f3478"
$AppSecret = 's_rkvIn1oZ1cNceUBvJ2or1lrrIsb*:='

# Build the request to get the OAuth 2.0 access token
$uri = "https://login.microsoftonline.com/$tenantId/oauth2/v2.0/token"
$body = @{
    client_id     = $AppId
    scope         = "https://graph.microsoft.com/.default"
    client_secret = $AppSecret
    grant_type    = "client_credentials"}

# Request token
$tokenRequest = Invoke-WebRequest -Method Post -Uri $uri -ContentType "application/x-www-form-urlencoded" -Body $body -UseBasicParsing
# Unpack Access Token
$token = ($tokenRequest.Content | ConvertFrom-Json).access_token
$headers = @{Authorization = "Bearer $token"}
$ctype = "application/json"

With a token, we can issue the Graph request to fetch the SharePoint Online storage usage data:

# Get SharePoint files usage data
$SPOFilesReportsURI = "https://graph.microsoft.com/v1.0/reports/getSharePointSiteUsageDetail(period='D7')"
$Sites = (Invoke-RestMethod -Uri $SPOFilesReportsURI -Headers $Headers -Method Get -ContentType "application/json") -Replace "", "" | ConvertFrom-Csv

All that remains to be done is to parse the returned data and generate a report (a CSV file). You can download the script from GitHub. As always, the code is bare-bones and doesn’t include much in terms of error checking.

I also output the report data to Out-GridView (Figure 2) as it’s the easiest way to browse the information.

Advantages and Disadvantages

The big advantage of this approach is that no dependency exists on cmdlets in the PowerShell module for SharePoint Online or an administrator account. All the code is basic PowerShell that can be run by any user.

Because this approach uses data fetched from the Graph, the code is fast too – much faster than the version based on the SharePoint Online cmdlets, and the speed advantage becomes larger as the number of sites grows. This is because the Graph generates the report data and has it ready for fetching while the other approach requires you to generate the data for each site with the Get-SPOSite cmdlet. On the other hand, the Graph data is at least two days old, something that might not be too much of a concern when reviewing storage usage.

The downside is that the Graph usage data includes a limited set of properties. Some useful properties, like site files, active files, and views, aren’t returned by the Get-SPOSite cmdlet, but Get-SPOSite returns information like the site title, group identifier (to get a list of site owners), and sensitivity label among others.

Combine to Get Both

Combining data fetched from the Graph with that fetched by Get-SPOSite is the best of both worlds, even if you’ll need to use a SharePoint administrator account. The question is what data are needed. If you really need the extended information about a site, you’ll have to use the SharePoint Online module. But if all you need is simple storage data, the Graph can provide that information quickly, albeit if it’s slightly out-of-date.