Microsoft Azure

How to: Get stale AAD users

Every organization sometimes ask the question, can I get a list of all users with LastSignInDate? and than you say yes, sure! But how (do you think)? Your search ends here, we can get the LastSignInDate with MsGraph! I tell you how.

Option 1: Microsoft Graph PowerShell

Microsoft Graph can be reached with the Graph Explorer, an app registration (later more) and with Microsoft Graph PowerShell. Both Graph Explorer and Microsoft Graph PowerShell are Enterprise Application in the tenant. Each scope you request need to be consented in the Enterprise Application. To get the SignInActivity and information like UserPrincipalName, we need to consent the following scopes: ‘AuditLog.Read.All’, ‘Directory.Read.All’. But before you run the script you see below, you first need to install Microsoft Graph.

Install-Module Microsoft.Graph -Scope AllUsers

When installed, you can run the following script to obtain all users with their LastSignInDate information.

Connect-MgGraph -Scopes 'AuditLog.Read.All','Directory.Read.All'
Select-MgProfile beta
$Users = Get-MgUser -All | Select-Object UserPrincipalName,ID,DisplayName
foreach($User in $Users)
    $LastSignInDateTime = Get-MgUser -UserId $User.ID -Select SignInActivity | Select-Object -ExpandProperty SignInActivity
    $OutputTable = [ordered]@{
        Displayname        =  $User.DisplayName
        UserPrincipalName  =  $User.UserPrincipalName
        LastSignInDateTime =  $LastSignInDateTime.LastSignInDateTime
    $OutputTableObject = New-Object -Type PSObject -Property $OutputTable
    $Result += $OutputTableObject
$Result | Export-Csv "C:\Temp\$((Get-Date).ToString("yyyyMMdd"))_aad-stale-users.csv" -NoTypeInformation


If you are applying the above scopes for the first time, you will have to grant consent as mentioned before.

Option 2: Do a WebRequest to Microsoft Graph by using an app registration in Azure AD

  1. Make an app registration in Azure AD
  2. Go to: API permissions -> Add a permission -> Microsoft Graph -> Application permissions
  3. Select: User.Read.All, Directory.Read.All, Auditlogs.Read.All
  4. Give consent
  5. Certificates & secrets -> New client setcret
  6. Note the Value of the secret and App ID

Note: You can use the app registration for any scopes, but you can also scope per app registration. Check out the complete list of Microsoft Graph permissions. I personally use a read all and separate write app registration per purpose.

When the app registration is created you can run the following script:

#App Registration and Azure AD information
$clientID     = "xxxx-xxxx-xxx-xxx-xxxxxxxxxxxx"    #  <-insert the applicaiton ID here
$clientSecret = Get-Content -Path 'C:\temp\APISecret.txt' #  <-insert the secret here
$tenantDomain = ""                  #  <-insert your domain here
#Access token:
$loginURL     = ""
$resource     = ""
$body         = @{grant_type="client_credentials";resource=$resource;client_id=$ClientID;client_secret=$ClientSecret}
$oauth        = Invoke-RestMethod -Method Post -Uri $loginURL/$tenantdomain/oauth2/token?api-version=1.0 -Body $body
$headerParams = @{'Authorization'="$($oauth.token_type) $($oauth.access_token)"}
$userList = @()
$url = '$select=displayname,userprincipalname,signInActivity&$top=999'
While ($Null -ne $url) {
    $data = (Invoke-WebRequest -Headers $headerParams -Uri $url) | ConvertFrom-Json
    $userList += $data.Value
    $url = $data.'@Odata.NextLink'

$userList | Select-Object DisplayName,userPrincipalName,@{n="LastLoginDate";e={$_.signInActivity.lastSignInDateTime}}|
Export-Csv "C:\Temp\$((Get-Date).ToString("yyyyMMdd"))_aad-stale-users2.csv" -NoTypeInformation

After I’m done, I delete the client secret, but if desirable you can import this to a KeyVault and do an import from the KeyVault in the script.

By default you get only 100 max records, and the most tenants have more than 100 users. With using the $top parameter and looking for the @odata.nextLink property in the results we can export a complete list. This method calling paging, which is described here:

What does a blank property value mean in the above options export?

To generate a lastSignInDateTime timestamp, you need a successful sign-in. Because the lastSignInDateTime property is a recent feature, the value of the lastSignInDateTime property can be blank if:

  • The last successful sign-in of a user took place before April 2020.
  • The affected user account was never used for a successful sign-in.

For how long is the last sign-in retained?

The last sign-in date is associated with the user object. The value is retained until the next sign-in of the user.

Share this: