Microsoft 365 Admin center supports Active users usage report which provides insight information about how the Azure AD users are utilizing their Microsoft 365 licenses and users last activity date in each Microsoft 365 service. In this article, we will explore how to extract the Active users report and generate Azure AD users’ last login date (last activity date) and license details from PowerShell.
We can use Microsoft Graph APIs to generate Office 365 usage reports. Fetching usage reports require the Reports.Read.All permission, either with Application or Delegated permission.
Summary
- Get Graph API Access Token
- Export Active users report
- Find Last Login date of Azure AD Users in each Microsoft 365 Service
- Find Inactive Users by Last Activity date
- Get License Status of Azure AD Users in each Microsoft 365 Service
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 Active users report using PowerShell
We can use the getOffice365ActiveUserDetail API to get active user details from Microsoft Office 365. This report retrieves all the Azure AD users with their license status and last activity date (last login) 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')"
$ReportRawData = (Invoke-RestMethod -Method Get -Uri $ApiUrl -Headers $headers -ErrorAction STOP).Remove(0,3)| ConvertFrom-Csv
$ReportData = @() # Result array
#Process raw report data to resolve last activity date and remove space in column
ForEach($UserRawData in $ReportRawData)
{
#Assign hashtable to add user details
$UserData = [ordered]@{}
$LatestActivityDate = $null# Get overall latest activity date
ForEach ($kvp in $UserRawData.PSObject.Properties) {
$propertyValue =$null
if($kvp.Name -like '*Date*')
{
#Parse string value as DateTime object
$propertyValue = if($kvp.Value) { [DateTime]$kvp.Value } Else {$null}
}
else
{
$propertyValue =$kvp.Value
}
$UserData.add(($kvp.Name -replace ' ',''), $propertyValue)
if($kvp.Name -like '*Activity Date*' -and $propertyValue -gt $LatestActivityDate)
{
# Set latest activity date
$LatestActivityDate= $propertyValue
}
}
# Add overall latest activity date
$UserData.add(('LatestActivityDate'), $LatestActivityDate)
$ReportData += New-Object PSObject -property $UserData
}
#Export report details to CSV
$ReportData | Export-CSV "C:\M365ActiveUserReport.CSV" -NoTypeInformation -Encoding UTF8
Find Last Login date of Azure AD Users in each Microsoft 365 Service
The above commands store the report details in the array object “$ReportData“, we can apply select query and filter query with the result array and generate different reports.
Here, we are considering the last activity date as the last login date, this is not the accurate last login date for the user, but we can use it in some cases such as finding inactive users in the last N days (ex: 30, 90 days). The following command returns Azure AD Users with their last activity date in each service.
#Display last activity dates for all users
$ReportData | Select DisplayName, UserPrincipalName ,"*ActivityDate*"
#Export users’ last activity report to CSV
$ReportData | Select DisplayName, UserPrincipalName ,"*ActivityDate*" |`
Export-CSV "C:\UserLastActivityReport.CSV" -NoTypeInformation -Encoding UTF8
Find Inactive Users by Last Activity date
We can filter the result array object by LatestActivityDate or any other last activity date property to find inactive users. The following command returns inactive Microsoft 365 users by the last activity date in the last 90 days.
$DaysInactive = 90
$dateTime = (Get-Date).Adddays(-($DaysInactive))
$ReportData | Where-Object { $_.LatestActivityDate -eq $Null -OR $_.LatestActivityDate -le $dateTime } |`
Select DisplayName, UserPrincipalName ,LatestActivityDate
We can also find inactive users in specific services (ex: Teams, Exchange, OneDrive, etc..). The below command gets inactive users in Microsoft Teams in the last 90 days.
$DaysInactive = 90
$dateTime = (Get-Date).Adddays(-($DaysInactive))
$ReportData | Where-Object { $_.TeamsLastActivityDate -eq $Null -OR $_.TeamsLastActivityDate -le $dateTime } |`
Select DisplayName, UserPrincipalName ,TeamsLastActivityDate
Get License Status of Azure AD Users in each Microsoft 365 Service
We can query the result array “$ReportData” to retrieve the license status fields. The below command returns all Azure AD users with their license status in each service.
$ReportData | Select DisplayName, UserPrincipalName, "Has*"
We can also find users by specific license (ex: Exchange, OneDrive) applied or not. The below command lists users with the Exchange license.
$ReportData | Where-Object { $_.HasExchangeLicense -eq 'True'} |`
Select DisplayName, UserPrincipalName, AssignedProducts
Run the below command to list users with the OneDrive license.
$ReportData | Where-Object { $_.HasOneDriveLicense -eq 'True'} |`
Select DisplayName, UserPrincipalName, AssignedProducts
The below command lists users with the SharePoint license.
$ReportData | Where-Object { $_.HasSharePointLicense -eq 'True'} |`
Select DisplayName, UserPrincipalName, AssignedProducts
I’m getting a 403 forbidden when I attempt this line:
$ReportRawData = (Invoke-RestMethod -Method Get -Uri $ApiUrl -Headers $headers -ErrorAction STOP).Remove(0,3)| ConvertFrom-Csv
Ensure that your OAuth $AccessToken has the require permission “Reports.Read.All”