Last week, a change was rolled out to the Usage reports endpoints in the Graph API, one that allows us to specify in which format the report data should be returned. Until now, this was done in a CSV format, by providing a 302 redirect to the (temporary) location where you could download the CSV file. With those additions, you can now request the report to be provided in a JSON format, so you can “consume” it directly.
You can find the corresponding entries over at the Graph API changelog. The relevant documentation articles have also been updated (only on the /beta endpoint, at least for the time being). Here is the important part:
This method supports the $format, $top, and $skipToken OData query parameters to customize the response. The default output type is text/csv. However, if you want to specify the output type, you can use the OData $format query parameter set to text/csv or application/json.
As you can see from the above, the default output type remains CSV, so if that’s what you prefer, no code changes should be needed. If however you want to receive the report in JSON format, you will need to add the additional ?$format=application/json query parameter to your GET request. After previewing this functionality for a limited set of reports, Microsoft is now ready for a broader availability and has rolled it out to the full set of reporting endpoints.
Let’s take a look at a specific example, such as the OneDrive Usage > File counts report, obtainable via the getOneDriveUsageFileCounts endpoint (now method?). As long as we have a valid access token with sufficient permissions to query the reports endpoint, the code is simple:
$uri = "https://graph.microsoft.com/beta/reports/getOneDriveUsageFileCounts(period='D7')?`$format=application/json" $Gr = Invoke-WebRequest -Headers $AuthHeader1 -Uri $uri -Verbose -Debug ($gr.Content | ConvertFrom-Json).value @odata.type : #microsoft.graph.oneDriveUsageFileCounts reportRefreshDate : 2021-11-23 siteType : All total : 67464 active : 0 reportDate : 2021-11-23 reportPeriod : 7 @odata.type : #microsoft.graph.oneDriveUsageFileCounts reportRefreshDate : 2021-11-23 siteType : All total : 67464 active : 251 reportDate : 2021-11-22 reportPeriod : 7 @odata.type : #microsoft.graph.oneDriveUsageFileCounts reportRefreshDate : 2021-11-23 siteType : All total : 67216 active : 4 reportDate : 2021-11-21 reportPeriod : 7 @odata.type : #microsoft.graph.oneDriveUsageFileCounts reportRefreshDate : 2021-11-23 siteType : All total : 67212 active : 2 reportDate : 2021-11-20 reportPeriod : 7
where we have requested the report data to be returned as JSON and parsed it accordingly. As an added benefit, using the JSON format also allows one to work with the reports directly in the Graph Explorer tool, which otherwise cannot handle the 302 redirect. Here’s how the same report looks in the Graph Explorer:
Another benefit of the JSON format is that you can now combine it with other query parameters, such as $top, which in turn allows you to get just the data you care about instead of the full report. Note that not every report has been updated to support this, but here’s an example using the Outlook activity > User detail report:
$uri = "https://graph.microsoft.com/beta/reports/getEmailActivityUserDetail(period='D7')?`$format=application/json&`$top=1"
$Gr = Invoke-WebRequest -Headers $AuthHeader1 -Uri $uri -Verbose -Debug
($gr.Content | ConvertFrom-Json).value
reportRefreshDate : 2021-11-23T00:00:00Z
userPrincipalName : xxxxx@michev.info
displayName : xxxxx
isDeleted : False
deletedDate :
lastActivityDate :
sendCount : 0
receiveCount : 0
readCount : 0
meetingCreatedCount : 0
meetingInteractedCount : 0
assignedProducts : {OFFICE 365 E5}
reportPeriod : 7
Instead of returning the full report, only a single entry was returned, as instructed by the $top query parameter. While the example given here is hardly a useful one, you need to keep in mind that by default a maximum of 200 items will be returned when using the $format=application/json query parameter. This is where the $top query parameter will come handy.
Lastly, I want to reiterate that those changes are only available on the /beta endpoint currently. Trying to use the $format=application/json query parameter against the /v1.0 one will result in an error. I’ll update the article once the changes are released in GA.