PowerShell: Export Last Login Date of Entra ID Users

Recently, I was tasked with finding the most recent sign-in information of all Entra ID/Microsoft 365 users in my organization and exporting it in a CSV file. Using this report, we wanted to get a list of users who have not connected to Entra ID or used any Microsoft 365 services for a long time and deactivate their accounts. The scenario could be different in your case, however this report is really useful to list inactive users.

In this blog post, we will explore different methods for listing and exporting Entra ID user’s last login date and time information. We will use GUI method and also powershell cmdlets like Get-AzureADAuditSignInLogs, included in AzureADPreview PS module. This cmdlet enables us to fetch the sign-in logs of Entra ID users.

Powershell cmdlet Get-AzureADAuditSignInLogs can export all Sign-in data for an Entra ID user. I have listed some of the relevant sign-in data we can retrieve for a user:

• User’s Object Id: Entra ID User’s Object ID information.
• Last Sign-in Date and Time: This is the most recent sign-in log date and time information. The property name is CreatedDateTime.
• User’s Display Name: Users display name.
• UserPrincipalName: User Principal Name information of the user. For example jatin.makhija@cloudinfra.net
• Application Display Name: Which application is being used for Authentication? For example: When logging on to a Windows device, It will show as Windows Sign In.
• Device ID: The user will sign in from a device to authenticate to Entra ID; that device ID will be recorded in the sign-in log.
• Device Operating system: Device Operating System is used for sign-in. For example: Windows, macOS, etc.
• Device Compliance status: Compliance Status of the device. For example, IsCompliant: True or False.
• Device Display Name: Device display name as it shows in Entra ID. For example Cloudinfra-net1
• Any Authentication Failure Reason: If you want to find out the reason for an authentication failure, you can check this information.
• IP Address: The device IP Address is also recorded in sign-in logs and retrievable.
• Client App Used: Check if a thick or thin client is used to log on to Entra ID. This could be a Browser app, Outlook app, Mobile app, or Desktop client.

1. Get Last Login Date/Time from Entra admin center

To obtain the last login date and time information from Entra admin center, please follow these steps:

• Sign in to the Entra admin center > Users > All users.
• Click on the the user for which you want to find Sign-in information.
• Click on Sign-in logs on the left-hand side.
• To identify the most recent log entry, look at the top of the list and check the Date column, which displays the user’s last sign-in date-timestamp.

2. Get Last Login Date/Time Using Powershell [For One Entra ID User]

Checking the Last Login Information for one Entra ID user through the Entra admin center is straightforward. However, GUI is not the fastest way to get the data. Therefore, we will now retrieve last login date and time information for a user using Powershell.

Before running Get-AzureADAuditSignInLogs PowerShell cmdlet, you must install the Azure AD Preview PS module and connect to Entra ID. Let’s check the steps:

Install AzureADpreview PowerShell module

Install-module -name AzureADpreview -Scope CurrentUser

Connect to Entra ID/AzureAD

Connect-AzureAD

Get the Last Login date time of an Entra ID user

To get the last login date and time of an Entra ID user, use below powershell command. Replace the userprincipalname (UPN) value to your organization user’s UPN value and execute the command. You will get UserDisplayName and CreatedDateTime values. CreatedDateTime value represents User’s Sign in date/timestamp.

Get the Last Login date time of an Entra ID user

Get-AzureADAuditSignInLogs -Filter "startsWith(userPrincipalName,'adelev@cloudinfra.net')" -top 1 | Select UserDisplayName, CreatedDateTime

3. Export Last Login Date/Time of Entra ID Users to CSV Using Powershell [For a List of Users]

Checking the last login details for a single user with a quick PowerShell command is easy. You can also fetch the last login date and time information for a list of users in a text file. I have created a PowerShell script that will loop through each user given in a text file and fetch the sign-in data. In the end, it will export the data in a CSV file.

• Before running the script, ensure you have installed the AzureADPreview module and connected to Entra ID using the Connect-AzureAD cmdlet.
• Gather user’s UPN in a text file. You can utilize Get-MsolUser to get user UPN information.
• Update $UPNlist variable in the script to point to the path of the text file containing users UPN information. For example:

$UPNlist

$UPNlist = Get-Content <path_of_user_upn_text_file>

• By default, the exported data is saved in a file named LastLogininfo.csv in the C:\temp directory. However, you can modify the script to store the report in a different location.
• If a user has never logged in, the lastlogindate column in the report will display Never Logged In to indicate that no previous login activity has been recorded for that user.
• Copy below powershell code and save it in a file with a .ps1 extension, for example, ExportLastlogin.ps1. Then, execute the script to Export the data.

ExportLastlogin.ps1

<#
.DESCRIPTION
    This script Export users Last Sign in date time from AzureAD.
    Copy the UPNs and provide the path of the text file in $UPNlist 
    variable.
    Author:  Jatin Makhija
    Site:    cloudinfra.net
    Version: 1.0.0
#>
$UPNlist = Get-Content C:\output\upn.txt
$object  = Foreach ($upn in $upnlist) {
  try {
      $signindata = Get-AzureADAuditSignInLogs -Filter "startsWith(userPrincipalName, '$upn')" -top 1
      if ($signindata -eq $Null)
          {
          [PSCustomobject]@{
             UserdisplayName = $upn
             LastLoginDate = "Never Logged in"
          }  
      }
      Else{
           [PSCustomobject]@{
             UserdisplayName = $signindata.UserDisplayName
             LastLoginDate = $signindata.CreatedDateTime
            }
      }
}
      Catch {
        Write-Error $_
      }
}
$object | Export-csv c:\temp\LastLogininfo.csv -NoTypeInformation

• Here is an example of a report retrieved using the above script.

4. Export Last Login Date/Time to CSV Using Powershell [for All Entra ID Users]

In the previous section, we have learnt the steps to export last login date and time stamp information for a list of Entra ID users given in a text file. Now, we will export this information for All Entra ID users using a powershell script.No changes are required in the script, but there are some important steps you need to take before executing the script.

• Before running the script, ensure you have installed the AzureADPreview module and connected to Entra ID using the Connect-AzureAD cmdlet.
• Install MSOnline Powershell module using Install-module -Name MSOnline.
• Connect to MSOnline using Connect-MsolService cmdlet.
• Exported data will be stored in C:\temp\LastLogininfo.csv file. You can change the location in the script if you want to save the report in a different location.
• If the User has never logged on, the LastLoginDate column will show Never Logged In.
• Copy below powershell code and save it in a file with a .ps1 extension, for example, ExportLastlogin.ps1. Then, execute the script to Export the date.

ExportLastlogin.ps1

<#
.DESCRIPTION
    This script Export users Last Sign in date time from AzureAD.
    Copy the UPNs and provide the path of the text file in $UPNlist 
    variable.
    Author:  Jatin Makhija
    Site:    cloudinfra.net
    Version: 1.0.0
#>
$allUsers = Get-MsolUser -all
$object  = Foreach ($upn in $allUsers.userprincipalname) {
  try {
      $signindata = Get-AzureADAuditSignInLogs -Filter "startsWith(userPrincipalName, '$upn')" -top 1
      if ($signindata -eq $Null)
          {
          [PSCustomobject]@{
             UserdisplayName = $upn
             LastLoginDate = "Never Logged in"
          }  
      }
      Else{
           [PSCustomobject]@{
             UserdisplayName = $signindata.UserDisplayName
             LastLoginDate = $signindata.CreatedDateTime
            }
      }
}
      Catch {
        Write-Error $_
      }
}
$object | Export-csv c:\temp\LastLogininfo.csv -NoTypeInformation

More Information

1. Get-AzureADAuditSignInLogs is not recognized

You might encounter an error message when retrieving user’s last login information using the Get-AzureADAuditSignInLogs cmdlet or when executing a script that utilizes this cmdlet. The error message you may encounter is as follows:

Get-AzureADAuditSignInLogs : The term 'Get-AzureADAuditSignInLogs' is not recognized as the name of a cmdlet,
function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the
path is correct and try again.
At line:1 char:1
+ Get-AzureADAuditSignInLogs
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (Get-AzureADAuditSignInLogs:String) [], CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException

Please find below guidance on resolving this error:

• To resolve the error, install the AzureADPreview PowerShell module on your device. If it’s not already installed, use the Install-module -name AzureADpreview -Scope CurrentUser command.

• If you encounter any issues, try running the command as an administrator with the -AllowClobber and Force flags like this: Install-Module -Name AzureADPreview -AllowClobber -Force on a PowerShell console.

• To verify whether the AzureADPreview PowerShell module has been successfully installed, run the following command: get-command *azureadaudit* to find out if Get-AzureADAuditSignInLogs cmdlet is available.

• It’s important to note that even if the AzureADPreview module has been installed successfully, you can still encounter the same error message if you don’t have the necessary permissions to read Entra ID sign-in logs. The error message, Get-AzureADAuditSignInLogs is not recognized may persist due to insufficient permissions to access this specific feature.

• You can try elevating your permissions to resolve this issue. If possible, grant yourself global administrator rights and test if you still encounter the same error message when using the Get-AzureADAuditSignInLogs cmdlet. Often, this issue can be resolved by having the appropriate administrative privileges to access Entra ID sign-in logs.

Conclusion

In this blog post, we’ve covered the steps for exporting the last login information for Entra ID users into a CSV file. You can also list the last login information directly on the PowerShell console for a quick view by removing/commenting Export-CSV cmdlet in the script.

If you encounter any issues, please ensure you’ve connected to AzureAD and the MSOnline service before running the script.

Read Next

Export Microsoft 365 Blocked/Disabled Users Report

Powershell: Export Microsoft 365 User Licenses to CSV