Onboarding and offboarding users are crucial aspects of managing a Microsoft 365 organization. Users who stop using their accounts without notifying their manager or IT remain active, potentially incurring unnecessary license costs.
Inactive users haven’t accessed Microsoft 365 resources like Microsoft Teams, Exchange Online, SharePoint, or OneDrive for an extended period, varying from 30 to 365 days.
Organizations should regularly identify and block sign-ins for inactive users via the Microsoft 365 admin center to mitigate security risks and reduce costs. It’s also essential to remove any Microsoft 365 licenses allocated to them.
The specific criteria for defining an inactive user can differ among organizations; for instance, one company might designate an employee inactive after 90 days, while others may use different timeframes.
Balancing account management is crucial to avoid mistakenly disabling legitimate users, especially those on extended leave. It’s essential to keep their accounts active and preserve their data.
This blog post will explore various methods for Identifying inactive users in Microsoft 365. It will also provide the last login date and time to help us determine if there are inactive users.
One of the other approaches Involves examining Entra ID USer Sign-In Activity through Sign-in Logs. You can choose to do this manually via the Entra admin center or use the Get-AzureADAuditSignInLog cmdlet for a more automated process.
Get-AzureADAuditSignInLog
Table of Contents
Method 1 – Using Microsoft Graph Explorer
To use a Graph API query that retrieves the user’s display name and SignInActivity, you can utilize Microsoft Graph Explorer. The specific graph query to use is https://graph.microsoft.com/beta/users?$select=displayName,signInActivity. Let’s check the steps:
- Consent to AuditLog.Read.All and User.Read.All permissions to avoid any errors while executing a query on Graph Explorer.
- To Consent to these permissions, From the Microsoft Graph Explorer page > click on your SignIn Account > Click on Consent to Permissions > Search for AuditLog.Read.All and User.Read.All permissions and provide Consent.
- Execute the query: https://graph.microsoft.com/beta/users?$select=displayName,signInActivity to fetch the required Information on the page.
Example Graph API Queries
1. Search the Last Sign-in date and time for a specific user
https://graph.microsoft.com/beta/users?$filter=startswith(displayName,'john')&$select=displayName,signInActivity
2. List users with LastSignIndateTime before a specified date
https://graph.microsoft.com/beta/users?$filter=signInActivity/lastSignInDateTime le 2022-09-01T00:00:00Z
Method 2 – Using Microsoft Graph Powershell script (Export Users Last Sign-in Date/Time) [Interactive way]
You can manually Export Sign-in Logs reports from Microsoft 365/Entra ID, but that report is not very customizable. Alternatively, you can use the Microsoft Graph API in a PowerShell script to fetch the user information you need, including Sign-in Activity data.
You can export Sign-in Activity data to CSV through Microsoft Graph. The first method is interactive, where you’ll start by connecting to Microsoft Graph using the Connect-MgGraph cmdlet.
The second is a non-interactive approach, which involves retrieving data through an application registered in Entra ID. This section will explore the Interactive method to export Sign-in Activity logs to CSV.
Prerequisites
- To read
lastSignInDateTimeproperty of a user, you will need AuditLog.Read.All and User.Read.All Microsoft Graph permissions. - Entra ID Premium License.
Connect-MgGraph
Connect-MgGraph -Scopes "AuditLog.Read.All", "User.Read.All"
Fetch Users account Properties
Next, we’ll utilize the Get-MgUser cmdlet to fetch the user’s account properties we need. If you wish to include more properties, you can add them to the -Property parameter.
$alluserdata = Get-MgUser -All -Property Userprincipalname, DisplayName, SignInactivity, AccountEnabled
Below are user account properties you can retrieve using Get-MgUSer cmdlet:
AboutMe
AccountEnabled
Activities
AgeGroup
AgreementAcceptances
Analytics
AppConsentRequestsForApproval
AppRoleAssignedResources
AppRoleAssignments
Approvals
AssignedLicenses
AssignedPlans
Authentication
AuthorizationInfo
Birthday
BusinessPhones
Calendar
CalendarGroups
CalendarView
Calendars
Chats
City
CloudPCs
CompanyName
ConsentProvidedForMinor
ContactFolders
Contacts
Country
CreatedDateTime
CreatedObjects
CreationType
CustomSecurityAttributes
DeletedDateTime
Department
DeviceEnrollmentConfigurations
DeviceEnrollmentLimit
DeviceKeys
DeviceManagementTroubleshootingEvents
Devices
DirectReports
DisplayName
Drive
Drives
EmployeeHireDate
EmployeeId
EmployeeLeaveDateTime
EmployeeOrgData
EmployeeType
Events
Extensions
ExternalUserState
ExternalUserStateChangeDateTime
FaxNumber
FollowedSites
GivenName
HireDate
Id
Identities
ImAddresses
InferenceClassification
InfoCatalogs
InformationProtection
Insights
Interests
IsManagementRestricted
IsResourceAccount
JobTitle
JoinedGroups
JoinedTeams
LastPasswordChangeDateTime
LegalAgeGroupClassification
LicenseAssignmentStates
LicenseDetails
Mail
MailFolders
MailNickname
MailboxSettings
ManagedAppRegistrations
ManagedDevices
Manager
MemberOf
Messages
MobileAppIntentAndStates
MobileAppTroubleshootingEvents
MobilePhone
MySite
Notifications
Oauth2PermissionGrants
OfficeLocation
OnPremisesDistinguishedName
OnPremisesDomainName
OnPremisesExtensionAttributes
OnPremisesImmutableId
OnPremisesLastSyncDateTime
OnPremisesProvisioningErrors
OnPremisesSamAccountName
OnPremisesSecurityIdentifier
OnPremisesSyncEnabled
OnPremisesUserPrincipalName
Onenote
OnlineMeetings
OtherMails
Outlook
OwnedDevices
OwnedObjects
PasswordPolicies
PasswordProfile
PastProjects
PendingAccessReviewInstances
People
Photo
Photos
Planner
PostalCode
PreferredDataLocation
PreferredLanguage
PreferredName
Presence
Print
Profile
ProvisionedPlans
ProxyAddresses
RefreshTokensValidFromDateTime
RegisteredDevices
Responsibilities
Schools
ScopedRoleMemberOf
Security
SecurityIdentifier
ServiceProvisioningErrors
Settings
ShowInAddressList
SignInActivity
SignInSessionsValidFromDateTime
Skills
State
StreetAddress
Surname
Teamwork
Todo
TransitiveMemberOf
TransitiveReports
UsageLocation
UsageRights
UserPrincipalName
UserType
WindowsInformationProtectionDeviceRegistrations
AdditionalProperties
Export the Last Sign-in date and time of All Users into a CSV file using below Powershell script
Export-LastSigninDateTime.ps1
<#
.DESCRIPTION
This script Exports users DisplayName, UserprincipalName, Last Sign-in date and time
and Account Enabled status using Microsoft Graph API (Interactive) in a CSV file.
Author: Jatin Makhija
Site: cloudinfra.net
Version: 1.0.0
#>
$output = @()
#Connect to Microsoft Graph with required scopes
Connect-MgGraph -Scopes "AuditLog.Read.All", "User.Read.All"
#Fetch the required properties of a user account
$alluserdata = Get-MgUser -All -Property Userprincipalname, DisplayName, SignInactivity, AccountEnabled
ForEach ($User in $alluserdata)
{
#Get Last Sign-in date for this user into a variable
$signinactivity = $User.SignInActivity.LastSignInDateTime
If($signinactivity -eq $null){
#If user has never logged on then there will not be any logs
$LastSignInDate = "Sign In logs Not available"
}
Else{
#Assign signindatetime value to LastSignindate variable
$LastSignInDate = $signinactivity
}
#Create Powershell Custom Object
$output += [PSCustomObject]@{
UPN = $User.UserPrincipalName
DisplayName = $User.DisplayName
AccountEnabled = $User.AccountEnabled
LastSignInDate = $LastSignInDate
}
}
#Export object value into a CSV file
$output | Export-csv C:\temp\LastSigninInfo.csv -NoTypeInformation
Method 3 – Using Microsoft Graph Powershell script (Export Users Last Sign-in Date/Time) [Non-Interactive way]
The interactive method for exporting a user’s last sign-in data to a CSV file is effective, but it requires the user to authenticate using Connect-MgGraph each time the script is executed.
If you need to automate the script on a schedule, such as generating a weekly report and sending it to your team without manual interaction, the non-interactive method is the ideal choice. For instance, a CI/CD pipeline on Azure DevOps can achieve this.
To enable the non-interactive method, you’ll first need to create an application in Entra ID and configure the necessary permissions. Let’s go through the steps to create and set up this application.
- Sign in to the Entra admin center.
- Go to Applications > App registrations.
- Click on + New registration.
- Provide a Name for this application and select Supported account types.
- Click on Register to create a new application in Entra ID.
- After creating the application, please open it and click on API permissions under Manage.
- Click on + Add a permission.
- Click on Microsoft Graph > Application permissions.
- Search for and select Audit.Read.All and User.Read.All permissions.
- Click on Add permissions.
- Then click on Grant admin consent for <your org name>.
Once you’ve configured the API permissions, the next step is to create a Client secret for this application. Follow these steps to generate a client secret for your application:
- Click on Certificates & Secrets on the left-hand side.
- Go to the Client secrets tab.
- Click on + New client secret.
- Describe this secret and choose an Expiration date.
- Copy the Client secret located under the Value column. This value will only be visible for a limited time; after that, it won’t be available for copying. Therefore, ensure you copy the Client’s secret value as soon as you generate it.
Once you’ve configured the API permissions and created a client secret, you’ll require the following information to use in the PowerShell script:
- Client ID – Open the application, Click the Overview tab, and copy the Application (client) ID value.
- Client secret – Go to Certificates & secrets > Copy the value of secret.
- Tenant ID -Sign in to the Entra admin center. On the Overview page, Copy the Entra ID tenant ID.
Once you’ve gathered all the necessary information, you must replace it in the PowerShell variables within the script.
ClientID, ClientSecret and TenantID variables
$ClientID = "2bc59a27-26dd-4e84-8410-6d5cb28e6010" $ClientSecret = "VZD8Q~be11hbbKBCY~kEMXrKHBeQ5VZYR22UFaf8" $TenantId = "7ccdb47d-f99c-44ss-a9ww-22487525559e"
Generate an access token
$body = @{
client_id = $clientId
scope = "https://graph.microsoft.com/.default"
client_secret = $clientSecret
grant_type = "client_credentials"
}
$req = Invoke-RestMethod -Method Post -Uri https://login.microsoftonline.com/$TenantId/oauth2/v2.0/token -Body $Body
$token = $req.access_token
Connect to Microsoft Graph using the access token
Connect-MgGraph -AccessToken $token | Out-Null
Export the Last Sign-in date and time of All Users into a CSV file using below Powershell script
<#
.DESCRIPTION
This script Export users DisplayName, UserprincipalName, Last Sign-in date and time
and Account Enabled status using Microsoft Graph API (Non-Interactive)
variable.
Author: Jatin Makhija
Site: cloudinfra.net
Version: 1.0.0
#>
#Provide client ID of the Applicaton
$ClientID = "2bc59a27-26dd-4e84-8410-6d5cb28e6010"
#Provide client secret value of the Application
$ClientSecret = "VZD8Q~be11hbbKBCY~kEMXrKHBeQ5VZYR22UFaf8"
#Provide Entra ID tenant ID
$TenantId = "7ccdb47d-f99c-44ss-a9ww-22487525559e"
$body = @{
client_id = $clientId
scope = "https://graph.microsoft.com/.default"
client_secret = $clientSecret
grant_type = "client_credentials"
}
$req = Invoke-RestMethod -Method Post -Uri https://login.microsoftonline.com/$TenantId/oauth2/v2.0/token -Body $Body
$token = $req.access_token
#Connect to Microsoft Graph using Access token
Connect-MgGraph -AccessToken $token | Out-Null
Select-MgProfile beta
# Get required user properties into a variable
$alluserdata = Get-MgUser -All -Property Userprincipalname, DisplayName, SignInactivity, AccountEnabled
ForEach ($User in $alluserdata)
{
#get user's lastsignindatetime value into a variable
$signinactivity = $User.SignInActivity.LastSignInDateTime
If($signinactivity -eq $null){
#If user has not logged in even once, No Sign-in Logs will be available
$LastSignInDate = "Sign In logs Not available"
}
Else{
$LastSignInDate = $signinactivity
}
#Create a Custom Powershell Object
$output += [PSCustomObject]@{
UPN = $User.UserPrincipalName
DisplayName = $User.DisplayName
AccountEnabled = $User.AccountEnabled
LastSignInDate = $LastSignInDate
}
}
#Export PS object into a CSV file
$output | Export-csv C:\temp\LastSigninInfo.csv -NoTypeInformation
Export Active Users report from Microsoft 365 admin center
Built-in reports are also readily available in the Microsoft 365 admin center under Reports to identify active users of Microsoft 365 apps. To access these reports, please follow these steps:
- Sign in to the Microsoft 365 admin center.
- Go to Reports > Usage.
- Under Product Reports > Click on Microsoft 365 apps.
- Click on the Active Users tab to find the Latest activity date for all Microsoft 365 apps.
For each user, you’ll discover the following information, which can also be exported into a CSV file using the Export link:
- Report Refresh Date
- User Principal Name
- Display Name
- Is Deleted
- Deleted Date
- Has Exchange License
- Has OneDrive License
- Has SharePoint License
- Has Skype For Business License
- Has Yammer License
- Has Teams License
- Exchange Last Activity Date
- OneDrive Last Activity Date
- SharePoint Last Activity Date
- Skype For Business Last Activity Date
- Yammer Last Activity Date
- Teams Last Activity Date
- Exchange License Assign Date
- OneDrive License Assign Date
- SharePoint License Assign Date
- Skype For Business License Assign Date
- Yammer License Assign Date
- Teams License Assign Date
- Assigned Products – This will show the licenses assigned to the user
- Click on Export to export the data into a CSV file.
Conclusion
In this blog post, we’ve explored various methods for exporting the last sign-in date and time information for users in Microsoft 365. An alternative is to go through Entra ID sign-in logs or use the Get-AzureADAuditSignInLog cmdlet. Once you have the list of users, you can filter the report to identify those who haven’t signed in for an extended period.
Also, this report indicates whether the Microsoft 365 user account is enabled or disabled. If a user account is marked as enabled but hasn’t logged in to Microsoft 365 for an extended period, like a year, it may be worth contacting the user’s manager to determine if the account can be disabled.
More Information
For more information about managing Inactive user accounts in Microsoft 365, click here. You can refer to the link here. It provides Microsoft Graph API queries, which you can run through a Powershell script.
