Find Last login date for all Azure AD Users using PowerShell

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

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.

Find Last login date for all Office 365 Users without using PowerShell
Advertisement

36 thoughts on “Find Last login date for all Azure AD Users using PowerShell”

  1. 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!

    Reply
    • 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.

      Reply
  2. 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.

    Reply
  3. 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

    Reply
  4. 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

    Reply
  5. 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

    Reply
  6. 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?

    Reply
    • 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.

      Reply

Leave a Comment