Export AD users to CSV with PowerShell

We want to export AD users information to CSV with PowerShell. Why do we need to list the AD users with PowerShell? For example, we want to know if every AD user has the correct mobile phone number in Active Directory. That’s because the service desk looks up the information in Active Directory before they make a call. In this article, you will learn how to export Active Directory users to CSV file with PowerShell.

Information export AD users PowerShell script

The Export-ADUsers.ps1 PowerShell script will run against the distinguishedName that you set. 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. First name
2. Last name
3. Display name
4. User logon name
5. User principal name
6. Street
7. City
8. State/province
9. Zip/Postal Code
10. Country/region
11. Job Title
12. Department
13. Company
14. Manager
15. OU
16. Description
17. Office
18. Telephone number
19. Other Telephone
20. E-mail
21. Mobile
22. Pager
23. Notes
24. Account status
25. User created date
26. Last logon date

Export Active Directory users to CSV with PowerShell

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

Step 1: Prepare export AD users PowerShell script

Download and place Export-ADUsers.ps1 PowerShell script on the Domain Controller C:\scripts folder. If you don’t have a scripts folder, create one.

Ensure that the file is unblocked to prevent any 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-ADUsers.ps1 and place it in the C:\scripts folder.

<#
    .SYNOPSIS
    Export-ADUsers.ps1

    .DESCRIPTION
    Export Active Directory users to CSV file.

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

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

    .CHANGELOG
    V1.00, 05/24/2021 - Initial version
    V1.10, 04/01/2023 - Added progress bar, user created date, and OU info
    V1.20, 05/19/2023 - Added function for OU path extraction
#>

# Split path
$Path = Split-Path -Parent "C:\scripts\*.*"

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

# Define CSV and log file location variables
# They have to be on the same location as the script
$Csvfile = $Path + "\AllADUsers_$LogDate.csv"

# Import Active Directory module
Import-Module ActiveDirectory

# Function to extract OU from DistinguishedName
function Get-OUFromDistinguishedName {
    param(
        [string]$DistinguishedName
    )

    $ouf = ($DistinguishedName -split ',', 2)[1]
    if (-not ($ouf.StartsWith('OU') -or $ouf.StartsWith('CN'))) {
        $ou = ($ouf -split ',', 2)[1]
    }
    else {
        $ou = $ouf
    }
    return $ou
}

# Set distinguishedName as searchbase, you can use one OU or multiple OUs
# Or use the root domain like DC=exoip,DC=local
$DNs = @(
    "OU=Sales,OU=Users,OU=Company,DC=exoip,DC=local",
    "OU=IT,OU=Users,OU=Company,DC=exoip,DC=local",
    "OU=Finance,OU=Users,OU=Company,DC=exoip,DC=local"
)

# Create empty array
$AllADUsers = @()

# Loop through every DN
foreach ($DN in $DNs) {
    $Users = Get-ADUser -SearchBase $DN -Filter * -Properties * 

    # Add users to array
    $AllADUsers += $Users

    # Display progress bar
    $progressCount = 0
    for ($i = 0; $i -lt $AllADUsers.Count; $i++) {

        Write-Progress `
            -Id 0 `
            -Activity "Retrieving User " `
            -Status "$progressCount of $($AllADUsers.Count)" `
            -PercentComplete (($progressCount / $AllADUsers.Count) * 100)

        $progressCount++
    }      
}

# Create list
$AllADUsers | Sort-Object Name | Select-Object `
@{Label = "First name"; Expression = { $_.GivenName } },
@{Label = "Last name"; Expression = { $_.Surname } },
@{Label = "Display name"; Expression = { $_.DisplayName } },
@{Label = "User logon name"; Expression = { $_.SamAccountName } },
@{Label = "User principal name"; Expression = { $_.UserPrincipalName } },
@{Label = "Street"; Expression = { $_.StreetAddress } },
@{Label = "City"; Expression = { $_.City } },
@{Label = "State/province"; Expression = { $_.State } },
@{Label = "Zip/Postal Code"; Expression = { $_.PostalCode } },
@{Label = "Country/region"; Expression = { $_.Country } },
@{Label = "Job Title"; Expression = { $_.Title } },
@{Label = "Department"; Expression = { $_.Department } },
@{Label = "Company"; Expression = { $_.Company } },
@{Label = "Manager"; Expression = { (Get-AdUser $_.Manager -Properties DisplayName).DisplayName } },
@{Label = "OU"; Expression = { Get-OUFromDistinguishedName $_.DistinguishedName } },
@{Label = "Description"; Expression = { $_.Description } },
@{Label = "Office"; Expression = { $_.Office } },
@{Label = "Telephone number"; Expression = { $_.telephoneNumber } },
@{Label = "Other Telephone"; Expression = { $_.otherTelephone -join ";"} },
@{Label = "E-mail"; Expression = { $_.Mail } },
@{Label = "Mobile"; Expression = { $_.mobile } },
@{Label = "Pager"; Expression = { $_.pager } },
@{Label = "Notes"; Expression = { $_.info } },
@{Label = "Account status"; Expression = { if (($_.Enabled -eq 'TRUE') ) { 'Enabled' } Else { 'Disabled' } } },
@{Label = "User created date"; Expression = { $_.WhenCreated } },
@{Label = "Last logon date"; Expression = { $_.lastlogondate } } |

# Export report to CSV file
Export-Csv -Encoding UTF8 -Path $Csvfile -NoTypeInformation #-Delimiter ";"

• Line 54,55,56: Edit the target distinguishedName. You can have one OU or multiple OUs (in this example).

Step 2: Get distinguished name

You need to add the distinguished name value in the PowerShell script. Follow the below steps to get the distinguished name in Active Directory:

1. Start Active Directory Users and Computers
2. Right-click the target OU and click Properties
3. Go to the Attribute Editor tab
4. Find the attribute distuingedName in the attributes list
5. Double-click to open the string and copy the value

Note: If you don’t see the Attribute Editor tab, click in Active Directory Users and Computers in the menu bar on View and enable Advanced Features.

Step 3: Run export AD users PowerShell script

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

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

Step 4: Open AD users report CSV file

Go to the scripts folder and verify that you see the AllADUsers_ file.

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

Everything looks great!

Read more: Remove users from group with PowerShell »

Conclusion

You learned how to Export 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 Get Organizational Units with PowerShell. Don’t forget to follow us and share this article.