Table of Contents
Longstanding Service Issue Retrieving SharePoint Usage Data
The Microsoft 365 ecosystem is so large that it’s hard to keep track of everything that changes that show up in different workloads. We’ve always known about the difficulties of tracking new features, deprecations, and other issues, but sometimes it takes a user to report something to focus on a specific problem.
An example is when a reader noted that the Graph-based script to report the storage quota used by SharePoint sites no longer included site URLs in the output (Figure 1). The original script (from 2020) used a registered Entra ID app to authenticate and use the Graph getSharePointSiteUsageDetail API to fetch site detail data.
Problems in the Graph APIs Accessing SharePoint Usage Data
When I investigated the problem, I decided to update the script code to use the Microsoft Graph PowerShell SDK instead. The update did nothing to retrieve the missing data. This isn’t surprising because the problem lies in the Graph API rather than the way the API is called.
The Microsoft 365 admin center uses the same Graph API for its SharePoint site usage report and the same problem of no site URL data is seen there (Figure 2).
Even worse, the SharePoint site activity report in the Microsoft 365 admin center displays no data (Figure 3).
This problem is because the getSharePointActivityUserDetail API returns no data whatsoever. Here’s an example of using the API in PowerShell in an attempt to retrieve SharePoint Online user activity for the last 180 days. The retrieved data should end up in the SPOUserDetail.CSV file.
$Uri = "https://graph.microsoft.com/v1.0/reports/getSharePointActivityUserDetail(period='D180')" Invoke-MgGraphRequest -Uri $Uri -Method GET -OutputFilePath SPOUserDatail.CSV
However, the output file is perfectly empty apart from the column headers (Figure 4).
The same approach works perfectly with other usage data. For instance, this query works nicely to fetch Exchange Online usage data:
$Uri = "https://graph.microsoft.com/v1.0/reports/getEmailActivityUserDetail(period='D180')" Invoke-MgGraphRequest -Uri $Uri -Method GET -OutputFilePath $EmailUsage.CSV
A Known Service Issue with SharePoint Usage Data
It’s not surprising that an API should have a problem. The APIs haven’t changed recently, so the root cause is more likely due to a change in the SharePoint Online back end. This feeling is reinforced by service health report SP676147 filed on 21 September 2023 (last updated 9 February 2024) that blithely says that “SharePoint and OneDrive URLs may not be displayed in some usage reports.”
Microsoft goes on to note that:
“We’re continuing our work through the validation of multiple potential mitigation strategies to display the URLs of the affected site usage reports. Due to the complexity of the scenarios involved we anticipate this may take additional time.”
The next update for the service health announcement is due on 1 March 2024. What I’m struggling with is that the usage reports included site URLs without any difficulty for years. Why it should suddenly become an issue is inexplicable. And taking over six months to find a solution is even more so.
Microsoft suggests that developers use the Graph Sites API to retrieve the site URL. For example:
$Uri = ("https://graph.microsoft.com/v1.0/sites/{0}" -f $Site.'Site Id')
$SiteData = Invoke-MgGraphRequest -Uri $Uri -Method GET
This works, but only when using an application permission. Using delegated permissions restricts access to sites that the signed-in user is a member of.
SharePoint PowerShell Still Works
Fortunately, it’s possible to get the site storage quota information using the SharePoint Online management PowerShell module. The Graph APIs read from a usage data warehouse that’s populated using background processes. The data is always at least two days old, but it’s much faster to access than using PowerShell to check the storage for each site. But needs must, and at least the old method still works.
I admit forgetting about the service health announcement, perhaps because it’s been ongoing for so long. I’m genuinely surprised that Microsoft is still working on something that seems so innocuous. And I’m even more surprised that customers aren’t making more of a fuss because the URL is the fundamental way to identify a SharePoint site.
Learn how to exploit the data available to Microsoft 365 tenant administrators through the Office 365 for IT Pros eBook. We love figuring out how things work.