skip to Main Content

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:

  • First name
  • Last name
  • Display name
  • User logon name
  • User principal name
  • Street
  • City
  • State/province
  • Zip/Postal Code
  • Country/region
  • Job Title
  • Department
  • Company
  • Manager
  • Description
  • Office
  • Telephone number
  • E-mail
  • Mobile
  • Notes
  • Account status
  • 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. Make sure to check if 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 in Notepad. Give it the name Export-ADUsers.ps1 and place it in the C:\scripts folder.

# 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

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

# 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 = "Description"; Expression = { $_.Description } },
@{Label = "Office"; Expression = { $_.Office } },
@{Label = "Telephone number"; Expression = { $_.telephoneNumber } },
@{Label = "E-mail"; Expression = { $_.Mail } },
@{Label = "Mobile"; Expression = { $_.mobile } },
@{Label = "Notes"; Expression = { $_.info } },
@{Label = "Account status"; Expression = { if (($_.Enabled -eq 'TRUE') ) { 'Enabled' } Else { 'Disabled' } } },
@{Label = "Last logon date"; Expression = { $_.lastlogondate } }|

# Export report to CSV file
Export-Csv -Encoding UTF8 -Path $Csvfile -NoTypeInformation #-Delimiter ";"
  • Line 17,18,19: Edit the target distinguishedName. You can have one OU or multiple OUs (in this example).

Step 2: 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 3: Open AD users report CSV file

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

Export AD users to CSV with PowerShell CSV file

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

Export AD users to CSV with PowerShell Excel

Everything looks great!

Read more: Remove users from group with PowerShell »

Conclusion

In this article, 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.

ALI TAJRAN

ALI TAJRAN

ALI TAJRAN is a passionate IT Architect, IT Consultant, and Microsoft Certified Trainer. He started Information Technology at a very young age, and his goal is to teach and inspire others. Read more »

This Post Has 24 Comments

  1. Thank you for the scripts.

    How can we integrate memberof property to put friendly group name on each line for each user?

    Our manager would like to sort on a user name, and see all groups, line by line.

    I added memberof to the script and it gives one line of all groups user is a member of separated by colon.

    Trying to make it friendlier. 🙂

  2. Hi Ali,

    The script is working fine. but in the exported report, account status showing wrong content. Few users its showing enabled status. others are disabled. but when i was checked in AD all the users are active. Is there anything need to check or any other commands to integrate with this command in the script.

  3. Hello,
    the script works for me, but with some issues, i get a csv file, but its not formated but only each user seperated per line in one field. Can you help there? This is also an error message while executing.

    PS C:\Scripts> C:\Scripts\Export-ADUsers_StLi.ps1
    Get-ADUser : Verzeichnisobjekt nicht gefunden
    In C:\Scripts\Export-ADUsers_StLi.ps1:26 Zeichen:14
    + $Users = Get-ADUser -SearchBase “$DN” -Filter * -Properties *
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : ObjectNotFound: (:) [Get-ADUser], ADIdentityNotFoundException
    + FullyQualifiedErrorId : ActiveDirectoryCmdlet:Microsoft.ActiveDirectory.Management.ADIdentityNotFoundException,Microsoft.ActiveDirectory.Management.Commands.GetADUser

  4. great script! only 1 issue, how do i add the time of creation for each user?
    i assume its something like @{Label = “User Created”; Expression = {$_.created}}

    am i right?

  5. hey, I’m new to PowerShell and I what to run the script but I have some errors for example

    Get-ADUser : Directory object not found
    At C:\Data\Scripts\Export-ADUsers.ps1:25 char:14
    + $Users = Get-ADUser -SearchBase $DN -Filter * -Properties *
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : ObjectNotFound: (:) [Get-ADUser], ADIdentityNotFoundException
    + FullyQualifiedErrorId : ActiveDirectoryCmdlet:Microsoft.ActiveDirectory.Management.ADIdentityNotFoundException,Microsoft.ActiveDirectory.Management.Commands.GetADUser

  6. Merhaba,

    Çok teşekkürler, çok faydalı oldu.
    Sayenizde istediğim gibi yapıyı çekebildim.

    Sağlıkla kalın.

  7. Very useful and informative. Thanks for the comments in the script and actually the whole script.

  8. Thanks for this great scipt.
    The intention was actually to do an export from Azure, but I’m not a powershell guru, and your script has most of the properties I need. Only miss the attributes UserType and Directorysynced
    Can these attributes also be found in Active Directory ?
    Thanks
    Steve

      1. Hello Ali,
        great script, but i have a question.
        I need a special feld called “othertelephone”, this can I run with

        select name,@{n=”othertelephone”;e={$_.othertelephone -join “;”}}

        How can I add this to your script, I tried some, but I had no idea left

        thx
        Steve

  9. Thank you for the Script. please confirm how to sort so records are arranged by OU and not alphabetically when exported.

      1. Adding this line did not work for me. Export works but I cannot get the OU information.

        Can you help me out ?

  10. for some reason it returned all accounts as disabled, it is not getting the checkbox onformation

    can you help?

    1. I had to run it in an admin shell otherwise it returned a lot of my users as disabled even though they were enabled.

  11. Hi Ali,

    Great article.
    I am working on an audit document and needed an easy powerfull way to to export all AD users.
    Your script helped me perfectly!

    Take care!

  12. Great Script does as intended and the formatting is excellent!
    Would be great to see a method on how to import all these fields back into AD if you wanted to see a bulk update on fields

Leave a Reply

Your email address will not be published.