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

frob 4,216 Reputation points
2023-04-13T20:51:24.1033333+00:00

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

PowerShell
PowerShell
A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
2,161 questions
Microsoft Entra ID
Microsoft Entra ID
A Microsoft Entra identity service that provides identity management and access control capabilities. Replaces Azure Active Directory.
19,849 questions
{count} votes

Accepted answer
  1. Dillon Silzer 54,751 Reputation points
    2023-04-14T05:17:52.2166667+00:00

    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.

    1 person found this answer helpful.
    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Ian Xue (Shanghai Wicresoft Co., Ltd.) 31,166 Reputation points Microsoft Vendor
    2023-04-17T03:50:19.03+00:00

    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.

    0 comments No comments

  2. JamesTran-MSFT 36,481 Reputation points Microsoft Employee
    2023-04-17T21:16:14.6833333+00:00

    @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.User's image

    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.