Export Microsoft 365 License Usage Report using PowerShell

by

Microsoft 365 Admin center supports license usage summary reports such as “number of daily active users, overall active users by service in last N days, and user’s last activity date in each service“. We can easily export these reports from the Admin center under Usage reports. For more information, see View and Export License Usage Reports from the Admin center.

Apart from the Admin center, Microsoft Graph supports APIs to generate Microsoft 365 active users reports. In this post, we will explore how to generate license usage reports using Microsoft Graph API from PowerShell. Fetching usage summary report requires the Reports.Read.All permission, either with Application or Delegated permission.

Summary

Get Graph API Access Token

The following commands get Access Token for application permissions. You should have already created an Azure AD app, configured the application permission “Reports.Read.All” and granted Admin consent to use the permission in the app.  You can refer to this post for more details: How to Register and Configure Azure AD application.

#Provide your Office 365 Tenant Id or Tenant Domain Name
$TenantId = "contoso.onmicrosoft.com"
  
#Provide Azure AD Application (client) Id of your app.
#You should have granted Admin consent for this app to use the application permission "Reports.Read.All" in your tenant.
$AppClientId="xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"
  
#Provide Application client secret key
$ClientSecret ="Y4o6R~2M.runI3QFTn1aXX_7Ubmz35xZf2wsY"
  
$RequestBody = @{client_id=$AppClientId;client_secret=$ClientSecret;grant_type="client_credentials";scope="https://graph.microsoft.com/.default";}
$OAuthResponse = Invoke-RestMethod -Method Post -Uri https://login.microsoftonline.com/$TenantId/oauth2/v2.0/token -Body $RequestBody
$AccessToken = $OAuthResponse.access_token

Export daily Active users count by product

In the below script, we are using the getOffice365ActiveUserCounts API which gets the count of daily active users in the reporting period by each product (Ex: Exchange, SharePoint, Yammer, Teams), we have provided the reporting period as 180 days (D180), you can specify a different supported period (ex: D7, D30, D90).

#Provide your access token. 
#$AccessToken="eyJ0eXAiOiJ......" 
 
#Form request headers with the acquired $AccessToken
$headers = @{'Content-Type'="application\json";'Authorization'="Bearer $AccessToken"}
 
#This API request get daily active users count by service in last 180 days
$ApiUrl = "https://graph.microsoft.com/v1.0/reports/getOffice365ActiveUserCounts(period='D180')"
 
$ReportData = (Invoke-RestMethod -Method Get -Uri $ApiUrl -Headers $headers -ErrorAction STOP).Remove(0,3)| ConvertFrom-Csv

$ReportData | Export-CSV "C:\DailyActiveUserCounts.CSV" -NoTypeInformation -Encoding UTF8

Get Active and Inactive users count by service in last N days

We can use the getOffice365ServicesUserCounts API to retrieve the number of users by activity type (active and inactive) and service (ex: Exchange, SharePoint, OneDrive, etc..).

#Provide your access token. 
#$AccessToken="eyJ0eXAiOiJ......" 
 
#Form request headers with the acquired $AccessToken
$headers = @{'Content-Type'="application\json";'Authorization'="Bearer $AccessToken"}
 
#This API request get active and inactive users count by service in last 180 days
$ApiUrl = "https://graph.microsoft.com/v1.0/reports/getOffice365ServicesUserCounts(period='D180')"
 
$ReportData = (Invoke-RestMethod -Method Get -Uri $ApiUrl -Headers $headers -ErrorAction STOP).Remove(0,3)| ConvertFrom-Csv
$ReportData | Export-CSV "C:\UserCountsByServiceUsage.CSV" -NoTypeInformation -Encoding UTF8

Export Microsoft 365 Active users report

We can use the getOffice365ActiveUserDetail API to get Microsoft Office 365 active user details. This report list all the users with their license status and last activity date in each service (ex: Exchange, SharePoint, OneDrive, etc..).

Note: By default, user details (User Principal Name, Display Name) will be hidden in this report. To display the user details, follow the Steps to show user details in the reports.

#Provide your access token. 
#$AccessToken="eyJ0eXAiOiJ......" 
 
#Form request headers with the acquired $AccessToken
$headers = @{'Content-Type'="application\json";'Authorization'="Bearer $AccessToken"}
 
#This API request get active users report in last N days (ex: D180)
$ApiUrl = "https://graph.microsoft.com/v1.0/reports/getOffice365ActiveUserDetail(period='D180')"
 
$ReportData = (Invoke-RestMethod -Method Get -Uri $ApiUrl -Headers $headers -ErrorAction STOP).Remove(0,3)| ConvertFrom-Csv

$ReportData | Export-CSV "C:\M365ActiveUserReport.CSV" -NoTypeInformation -Encoding UTF8

Related Posts

Advertisement