The user’s last successful sign-in time provides potential insights into the user’s continued need for access to Microsoft 365 resources. As an IT administrator, you want to detect and handle obsolete or inactive user accounts. It can also help with determining if group membership or app access is still needed for inactive users.
The Microsoft Graph API now supports the resource type signInActivity in users end-point, this resource exposes the lastSignInDateTime property which shows the last time a user made a successful interactive sign-in to Azure AD. Fetching signInActivity property requires an Azure AD Premium P1/P2 license and the AuditLog.Read.All permission. The following request retrieves user details along with signInActivity property.
#GET Request
https://graph.microsoft.com/beta/users?$select=displayName,signInActivity
Up until now, this is the only possible way to get the last sign-in date for users. Before Microsoft Graph supports this property, we need to either get the mailbox last logon time using the Get-MailboxStatistics cmdlet or we need to crawl the Azure AD sign-in logs or the Unified audit logs in the Security and Compliance Center. We can get the signInActivity property only by querying the Graph API directly. Until now, the Graph API powered AzureAD PowerShell V2 (AzureAD /AzureADPreview) module also does not expose this property.
Summary
- Get Graph API Access Token
- Export Last login date for all Microsoft 365 Users
- Find Inactive Azure AD users
- List Licensed users/Guest users with last login date
- Export Last Login date without using PowerShell
Get Graph API Access Token
We can use the MSAL.PS library to acquire access tokens with Delegated permissions. Run the following command in PowerShell to install this module. You can close the PowerShell and re-open it once you installed the module.
Install-Module -Name MSAL.PS
Run the following commands to get Access Token for the delegated permissions “AuditLog.Read.All” and “User.Read.All“.
#Provide your Office 365 Tenant Domain Name or Tenant Id
$TenantId = "contoso.onmicrosoft.com"
#$TenantId = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"
#Used the Microsoft Graph PowerShell app id. You can create and use your own Azure AD App id if needed.
$AppClientId="14d82eec-204b-4c2f-b7e8-296a70dab67e"
$MsalParams = @{
ClientId = $AppClientId
TenantId = $TenantId
Scopes = "https://graph.microsoft.com/User.Read.All","https://graph.microsoft.com/AuditLog.Read.All"
}
$MsalResponse = Get-MsalToken @MsalParams
$AccessToken = $MsalResponse.AccessToken
Export Last login date for all Microsoft 365 Users
Once you have acquired the required Graph access token, you can easily retrieve the last sign-in date time for all Azure AD users by querying the Graph API directly from PowerShell. The following commands fetch all users with the properties LastSignInDateTime and LastNonInteractiveSignInDateTime and export the result to a CSV file.
#Provide your access token.
#$AccessToken="eyJ0eXAiOiJ......"
#Form request headers with the acquired $AccessToken
$headers = @{"Content-Type"="application/json";"Authorization"="Bearer $AccessToken"}
#This request get users list with signInActivity.
$ApiUrl = "https://graph.microsoft.com/beta/users?`$select=displayName,userPrincipalName,signInActivity,userType,assignedLicenses"
$Result = @()
While ($ApiUrl -ne $Null) #Perform pagination if next page link (odata.nextlink) returned.
{
$Response = Invoke-RestMethod -Method GET -Uri $ApiUrl -ContentType "application/json" -Headers $headers
if($Response.value)
{
$Users = $Response.value
ForEach($User in $Users)
{
$Result += New-Object PSObject -property $([ordered]@{
DisplayName = $User.displayName
UserPrincipalName = $User.userPrincipalName
LastSignInDateTime = if($User.signInActivity.lastSignInDateTime) { [DateTime]$User.signInActivity.lastSignInDateTime } Else {$null}
LastNonInteractiveSignInDateTime = if($User.signInActivity.lastNonInteractiveSignInDateTime) { [DateTime]$User.signInActivity.lastNonInteractiveSignInDateTime } Else { $null }
IsLicensed = if ($User.assignedLicenses.Count -ne 0) { $true } else { $false }
IsGuestUser = if ($User.userType -eq 'Guest') { $true } else { $false }
})
}
}
$ApiUrl=$Response.'@odata.nextlink'
}
$Result | Export-CSV "C:\Temp\LastLoginDateReport.CSV" -NoTypeInformation -Encoding UTF8
Find Inactive Azure AD users
The above commands store the details in the array object “$Result“, we can filter the result and generate different reports. The following command returns inactive Microsoft 365 users who are not logged-in in the last 90 days.
$DaysInactive = 90
$dateTime = (Get-Date).Adddays(-($DaysInactive))
$Result | Where-Object { $_.LastSignInDateTime -eq $Null -OR $_.LastSignInDateTime -le $dateTime }
Find last login date for Licensed users
Run the following command to list only licensed Office 365 users.
$Result | Where-Object { $_.IsLicensed -eq $true }
Find last sign-in time for Guest users
Run the following command to list all guest users.
$Result | Where-Object { $_.IsGuestUser -eq $true }
Export M365 Users’ Last Sign-In Date Without Using PowerShell
Would you like to retrieve the last sign-in date of Office 365 Users without relying on PowerShell scripts? We suggest exploring Specmasoft‘s desktop-based Microsoft 365 reporting and management tool. This solution offers a variety of pre-configured reports, enabling you to easily monitor essential information, such as the Last Login Date, for your Office 365 users with minimal effort. Take advantage of a 15-day Free Trial to experience the convenience and efficiency of this tool.
I don’t understand why we have to do so many things to get very simple data.
Unbelievable.
Hopefully we will get this feature through the Get-AzureADUser cmdlet to simplify the work.
Hello Megan,
I got a different error from Ryan and Amjed:
Invoke-WebRequest : The remote server returned an error: (503) Server Unavailable.
At line:18 char:13
+ $Response = Invoke-WebRequest -Method GET -Uri $ApiUrl -ContentType ” …
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-WebRequest], WebException
+ FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeWebRequestCommand
Another two questions:
1. How to run it on just a list of ~800 users?
2. IS it possible to run it on 20,000 users? Should I just switch $top=999 to $top=20,000?
Thank you!
Please disregard my previous question. Everything works as expected 🙂
I was able to export the results however for some accounts the LastSigninDateTime shown as 01-01-0001 05:30:00. What does it mean?
This indicates that the user account does not make any successful Interactive sign request. But the account may be used for Non-Interactive sign-in.
Hi again,
My own mistake.
Just for the record: A $ prefix needs to be escaped, in PowerShell with “`”
Cheers,
Sandra
As it stands, this only exports ‘Interactive’ sign-ins from the Graph API – which didn’t work for my needs.
To add non-interactive sign ins, I added the following line to the Result Object under LastSignInDateTime:
LastNonInteractiveSignIn = if($User.signInActivity.lastNonInteractiveSignInDateTime) { [DateTime]$User.signInActivity.lastNonInteractiveSignInDateTime } Else { $null }
That way, I can see the last time a user account interacted with our tenant, even if they weren’t actively signing in.
Thanks, updated the same in the post.
I am receiving the following error and cant figure out where to make the change.
PS C:\Windows\System32> $MsalResponse = Get-MsalToken @MsalParams
Get-MsalToken: C:\Program Files\WindowsPowerShell\Modules\MSAL.PS\4.37.0.0\Get-MsalToken.ps1:314
Line |
314 | … ionResult = Get-MsalToken -Interactive -PublicClientApplication $Publ …
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| A configuration issue is preventing authentication – check the error message from the server for
| details. You can modify the configuration in the application registration portal. See
| https://aka.ms/msal-net-invalid-client for details. Original exception: AADSTS7000218: The request
| body must contain the following parameter: ‘client_assertion’ or ‘client_secret’. Trace ID:
| a793d004-8926-4e73-800b-99b158ae4e00 Correlation ID: 5fffb381-4fdb-4f05-b695-74339fe6c193 Timestamp:
| 2022-08-23 17:47:46Z
PS C:\Windows\System32> $AccessToken = $MsalResponse.AccessToken
Can you confirm that you have created the Azure AD app as a Public client app?. The public client does not require the client secret code. You can refer to the below post for more details.
https://morgantechspace.com/2021/10/how-to-register-and-configure-azure-ad-application-from-azure-ad-portal.html
this might show it better
https://graph.microsoft.com/beta/users?$select=displayName,signInActivity
the issue is the comma before the signInActivity
PS C:\WINDOWS\system32> https://graph.microsoft.com/beta/users?$select=displayName,signInActivity
At line:1 char:59
+ https://graph.microsoft.com/beta/users?$select=displayName,signInActi
+ ~
Missing argument in parameter list.
+ CategoryInfo : ParserError: (:) [], ParentContainsErrorRecordException
+ FullyQualifiedErrorId : MissingArgument
I must be missing a step – when it gets to $MsalResponse = Get-MsalToken @MsalParams it has trouble logging me in even though I can use other commands to log in with the same username and password. I have global admin rights
Can you share the error message that you received?
Graph API is not giving both interactive and non-interactive sign in in single query. Above given code is working completely fine but logically gives wrong output. If I have done non-interactive signing, then this graph API query will not show my sign in and perhaps system will consider non-logged in user.
has anyone try to mitigate this issue?
Can you generate Sign-in logs report from the Azure AD portal and check your sign-in activity? If the sign-in details are available in this report, then we should get the same in Graph API.