We can finally report on last successful login timestamp in Entra ID

Today’s article will be a short one. In a small, but meaningful update, Microsoft has released a new addition to the signInActivity resource, which allows us to determine the last time a given user was able to sign in successfully to the service (“last successful login timestamp”). Until recently, we were only able to get the last login timestamp, regardless of whether is was a success or failure login attempt. Here’s the changelog entries and links to the resource documentation:

Added the lastSuccessfulSignInDateTime property to the signInActivity resource.
Added the lastSuccessfulSignInRequestId property to the signInActivity resource.

The signInActivity resource itself has been available for years now, though its GA status was achieved more recently. While very useful, it does have its quirks, such as the requirement for Azure AD Premium license, the need to also have Audit permissions and the aforementioned fact that it covered both failed and successful logins. A more convoluted quirk was discussed in this article. With the new additions, there is one more thing you need to be aware of, namely that values are not retroactively filled in, so you can expect to see null entries. As users log in, we will start getting the corresponding timestamps and requestIds.

In terms of getting the data, nothing has changed. The methods and requirements are all the same, and we can use either direct API queries or leverage the Graph SDK for PowerShell (beta) cmdlets. For example, to list all users within the tenant along with their signInActivity data, you can use the following:

GET https://graph.microsoft.com/beta/users?$select=displayName,userPrincipalName,signInActivity

To get the same data via PowerShell, we can use the Get-MgBetaUser cmdlet from the (beta) Graph module. Looking at the same thing multiple times is a bit boring though, so let’s instead examine how we can filter based on the newly introduced lastSuccessfulSignInDateTime property. The example below will filter only users with last successful login within the past month. At the time of writing this article, the results will be skewed, due to the aforementioned lack of retroactive data population. Going forward, the filter should work as expected.

Get-MgBetaUser -Filter "signInActivity/lastSuccessfulSignInDateTime ge $([datetime]::UtcNow.AddDays(-30).ToString("s"))Z" -All:$true

DisplayName        Id                                   Mail                             UserPrincipalName
-----------        --                                   ----                             -----------------
Vasil Michev       584b2b38-888c-4b85-8871-c9766cb4791b vasil@michev.info                vasil@michev.info
Gosho              064abb3c-0812-44f9-bdcc-eea7e6ea398b gosho@michev.info                gosho@michev.info

The funny part here is that the stupid Graph module doesn’t output the lastSuccessfulSignInDateTime value currently, yet as the underlying endpoints do support it, the filter works as expected. Until the module is updated, you cannot even use client-side filters, as it simply omits the two new properties. Once the module is updated, you will be able to list all users with at least one successful login (i.e. with non-null lastSuccessfulSignInDateTime) by the cmdlet below. Make sure you specifically request the SignInActivity property though. Anyway, here’s the example:

Get-MgBetaUser -All:$true -Property DisplayName,Id,UserPrincipalName,SignInActivity | ? {$_.signInActivity.lastSuccessfulSignInDateTime -ne $null} | select DisplayName,UserPrincipalName,@{n="LastSuccessfulSignIn";e={$_.signInActivity.lastSuccessfulSignInDateTime}}

In the meantime, you can use the Invoke-MgGraphRequest cmdlet instead:

$res = Invoke-MgGraphRequest -Uri "https://graph.microsoft.com/beta/users?`$select=id,displayName,userPrincipalName,signInActivity" -OutputType PSObject

$res.value | ? {$_.signInActivity.lastSuccessfulSignInDateTime -ne $null} | select DisplayName,UserPrincipalName,@{n="LastSuccessfulSignIn";e={$_.signInActivity.lastSuccessfulSignInDateTime}}

displayName        userPrincipalName                                            LastSuccessfulSignIn
-----------        -----------------                                            --------------------
Dora               Dora@michev.info                                             07/12/23 06:58:47
Gosho              gosho@michev.info                                            22/11/23 14:52:28