What PowerShell script can list all Azure AD users along with their last login date, in a CSV?

Question

Hi there What PowerShell script can list all Azure AD users along with their last login date, in a CSV? Thanks.

Answer 1

Hello frob,

I'd recommend to familiarize yourself with Graph API.

You can accomplish this quite easily with Graph API:

Example 3: Get users including their last sign-in time

https://learn.microsoft.com/en-us/graph/api/user-list?view=graph-rest-beta&tabs=http#example-3-get-users-including-their-last-sign-in-time

Then you can use JSON to CSV:

https://www.convertcsv.com/json-to-csv.htm

---

If this is helpful please accept answer.

Answer 2

Hi,

You can use Get-AzureADAuditSignInLogs to get the sign ins.

https://learn.microsoft.com/en-us/powershell/module/azuread/get-azureadauditsigninlogs?view=azureadps-2.0-preview

Best Regards,

Ian Xue

If the Answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

@frob

Thank you for your post and I apologize for the delayed response!

I understand that you want a PowerShell script that can list all Azure AD users along with their last login date and have that info exported to a CSV file. To do this, you can run the following PowerShell script in order to download all your Azure AD users to include their last login date.

Prior to running the below script, please make sure you installed the appropriate Azure AD and AzureADPreview modules

#To install the General Availability version of the module, run:
Install-Module AzureAD


#For more info on AllowClobber - https://learn.microsoft.com/en-us/powershell/module/powershellget/install-module?view=powershellget-2.x#-allowclobber

#To install the public preview release, run:
Install-module AzureADPreview -AllowClobber "-Force (Optional)"

#Import the AzureADPreview Module:
Import-Module AzureADPreview

#To check the version of the module installed on your computer run this command:
Get-Module AzureADPreview

Download all Azure AD Users and their Last Login Date:

#Connect to Azure AD
Connect-AzureAD

# If you want to test this script on one user
# $users = Get-AzureADUser -ObjectId "<ObjectID>"

# Get all Azure AD users
$users = Get-AzureADUser -All $true


# Create an empty array to store user data
$userData = @()

# Loop through each user and get their last login date
foreach ($user in $users) {
    $signInActivity = Get-AzureADAuditSignInLogs -Top 1 -Filter "userPrincipalName eq '$($user.UserPrincipalName)'" | Sort-Object -Property CreatedDateTime -Descending
    $lastLoginDate = $signInActivity.CreatedDateTime
    $userData += [PSCustomObject]@{
        UserPrincipalName = $user.UserPrincipalName
        DisplayName = $user.DisplayName
        LastLoginDate = $lastLoginDate
    }
}

# Export user data to CSV
$userData | Export-Csv -Path "C:\Users\<UserName>\Desktop\AzureADUsers-$(Get-Date -format "MM-dd-yyyy").csv" -NoTypeInformation

Note: For testing purposes, I only demonstrated this using my user.

I hope this helps!

If you have any other questions, please let me know. Thank you for your time and patience throughout this issue.

---

If the information helped address your question, please Accept the answer. This will help us and also improve searchability for others in the community who might be researching similar information.