PowerShell: Getting all Azure AD User IDs Last Login date and Time

As part of a recent project, I needed to check the last login time for all the Azure AD Users. We basically needed to see which IDs were being used and which weren’t. I assumed that this would be easy, but it turned out that there is no attribute in Azure AD for the User’s last login date or time.

The login information is stored in the Azure SignIn logs, which can be accessed from the Azure Console, so it is available, but you have to search for the information you want, and it is not straightforward. It is also not practical for thousands of users.

I have therefore developed a short PowerShell script that will pull back all the information required. I was also looking at License information so this script pulls that back for each user too.

First of all two Modules need to be installed.

Install-Module -Name Msonline
Install-Module -Name AzureADPreview -allowclobber

The Ms0nline module provides the commands to access the Azure AD User objects. The AzureADPreview module provides the command to access the Azure AD audit logs.

We then have to connect to the Msonline and AzureAD services.

$Cred = Get-Credential
Connect-MsolService -Credential $Cred
Connect-AzureAD -Credential $Cred

We now pull back all the users into an array and set the headers for the txt file, using ‘t” as a Tab separator (this makes the data easier to use in Excel).

$Users = Get-MsolUser -all
$Headers = "DisplayName`tUserPrincipalName`tLicense`tLastLogon" >>C:\Temp\Users.txt

The Get-MsolUser CmdLet comes from the Msonline module.

To get the Users last login time we use Get-AzureAdAuditSigninLogs, from the AzureADPreview module, filtering on the UserPrincipalName. -top 1 brings back the latest record, from which the CreatedDateTime attribute is selected.

$UPN = $User.UserPrincipalName
    $LoginTime = Get-AzureAdAuditSigninLogs -top 1 -filter "userprincipalname eq '$UPN'" | select CreatedDateTime

The code below takes the information we have gathered so far and adds it, Tab separated, to a variable. The variable is then written to the txt file.

$NewLine = $User.DisplayName + "`t" + $User.UserPrincipalName + "`t" + $User.Licenses.AccountSkuId + "`t" + $LoginTime.CreatedDateTime
$NewLine >>C:\Temp\Users.txt

The completed script, including a ForEach loop to loop through all the Users, is shown below.

$Cred = Get-Credential
Connect-MsolService -Credential $Cred
Connect-AzureAD -Credential $Cred

$Users = Get-MsolUser -all
$Headers = "DisplayName`tUserPrincipalName`tLicense`tLastLogon" >>C:\Temp\Users.txt
ForEach ($User in $Users)
    {
    $UPN = $User.UserPrincipalName
    $LoginTime = Get-AzureAdAuditSigninLogs -top 1 -filter "userprincipalname eq '$UPN'" | select CreatedDateTime
    $NewLine = $User.DisplayName + "`t" + $User.UserPrincipalName + "`t" + $User.Licenses.AccountSkuId + "`t" + $LoginTime.CreatedDateTime
    $NewLine >>C:\Temp\Users.txt
    }

This script is not optimised for speed, so running it against a very large Azure AD Tenant will take a considerable amount of time, but it is not the sort of script that needs to be run often, and it gets the job done.

Bear in mind that you will need to be a member of the correct Azure AD roles to be able to successfully run this script, for example Global Reader.