Export Azure AD users to CSV with PowerShell

We want to export Azure AD users information to CSV with PowerShell. Why do we need to list the Azure AD users with PowerShell? For example, we want to know if every Azure AD user has the correct attributes in Azure Active Directory. That’s because the service desk needs this information. In this article, you will learn how to export Azure Active Directory users to CSV file with PowerShell.

Information export Azure AD users PowerShell script

The Export-AADUsers.ps1 PowerShell script will run against the Azure tenant. After that, it will export the report to CSV file. You can open the CSV file with Microsoft Excel or any other application that supports the CSV file extension.

The script will gather the following information per user:

1. ID
2. First name
3. Last name
4. Display name
5. User principal name
6. Domain name
7. Email address
8. Job Title
9. Manager display name
10. Manager user principal name
11. Department
12. Company
13. Office
14. Employee ID
15. Mobile
16. Phone
17. Street
18. City
19. Postal code
20. State
21. Country
22. User type
23. On-Premises sync
24. Account status
25. Account created on
26. Last successful sign in (requires an Azure AD P1/P2 license)
27. Licensed
28. MFA status (including authentication methods)

Export Azure Active Directory users to CSV with PowerShell

Let’s go through the steps and export Azure Active Directory users to CSV file with PowerShell.

Step 1. Install Microsoft Graph PowerShell

Run Windows PowerShell as administrator and Install Microsoft Graph PowerShell.

Install-Module Microsoft.Graph -Force

Install-Module Microsoft.Graph.Beta -AllowClobber -Force

Important: Always install the Microsoft Graph PowerShell and Microsoft Graph Beta PowerShell modules. That’s because some cmdlets are not yet available in the final version, and they will not work. Update both modules to the latest version before you run a cmdlet or script to prevent errors and incorrect results.

Step 2. Connect to Microsoft Graph PowerShell

Connect to Azure Active Directory (AAD) with Microsoft Graph PowerShell.

Connect-MgGraph -Scopes "User.Read.All", "UserAuthenticationMethod.Read.All", "AuditLog.Read.All"

Enter your global administrator credentials and accept the Microsoft Graph permissions request.

Step 3. Prepare export Azure AD users PowerShell script

Create two folders on the (C:) drive:

• Temp
• Scripts

Download and place Export-AADUsers.ps1 PowerShell script in C:\scripts folder. The script will export the CSV file to the C:\temp folder.

Ensure the file is unblocked to prevent errors when running the script. Read more in the article Not digitally signed error when running PowerShell script.

Another option is to copy and paste the below code into Notepad. Give it the name Export-AADUsers.ps1 and place it in the C:\scripts folder.

<#
    .SYNOPSIS
    Export-AADUsers.ps1

    .DESCRIPTION
    Export Azure Active Directory users to CSV file.

    .LINK
    www.alitajran.com/export-azure-ad-users-to-csv-powershell

    .NOTES
    Written by: ALI TAJRAN
    Website:    www.alitajran.com
    LinkedIn:   linkedin.com/in/alitajran

    .CHANGELOG
    V1.10, 06/20/2023 - Initial version
    V1.10, 06/21/2023 - Added license status and MFA status including methods
    V1.20, 06/22/2023 - Added progress bar and last sign in date
    V1.30, 07/24/2023 - Update for Microsoft Graph PowerShell changes
    V1.40, 04/07/2024 - Added domain name
#>

# Connect to Microsoft Graph API
Connect-MgGraph -Scopes "User.Read.All", "UserAuthenticationMethod.Read.All", "AuditLog.Read.All"

# Create variable for the date stamp
$LogDate = Get-Date -f yyyyMMddhhmm

# Define CSV file export location variable
$Csvfile = "C:\temp\AllAADUsers_$LogDate.csv"

# Retrieve users using the Microsoft Graph API with property
$propertyParams = @{
    All            = $true
    # Uncomment below if you have Azure AD P1/P2 to get last sign in date
    # Property = 'SignInActivity'
    ExpandProperty = 'manager'
}

$users = Get-MgBetaUser @propertyParams
$totalUsers = $users.Count

# Initialize progress counter
$progress = 0

# Initialize an array to store user objects
$userObjects = @()

# Loop through all users and collect user objects
foreach ($index in 0..($totalUsers - 1)) {
    $user = $users[$index]

    # Update progress counter
    $progress++

    # Calculate percentage complete
    $percentComplete = ($progress / $totalUsers) * 100

    # Define progress bar parameters
    $progressParams = @{
        Activity        = "Processing Users"
        Status          = "User $($index + 1) of $totalUsers - $($user.userPrincipalName) - $($percentComplete -as [int])% Complete"
        PercentComplete = $percentComplete
    }

    # Display progress bar
    Write-Progress @progressParams

    # Get manager information
    $managerDN = $user.Manager.AdditionalProperties.displayName
    $managerUPN = $user.Manager.AdditionalProperties.userPrincipalName

    # Create an object to store user properties
    $userObject = [PSCustomObject]@{
        "ID"                          = $user.id
        "First name"                  = $user.givenName
        "Last name"                   = $user.surname
        "Display name"                = $user.displayName
        "User principal name"         = $user.userPrincipalName
        "Domain name"                 = $user.userPrincipalName.Split('@')[1]
        "Email address"               = $user.mail
        "Job title"                   = $user.jobTitle
        "Manager display name"        = $managerDN
        "Manager user principal name" = $managerUPN
        "Department"                  = $user.department
        "Company"                     = $user.companyName
        "Office"                      = $user.officeLocation
        "Employee ID"                 = $user.employeeID
        "Mobile"                      = $user.mobilePhone
        "Phone"                       = $user.businessPhones -join ','
        "Street"                      = $user.streetAddress
        "City"                        = $user.city
        "Postal code"                 = $user.postalCode
        "State"                       = $user.state
        "Country"                     = $user.country
        "User type"                   = $user.userType
        "On-Premises sync"            = if ($user.onPremisesSyncEnabled) { "enabled" } else { "disabled" }
        "Account status"              = if ($user.accountEnabled) { "enabled" } else { "disabled" }
        "Account Created on"          = $user.createdDateTime
        # Uncomment below if you have Azure AD P1/P2 to get last succesful sign in date
        # "Last sign in"                 = if ($user.SignInActivity.LastSuccessfulSignInDateTime) { $user.SignInActivity.LastSuccessfulSignInDateTime } else { "No sign in" }
        "Licensed"                    = if ($user.assignedLicenses.Count -gt 0) { "Yes" } else { "No" }
        "MFA status"                  = "-"
        "Email authentication"        = "-"
        "FIDO2 authentication"        = "-"
        "Microsoft Authenticator App" = "-"
        "Password authentication"     = "-"
        "Phone authentication"        = "-"
        "Software Oath"               = "-"
        "Temporary Access Pass"       = "-"
        "Windows Hello for Business"  = "-"
    }

    $MFAData = Get-MgBetaUserAuthenticationMethod -UserId $user.userPrincipalName

    # Check authentication methods for each user
    foreach ($method in $MFAData) {
        Switch ($method.AdditionalProperties["@odata.type"]) {
            "#microsoft.graph.emailAuthenticationMethod" {
                $userObject."Email authentication" = $true
                $userObject."MFA status" = "Enabled"
            }
            "#microsoft.graph.fido2AuthenticationMethod" {
                $userObject."FIDO2 authentication" = $true
                $userObject."MFA status" = "Enabled"
            }
            "#microsoft.graph.microsoftAuthenticatorAuthenticationMethod" {
                $userObject."Microsoft Authenticator App" = $true
                $userObject."MFA status" = "Enabled"
            }
            "#microsoft.graph.passwordAuthenticationMethod" {
                $userObject."Password authentication" = $true
                # When only the password is set, then MFA is disabled.
                if ($userObject."MFA status" -ne "Enabled") {
                    $userObject."MFA status" = "Disabled"
                }
            }
            "#microsoft.graph.phoneAuthenticationMethod" {
                $userObject."Phone authentication" = $true
                $userObject."MFA status" = "Enabled"
            }
            "#microsoft.graph.softwareOathAuthenticationMethod" {
                $userObject."Software Oath" = $true
                $userObject."MFA status" = "Enabled"
            }
            "#microsoft.graph.temporaryAccessPassAuthenticationMethod" {
                $userObject."Temporary Access Pass" = $true
                $userObject."MFA status" = "Enabled"
            }
            "#microsoft.graph.windowsHelloForBusinessAuthenticationMethod" {
                $userObject."Windows Hello for Business" = $true
                $userObject."MFA status" = "Enabled"
            }
        }
    }

    # Add user object to the array
    $userObjects += $userObject
}

# Complete the progress bar
Write-Progress -Activity "Processing Users" -Completed

# Export all user objects to CSV
$userObjects | Sort-Object "Display name" | Export-Csv -Path $Csvfile -NoTypeInformation -Encoding UTF8 #-Delimiter ";"

This is how it looks.

Step 4. Run export Azure AD users PowerShell script

Change the path to the scripts folder. Run the PowerShell script to export Azure AD users to CSV file. Wait till it completes.

PS C:\> cd c:\scripts
PS C:\scripts> .\Export-AADUsers.ps1

Step 5. Verify Azure AD users report CSV file

Go to the temp folder and verify that you see the AllAADUsers_ file.

Open the CSV file with your favorite application. In our example, it’s Microsoft Excel.

Everything looks fantastic!

Read more: Migrate Azure AD Connect to new server »

Conclusion

You learned how to Export Azure AD users to CSV with PowerShell. There is a lot of information in every user account. With PowerShell, you can have a custom report that will suit your needs.

Did you enjoy this article? You may also like Find Azure AD Connect accounts. Don’t forget to follow us and share this article.