Skip to main content

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. Email address
  7. Job Title
  8. Manager display name
  9. Manager user principal name
  10. Department
  11. Company
  12. Office
  13. Employee ID
  14. Mobile
  15. Phone
  16. Street
  17. City
  18. Postal code
  19. State
  20. Country
  21. User type
  22. On-Premises sync
  23. Account status
  24. Account created on
  25. Last log in (requires an Azure AD P1/P2 license)
  26. Licensed
  27. 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.`

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.traceroute.com/export-azure-ad-users-to-csv-powershell

    .NOTES
    Written by: traceroute
    Website:    www.traceroute.com
    LinkedIn:   linkedin.com/in/traceroute

    .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 login date
    V1.30, 07/24/2023 - Update for Microsoft Graph PowerShell changes
#>

# 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"

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

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

        # Initialize progress counter
        $progress = 0

        # Collect and loop through all users
        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
                "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 log in date
                # "Last log in"                 = if ($user.SignInActivity.LastSignInDateTime) { $user.SignInActivity.LastSignInDateTime } else { "No log 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"
                    }
                }
            }

            # Output the user object
            $userObject
        }
    }
}

# Export users to CSV
Get-AllMgUsers | Sort-Object "Display name" | Export-Csv -Path $Csvfile -NoTypeInformation -Encoding UTF8 #-Delimiter ";"

This is how it looks.

Export Azure AD users to CSV with PowerShell scripts folder

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.

Export Azure AD users to CSV with PowerShell temp folder

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

Export Azure AD users to CSV with PowerShell file

Everything looks fantastic!

Leave a Reply