Even in 2024, Microsoft 365 reporting story leaves a lot to be desired. The experience is spread across multiple interfaces and endpoints, each with its own implementation, controls and data formats. What’s worse, we still lack an uniform way to programmatically access and export the reports, with even “heavy hitters” such as Copilot not getting their Graph API report endpoint months into its worldwide release. Despite multiple requests from customers and ISVs alike, Microsoft is yet to add API support for many useful reports.
In turn, this forces customers to rely to workarounds such as copying cookies and access tokens or other ways of “emulating” the browser client, none of which offer a great experience. And none of which should be required, to be honest. So in this article, we will talk about a way to fetch (most of) the Microsoft 365 admin center and (some of) the Teams admin center reports without having to rely to such workarounds. Let’s dig into the details.
To start with, we need to understand where to get the data, and how to authenticate to the endpoint(s) in question. Enter the browser client. If you take a look at the requests used to fetch data for any of the Microsoft 365 admin center reports, you will note that none of them makes a direct query against the Graph API, even though we often hear from Microsoft about the “Graph-first” approach. Instead, all the reports are fetched from a different endpoint, which unfortunately we cannot auth against. Still, we can get a clue from the request being made as the report names look vaguely familiar…
Luckily, we can get further clues. By navigating to the Teams admin center’s Analytics & Reports > Usage reports section, we can select the Teams Apps usage report, select the desired date range for it as needed, then press the Run report button, all while having the browser’s Network capture feature monitor any requests being made. This reveals the following:
- A request is made against the https://login.microsoftonline.com/{tenantId}/oauth2/v2.0/token endpoint in order to obtain an access token for the https://reports.office.com resource. Said request leverages the refresh token obtained by the browser client when initially logging in to the Teams admin center, and is unfortunately something we cannot leverage.
- Leveraging the access token, a request is made against the https://reports.office.com/private/intraTenantConfig/host endpoint, in order to get the regional host for the reporting service (i.e. https://REPORTSWEU.OFFICE.COM).
- Then, requests are made for all the relevant endpoints for the selected report, which includes a variety of “count” and “details” queries. For example, the query below gives us the 180 day aggregate usage data for Teams apps in my tenant:
GET https://reportsweu.office.com/internal/ux/getTeamsAppUsageDetail?tenantId=923712ba-352a-4eda-bece-09d0684d0cfb&Aggregate=M180
- The output is a JSON blob, which is then rendered on the page to present you with the report data.
With the above information at hand, we can come up with a solution that bypasses the browser layer and fetch the data directly. Authentication is the main hurdle we need to solve here, as we certainly do not want to rely on having to manually copy the refresh token out of the TAC page. Luckily, we’ve run into the https://reports.office.com resource before. It is used by many of the PowerBI-based reporting/analytics packs, such as the Microsoft 365 usage analytics or the CQD Power BI query templates. Without further ado, we can leverage the following:
$appId = "a672d62c-fc7b-4e81-a576-e60dc46e951d" #"Microsoft PowerQuery For Excel" built-in application
$tenantId = "923712ba-352a-4eda-bece-09d0684d0cfb"
$redirectURI = "https://preview.powerbi.com/views/oauthredirect.html"
#Load the MSAL binaries
Add-Type -Path "C:\Program Files\WindowsPowerShell\Modules\MSAL\Microsoft.Identity.Client.dll"
#Get a token for the https://reports.office.com resource
$app2 = [Microsoft.Identity.Client.PublicClientApplicationBuilder]::Create("a672d62c-fc7b-4e81-a576-e60dc46e951d").WithRedirectUri($redirectURI).WithTenantId($tenantId).WithBroker().Build()
$Scopes = New-Object System.Collections.Generic.List[string]
$Scope = "https://reports.office.com/.default"
$Scopes.Add($Scope)
$token = $app2.AcquireTokenInteractive($Scopes).ExecuteAsync().Result
#Set the auth header
$authHeader1 = @{
'Authorization'="Bearer $($token.AccessToken)"
}
#Use the token to obtain the Teams App Usage Detail report
$res = Invoke-WebRequest -Uri "https://reportsweu.office.com/internal/ux/getTeamsAppUsageDetail?pageSize=200&tenantId=923712ba-352a-4eda-bece-09d0684d0cfb&Aggregate=M180" -Headers $authHeader1
($res.Content | ConvertFrom-Json).value
If all goes well, the result will resemble something like:
Indeed, those are the exact same values presented in the TAC UI. Apart from the Teams App Usage detail report/view, we can also fetch the Teams App installed counts data:
$Res = Invoke-WebRequest -Uri "https://reportsweu.office.com/internal/ux/getTeamsAppInstalledCounts?tenantId=923712ba-352a-4eda-bece-09d0684d0cfb&Aggregate=M180" -Headers $authHeader1 ($res.Content | ConvertFrom-Json).Value
The appInstalledCounts property from the output of the above request will give you a breakdown of the number of Teams apps installed across teams within the tenant, broken down per day, for each of the past 180 days.
Now, it’s important to understand that this method does not work for all reports available within the TAC. In fact, only a handful use the endpoint above, with the rest spread across various other endpoints and methods. The process however does work for (most of) the reports available within the Microsoft 365 admin center. In fact, if you take a look at the metadata document that can be obtained from the endpoint above, you will note a horde of other reports available for grab:
#Fetch the medatada document
$res = Invoke-WebRequest -Uri 'https://reportsweu.office.com/internal/ux/$metadata' -Headers $authHeader1
[xml]$metadata = $res.Content
#All available reports?
$allReports = ($metadata.Edmx.DataServices.Schema | ? {$_.Namespace -eq "Default"}).EntityContainer.EntitySet
A whooping total of 160 entries can be found therein, spanning from reports that already have Graph API endpoints, to reports only available within the Microsoft 365 admin center, to datasets used for the Usage analytics PowerBI pack, to reports that are not even yet made available in the admin center UI. Here’s a sneak peak:
But wait, some of those names look very familiar! Not only you can find names matching the Graph API endpoints, but you can also correlate them to the requests being made in the reports section of the M365 admin center, even for reports we can’t access via the API. For example, here is the data for the Copilot readiness user detail view:
#Copilot readiness user detail $res = Invoke-WebRequest -Uri "https://reportsweu.office.com/internal/ux/getCopilotReadinessActivityUserDetail?pageSize=200&tenantId=923712ba-352a-4eda-bece-09d0684d0cfb&Aggregate=M180" -Headers $authHeader1 ($res.Content | ConvertFrom-Json).value
And the Copilot usage one:
#Copilot usage user detail $res = Invoke-WebRequest -Uri "https://reportsweu.office.com/internal/ux/getCopilotActivityUserDetail?pageSize=200&tenantId=923712ba-352a-4eda-bece-09d0684d0cfb&Aggregate=M180" -Headers $authHeader1 ($res.Content | ConvertFrom-Json).value
But wait, there is more! As hinted above, there are actually some datasets that are not even exposed in the UI yet. One such example is the new, “V2”, Teams Premium feature usage report, the “counts” view for which you can fetch via:
#Teams Premium feature usage counts (V2) $res = Invoke-WebRequest -Uri "https://reportsweu.office.com/internal/ux/getTeamsPremiumV2ActiveUserCounts?pageSize=200&tenantId=923712ba-352a-4eda-bece-09d0684d0cfb&Aggregate=M180" -Headers $authHeader1 ($res.Content | ConvertFrom-Json).value.userCounts | ft
Granted, none of these examples are terribly interesting when run against my own tenant, but they do illustrate the idea. And, in the case of the Teams Premium usage report, you can even get some newer metrics, compared to the fields exposed in the similarly named report currently available in the Teams admin center, in preview.
I want to again reiterate that this method does not allow you to access each an every report available across the suite, which serves to further emphasize the remark I made in the beginning about the disjointed nature of the reporting experience in Microsoft 365. If you take a look at the set of reports available within the Teams admin center for example, you will find no less than five different endpoint being used… joy!
Before closing the article, one thing to mention. It looks like we cannot mimic the call made to determine the “host” value for the tenant. Although the generic https://reports.office.com endpoint seems to work, you might want to err on the side of caution guesstimate and hardcode the “regional” value. If you are having troubles with that part, grab the network capture for the Teams app usage report and look at the corresponding response value therein. For example, tenants homed in the EU should have a value of https://reportsweu.office.com.
In summary, we can use the Microsoft PowerQuery For Excel built-in application (with appID of a672d62c-fc7b-4e81-a576-e60dc46e951d) to fetch data from multiple Microsoft 365 reporting datasets. While this method offers no advantages in scenarios where existing Graph API endpoints can be leveraged for the report you are interested in, it does offer a method to fetch data otherwise not accessible by other programmatic methods. The only downside is that the method will unfortunately only work with delegate permissions, so automating it is still a challenge. And it will be until (and if) Microsoft decides to bless us with Graph API endpoint for each and every report. Amen.
P.S. Here’s the full list of “views” you can fetch via this method:
ActivationsDesktopActivationsSummary ActivationsMobileActivationsSummary EmailClientTenant EmailClientUser EmailStorageMailbox EmailStorageMailboxTenant ExchangeTenantEmailActivity ExchangeUserEmailActivity getAppHealthCurrency getBrowserActionMetrics getBrowserUserCounts getBrowserUserDetail getCopilotActivityUserDetail getCopilotAdoptionByDate getCopilotAdoptionByProducts getCopilotBCEActivityUserDetail getCopilotBCEAdoptionByDate getCopilotBCEAdoptionByPeriod getCopilotReadinessActivityUserDetail getDistributionBrowserUserCounts getFormsProUserActivityCounts getFormsProUserActivityUserCounts getFormsProUserActivityUserDetail getFormsUserActivityCounts getFormsUserActivityUserCounts getFormsUserActivityUserDetail getM365AppPlatformUserCounts getM365AppUserCounts getM365AppUserDetail getProjectActivityCounts getProjectActivityUserCounts getProjectActivityUserDetail getProjectPlatformUserCounts getProjectTaskActivityCounts getProPlusUsagePlatformsUserCounts getProPlusUsageUserCounts getProPlusUsageUserDetail getRemoteWorkV2Scores getScoresSnapshot getTeamsAppInstalledCounts getTeamsAppInstalledUserCounts getTeamsAppPlatformCounts getTeamsAppUsageAppCounts getTeamsAppUsageDetail getTeamsAppUsageDistributionAppCounts getTeamsAppUsageDistributionUserCounts getTeamsAppUsageUserCounts getTeamsAppUsedCounts getTeamsAppUsedUserCounts getTeamsAppUserDetail getTeamsAppUserPlatformCounts getTeamsClientVersionUsageUserCounts getTeamsClientVersionUsageUserDetail getTeamsPremiumAdvancedWebinarCounts getTeamsPremiumAdvancedWebinarDistribution getTeamsPremiumAdvancedWebinarUserCounts getTeamsPremiumAdvancedWebinarUserDistribution getTeamsPremiumIntelligentMeetingCounts getTeamsPremiumIntelligentMeetingDistribution getTeamsPremiumIntelligentMeetingUserCounts getTeamsPremiumIntelligentMeetingUserDistribution getTeamsPremiumPersonalizedMeetingCounts getTeamsPremiumPersonalizedMeetingDistribution getTeamsPremiumPersonalizedMeetingUserCounts getTeamsPremiumPersonalizedMeetingUserDistribution getTeamsPremiumProtectedMeetingCounts getTeamsPremiumProtectedMeetingDistribution getTeamsPremiumProtectedMeetingUserCounts getTeamsPremiumProtectedMeetingUserDistribution getTeamsPremiumSelfServiceUserCounts getTeamsPremiumSelfServiceUserDistribution getTeamsPremiumUserCounts getTeamsPremiumUserDetail getTeamsPremiumUserDistribution getTeamsPremiumV2ActiveUserCounts getTeamsPremiumV2ActiveUserDistribution getTeamsPremiumV2EngagedUserCounts getTeamsPremiumV2EngagedUserDistribution getTeamsPremiumV2MeetingCounts getTeamsPremiumV2MeetingDistribution getTeamsPremiumV2UserDetail getTeamsPremiumVirtualEventCounts getTeamsPremiumVirtualEventDistribution getTeamsPremiumVirtualEventUserCounts getTeamsPremiumVirtualEventUserDistribution getTeamsTeamActivityCounts getTeamsTeamActivityDetail getTeamsTeamActivityDistributionCounts getTeamsTeamCounts getTenantProductSummary getTenantProductUsage getTenantProductUsageTrend getVisioActivityPremiumUsersCounts getVisioActivityUsersCounts getVisioPlatformCounts getVisioPlatformDistribution getVisioPlatformPremiumCounts getVisioPlatformPremiumDistribution getVisioUserDetail getVivaGoalsActionDistribution getVivaGoalsActivityUsersCounts getVivaGoalsClientCounts getVivaGoalsDepartmentCounts getVivaGoalsUserDetail getVivaLearningActiveUsers getVivaLearningUserActivities GroupActivityGroup GroupActivityGroupV1 GroupActivityTenant GroupActivityTenantV1 LicenseActivityTenant LicenseActivityUser MultiGeoDistributionTenant MultiGeoDistributionUsersSummary Office365GroupsActivityActivitySummary Office365GroupsActivityGroupsSummary Office365GroupsActivityStorageSummary OnedriveSiteUsage OnedriveTenantActivity OnedriveTenantSiteUsage OnedriveUserActivity ReportSettings SharePointActivityFilesSummary SharePointActivityUsersSummary SharepointSiteUsageV1 SharepointTenantActivityV1 SharepointTenantActivityV2 SharepointTenantSiteUsageV1 SharepointUserActivityV1 SharepointUserActivityV2 SkypeTenantActivityOrganizer SkypeTenantActivityParticipant SkypeTenantActivityPeerToPeer SkypeTenantActivitySummary SkypeTenantClientsUsed SkypeUserActivityOrganizer SkypeUserActivityParticipant SkypeUserActivityPeerToPeer SkypeUserActivitySummary SkypeUserClientsUsed StaffHubTeamsActivity StaffHubTenantActivity StaffHubTenantActivitySummary StaffHubTenantTeamsSummary TeamsDeviceUsageV1Tenant TeamsDeviceUsageV1User TeamsUserActivityV1Tenant TeamsUserActivityV1User TeamsUserUsageV1Tenant TenantProplusActivationState TenantTiles UserProplusActivationState WpiBcrHourlyTenant WpiBcrTenant YammerGroupActivityGroup YammerGroupActivityTenant YammerTenantActivity YammerTenantClientsUsed YammerUserActivity YammerUserClientsUsed