Get list of inactive users with licenses assigned in Microsoft 365

[d-rajapaksa]

I have a Microsoft 365 tenant with more than 1000 users in it. I need to get a list of users who have not been active for the last 180 days but are assigned with licenses. The goal is to check if users left the company but never removed their licenses although inactive, which is a waste of money. Please advice. 

[#2]

@d-rajapaksa 

The link below should be the perfect answer:

https://docs.microsoft.com/en-us/azure/active-directory/reports-monitoring/howto-manage-inactive-use...

[#3]

Thanks, but this does not have a way to get users with licenses assigned

[#4 · Accepted solution]

To be more precise you could:

1/ Get all the users with licenses assigned
The most simple solution is: Get-MsolUser -All | where {$_.isLicensed -eq $true}

To go further, I use several custom scripts :
- To get a list of the licenses assigned to a user (ex : Office 365 E3) : https://github.com/thijoubert/Sharing/blob/main/PowerShell/AAD_Licenses_Users.ps1
- To get a full list of the SKU assigned to a user (ex : SharePoint Online P2) : https://github.com/thijoubert/Sharing/blob/main/PowerShell/AAD_Licenses_Users_Details.ps1

2/ Get the last sign-in date for you users:
The most simple solution : https://docs.microsoft.com/en-us/azure/active-directory/reports-monitoring/howto-manage-inactive-use...
You could use Graph explorer with an admin account

3/ Cross-reference the two lists to get your answer

[#5]

@thijoubertold i'm getting the following error in Graph API when try this.

[#6]

Did you grant the relevant permissions?
- User.Read.All
- Directory.Read.All
- Auditlogs.Read.All

[#7]

In the modify permission tab I cannot see the Auditlogs.Read.All... Where can I locate them?

[#8]

@d-rajapaksa 

You can find it here: 

Hope this helps

[#9]

Thank you so much this saved my life...

[#10]

@d-rajapaksaAzure AD is an enterprise product. How can it be that a page/list of inactive users is not available in the interface, by default? It's actually mindblowing.... 

[#11]

As you said it is an Enterprise Product so they provide the infrastructure with an API.
Pretty user interface and prebuilt reports are second class user cases.

[#12]

pretty snooty reply. but it's nonsense. As a charity user I get hounded to make sure my users are all active, that I must de-license the inactive ones, but microsoft provides no easy report to tell me which those are. non-usability is not an 'enterprise' feature. It's a stupid feature.

[#13]

@rayk_sland I use the following powershell code to get info of licensed users who have not logged in for at least 30 days.

# The function of this script is to return all users that have license's and have been inactive in AzureAD sigin logs for x amount of days.
# For customers who have Azure AD Premium P2 subscriptions, the sign-in logs are retained for 30 days.
# By default, Azure AD retains sign-in logs for 30 days, but the retention period can be increased up to two years by using Azure Monitor and Storage accounts

# The command Get-AzureADAuditSignInLogs often gets throttled by Microsoft so there is logic in the script to pause when this is detected and try again.

# This script requires install-module AzureADPreview -force
# Import-module azureadpreview

# You might need to Uninstall-Module AzureAD first to make sure Get-AzureADAuditSignInLogs is a command that you can use….

# Connect-AzureAD

# Download the CSV file containing the product name and SkuPartNumber mappings
Invoke-WebRequest -Uri "https://download.microsoft.com/download/e/3/e/e3e9faf2-f28b-490a-9ada-c6089a1fc5b0/Product%20names%2..." -OutFile "c:\temp\product_names.csv"

# Import the CSV file into a variable and create a dictionary using the String_Id as key and Product_Display_Name as value
$productNames = Import-Csv "c:\temp\product_names.csv"
$skuDict = @{}
foreach($product in $productNames)
{
$skuDict[$product.String_Id] = $product.Product_Display_Name
}

$DaysInactive = 30
$InactiveDate = (Get-Date).AddDays(-$DaysInactive)

$AllUsers = Get-AzureADUser -All $true
$InactiveUsers = @()

foreach ($User in $AllUsers) {
$LicenseDetail = Get-AzureADUserLicenseDetail -ObjectId $User.ObjectId
$License = ($LicenseDetail | ForEach-Object { $skuDict[$_.SkuPartNumber] }) -join ", "

Write-Output "Checking $($User.UserPrincipalName) with License: $License"

if ($LicenseDetail -ne $null) {
$RetryCount = 0
$MaxRetries = 5
$RetryDelayInSeconds = 10

do {
try {
Write-Output "Getting sign-in logs for $($User.DisplayName)"
$AllSignInLogs = Get-AzureADAuditSignInLogs -Filter "UserDisplayName eq '$($User.DisplayName)'"
#Write-Output "All sign-in logs:"
#Write-Output $AllSignInLogs

$SignIn = $AllSignInLogs | Sort-Object -Property CreatedDateTime -Descending | Select-Object -First 1
Write-Output "Most recent sign-in log entry:"
Write-Output $SignIn.CreatedDateTime
$ErrorOccured = $false
}
catch {
$ErrorMessage = $_.Exception.Message
if ($ErrorMessage.Contains("Too Many Requests")) {
Write-Output "Throttling detected. Waiting for $($RetryDelayInSeconds) seconds before retrying..."
Start-Sleep -Seconds $RetryDelayInSeconds
$ErrorOccured = $true
$RetryCount++
}
else {
Write-Output "Error occurred: $ErrorMessage"
$ErrorOccured = $false
}
}
} while ($ErrorOccured -eq $true -and $RetryCount -lt $MaxRetries)

if ($SignIn -eq $null -or $SignIn.CreatedDateTime -lt $InactiveDate) {
$InactiveUsers += [PSCustomObject]@{
DisplayName = $User.DisplayName
UserPrincipalName = $User.UserPrincipalName
LastSignin = $Signin
Enabled = $User.AccountEnabled
License = $License
}
write-output 'InactiveUser! '$User.DisplayName $License
}
}
}

$InactiveUsers | Export-Csv -Path "c:\temp\InactiveUsers.csv" -NoTypeInformation

[#14]

@d-rajapaksa 

See below link on sharing:

List inactive users in Office 365 - MS Exchange - Spiceworks

[#15]

@rayk_sland 

Its not snooty at all, its a fact man. That's how the cloud is done. API automation first, used GUI second or not at all. Some more experience with Azure / Cloud will teach you that. 

[#16]

This works really well, except its not getting any data in the 'LastSignin' column. Could this be something that is not enabled in my tenant. I see the following error showing up often in the PS window when running this script

Getting sign-in logs for Joe Bloggs
Error occurred: The term 'Get-AzureADAuditSignInLogs' is not recognized as the name of a cmdlet

[#17]

just seen the comment for having to "Uninstall-Module AzureAD" and then running "Install-Module AzureADPreview" before Importing it and Connecting to AzureAD. Re-running now against my tenant and it looks like it will yield the full set of data.

Thanks to @richardgnz

[#18]

Hi - Glad it helped. I have rewritten the script to use Microsoft Graph instead as do not know how long AzureADPreview module will be around...

# The function of this script is to return all users that have licences and have been inactive in AzureAD sigin logs for x amount of days.
# For customers who have Azure AD Premium P2 subscriptions, the sign-in logs are retained for 30 days.
# By default, Azure AD retains sign-in logs for 30 days, but the retention period can be increased up to two years by using Azure Monitor and Storage accounts

# Install the required PowerShell modules
Install-Module -Name Microsoft.Graph.Authentication -Scope CurrentUser
Install-Module -Name Microsoft.Graph.Users -Scope CurrentUser
Install-Module -Name Microsoft.Graph.Reports -Scope CurrentUser

# Import the required modules
Import-Module Microsoft.Graph.Authentication
Import-Module Microsoft.Graph.Users
Import-Module Microsoft.Graph.Reports

# Connect to Microsoft Graph using delegated permissions
Connect-MgGraph -Scopes "User.Read.All, AuditLog.Read.All"

# Download the CSV file containing the product name and SkuPartNumber mappings
Invoke-WebRequest -Uri "https://download.microsoft.com/download/e/3/e/e3e9faf2-f28b-490a-9ada-c6089a1fc5b0/Product%20names%2..." -OutFile "c:\temp\product_names.csv"

# Import the CSV file into a variable and create a dictionary using the SkuPartNumber as key and Product_Display_Name as value
$productNames = Import-Csv "c:\temp\product_names.csv"
$skuDict = @{}
foreach($product in $productNames)
{
$skuDict[$product.GUID] = $product.Product_Display_Name
}

# Set the number of days of inactivity to consider
$DaysInactive = 30
$InactiveDate = (Get-Date).AddDays(-$DaysInactive)

# Retrieve all users using the Microsoft Graph API
$AllUsers = Get-MgUser -All -Property Id, DisplayName, UserPrincipalName, AccountEnabled

# Initialize an empty array to store inactive users
$InactiveUsers = @()

# Iterate through each user and check for inactivity in Azure AD sign-in logs
foreach ($User in $AllUsers) {
$LicenseDetail = Get-MgUserLicenseDetail -UserId $User.Id
if ($LicenseDetail -ne $null) {
$License = ($LicenseDetail | ForEach-Object { $skuDict[$_.SkuId] }) -join ", "

Write-Output "Checking $($User.userPrincipalName) with License: $License"

Write-Output "Getting sign-in logs for $($User.displayName)"
$AllSignInLogs = Get-MgAuditLogSignIn -Filter "userDisplayName eq '$($User.displayName)'"
#Write-Output "All sign-in logs:"
#Write-Output $AllSignInLogs

$SignIn = $AllSignInLogs | Sort-Object -Property createdDateTime -Descending | Select-Object -First 1
Write-Output "Most recent sign-in log entry:"
Write-Output $SignIn.createdDateTime

if ($SignIn -eq $null -or $SignIn.createdDateTime -lt $InactiveDate) {
$InactiveUsers += [PSCustomObject]@{
DisplayName = $User.displayName
UserPrincipalName = $User.userPrincipalName
LastSignin = $Signin
Enabled = $User.accountEnabled
License = $License
}
write-output 'InactiveUser! '$User.displayName $License
}
}
}

$InactiveUsers | Export-Csv -Path "c:\temp\InactiveUsers.csv" -NoTypeInformation

Disconnect-MgGraph

[#19]

@richardgnz Fab! I'll give the MSGraph version a run through today. 

The AzureADPreview script ran successfully and only exported the accounts which had no sign-in activity for the last X days. What it didnt do was fill in any data in the CSV column 'LastSignIn' to be able to verify this. I could see the data was been captured and checked as the script ran, either showing the last sign-in if within the X day limit or stating 'Inactive User!' 

Not sure if you have experienced the same with your csv results?

[#20]

@Osiris1910 - Yeah same as you. The issue is with a standard Azure P2 license the signin data is only kept for 30 days. I do not have a tenant configured where this is stored for longer. So users when checked will have a null status for signin if more than 30 days since last login. If logs were configured to be stored longer then you would be able to get a more accurate picture but there would be a cost in doing this.

https://learn.microsoft.com/en-us/azure/active-directory/reports-monitoring/concept-activity-logs-az....