Onboarding and Offboarding users is an Important part of any Microsoft 365 organization. When a user stops using his/her account without informing their manager or IT, their account will remain Active and If any license is assigned to the user, license cost will be applicable as well.
It is not only a security risk but an additional cost to the organization because of the Inactive users. When we say Inactive user, it means that user has not signed in to access any Microsoft 365 resources e.g. Microsoft Teams, Exchange Online , Sharepoint, Onedrive etc. for a long period of time, it could be anywhere from 30 days / 180 days / 365 days.
Every organization should regularly check these type of users and block their Sign-in from Microsoft 365 admin center as soon as possible. Any Microsoft 365 license allocated to the user should be removed to save cost.
Each organization’s criteria for defining an Inactive user could be different. For example: My company cloudinfra.net would mark an employee as Inactive when user has been inactive for 90 days. For some organization, this could be 180 days or 1 year.
You do not want to disable legitimate user who is still associated with your organization but could be on a long holiday. Therefore, you want to keep their account active and retain their data.
In this blog post, we will see different ways to find Inactive users in Microsoft 365. One of the ways to check the Azure AD Sign In Activity using Sign-in Logs of each user. Either you can check this info manually from Azure portal or by using Get-AzureADAuditSignInlog cmdlet.
Microsoft Graph Explorer to check for Last Sign-in date and time
You can use Microsoft Graph Explorer to run Microsoft graph queries. Let’s say you want to test a Graph API query which Get’s user’s displayName and SignInActivity. The graph query is https://graph.microsoft.com/beta/users?$select=displayName,signInActivity.
If you have not consented to the AuditLog.Read.All and User.Read.All permissions then you may get an error message when executing this query. You can click on your SignIn Account > Click on Consent to Permissions > Search for these permissions and Consent to it.
Search Last Sign-in date and time for a specific user using MS Graph
You can run below MS Graph query to find Last Sign-in date and time information for a specific user account.
https://graph.microsoft.com/beta/users?$filter=startswith(displayName,'john')&$select=displayName,signInActivity
List user’s with LastSignIndateTime before a specified date
https://graph.microsoft.com/beta/users?$filter=signInActivity/lastSignInDateTime le 2022-09-01T00:00:00Z
Export Last Sign in date/time of All M365 users using Microsoft Graph (Interactive way)
Reports downloaded from Microsoft 365 admin center are not very customizable. It may also not meet your objective to export Sign In activity data about a user which you are looking for. You can export all user’s account data using Microsoft graph API including Sign-in Activity data.
There are two ways in which you can run Microsoft Graph to export this data to CSV. First is Interactive way in which you will need to Connect to Microsoft Graph using Connect-MgGraph cmdlet first and Second is a Non-Interactive method to retrieve this data which requires an application registered in Azure AD.
I will show both the ways and provide tested scripts to export last sign-in date and time information, I will also export other User account properties as well, For example: User’s display Name, User Principal Name, Account Enabled/Disabled status. You can also include more properties in the script to retrieve the data as per your requirement.
To read lastSignInDateTime property of a user, you will need AuditLog.Read.All and User.Read.All Microsoft Graph permissions. Therefore, when connecting to Microsoft graph using Connect-MgGraph cmdlet, you will need to Include these permissions in -scopes parameter. Please note that to access lastSignInDateTime property, you will require Azure AD premium license.
For more Information about managing Inactive user accounts in Microsoft 365. You can refer to the link here. It provides Microsoft Graph API queries which you can run through a powershell script.
Connect-MgGraph
Connect-MgGraph -Scopes "AuditLog.Read.All", "User.Read.All"
Then we will use Get-MgUser cmdlet to fetch user’s account property we require. You can add additional properties into the -Property parameter if you want.
$alluserdata = Get-MgUser -All -Property Userprincipalname, DisplayName, SignInactivity, AccountEnabled
You can fetch below properties about a user using Get-MgUser cmdlet. Make sure to Include the property name with -property switch to retrieve it.
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
Powershell script to Export Last Sign-in date and time of all Users into a CSV file
<#
.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
Export Last Sign in date/time of All M365 users using Microsoft Graph (Non-Interactive way)
Interactive method for exporting user’s Last sign-in date into a CSV file works great. However, due to Its Interactive nature, a user need to authenticate to Connect-MgGraph every time the script will run.
You may have a requirement to run this script automatically on a schedule. For example: If you want to generate this report every week and send it to your team without any any Interaction e.g. by using a CI/CD Pipeline on Azure DevOps. Then a Non-interactive way works best.
For this, you will need to create an application in Azure Active directory and provide necessary permissions first. Let’s see how to create this application and configure its permissions.
- Login on Microsoft 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 Azure AD
- After this application has been created, Open the application 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>
After you have configured API permissions, You will need to create Client Secret for this application. You can follow below steps to create a client secret for this application:
- Click on Certificates & Secrets on the left hand side
- Go to Client secrets tab
- Click on + New client secret
- Provide a Description of this secret and choose an Expiry date.
- Copy the Client secret which is under Value column. This value will only show for few minutes and after that it will not be available for copying. So please make sure to copy client secret value as soon as you generate it.
After configuring API Permissions and creating a client secret. You will need below Information to be used in the powershell script:
- Client ID of the application – Open the application, Go to Overview tab and copy Application (client) ID value.
- Client secret value – Go to Certificates & secrets > Copy the value of secret.
- Azure AD tenant ID information – Login on Microsoft Entra admin center > On Overview page you will be able to find Azure AD tenant’s ID. Copy tenant ID into a notepad.
Once you have all the information, you will need to replace it in below powershell variables in 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
Powershell script to Export Last Sign-in date time Info into a CSV file using Microsoft Graph
<#
.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 Azure AD 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
There are built-in reports also available from Microsoft 365 admin center under Reports to find out Microsoft 365 apps Active Users. To access the report, please follow below steps:
- Login on Microsoft 365 admin center
- Go to Reports > Usage
- Under Product Reports > Click on Microsoft 365 apps
- Click on Active Users tab to find out Last activity date of Microsoft 365 apps for all users.
For each user, you will find below information which can also be Exported into a CSV file using 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 have seen different ways to Export Last Sign-in date time information about a user in Microsoft 365. You can also use Azure AD sign in logs to check this Information and also by using Get-AzureADAuditSignInlog cmdlet. After you get the list of these users, You can filter the report and check the users who have not signed in for a long period of time.
This report also shows if the Microsoft 365 user account status is Enabled or disabled, If a user account is Enabled and has not logged on to Microsoft 365 for let’s say 1 year then you can check with user’s manager to confirm if user account can be disabled.