# How to Export Active Directory Users to CSV and Build Reports
[https://adamtheautomator.com/export-active-directory-users-to-csv/](https://adamtheautomator.com/export-active-directory-users-to-csv/)
For many Active Directory (AD) admins, retrieving users from AD was an entry point to PowerShell. PowerShell is a powerful tool for interrogating systems, and Active Directory is no exception. Searching for and returning AD users with PowerShell is just the beginning. Let’s take that up a notch and export Active Directory users to CSV!
Not a reader? Watch this related video tutorial!”
***Not seeing the video? Make sure your ad blocker is disabled.***
In this tutorial, you will learn how to perform some basic AD queries with PowerShell and create handy reports. Using PowerShell, you will learn to format output by renaming columns, merging text fields, and performing calculations to develop valuable reports.
> Manage and Report Active Directory, Exchange and Microsoft 365 with ManageEngine ADManager Plus. [Download Free Trial!](https://www.manageengine.com/products/ad-manager/tp/windows-active-directory-management-tool.html?utm_source=ata&utm_medium=website-listing&utm_campaign=admp-exchangegroup)
## Prerequisites
This tutorial will be a hands-on demonstration. If you’d like to follow along, be sure you have the following:
- Logged into an AD-joined computer with a domain user.
- PowerShell – This tutorial uses PowerShell Version 7.1.4, but any version of PowerShell should work.
- [Windows Remote System Administration Tools (RSAT)](https://adamtheautomator.com/powershell-import-active-directory/)
## Getting Comfortable with the `Get-ADUser` PowerShell Cmdlet
Before creating reports, you must first figure out how to find the AD users you’d like to export Active Directory users to CSV. To do that, you’ll use the `Get-ADUser` cmdlet. The `Get-ADUser` cmdlet is a PowerShell cmdlet that comes with the PowerShell ActiveDirectory module.
Open a PowerShell console and run the `Get-ADUser` cmdlet using the `Filter` parameter and argument of `*`. Using an asterisk with the `Filter` parameter tells `Get-ADUser` to return all AD users. You’ll create more sophisticated filters a bit later.
```powershell
Get-ADUser -Filter *
```
The Get-AdUser cmdlet returning all users
By default, the `Get-ADUser` cmdlet will return the following properties:
- `DistinguishedName` – The full LDAP name of the user object.
- `Enabled` – Is the user enabled, true or false.
- `GivenName` – The user’s first name.
- `Name` – The user’s full name.
- `ObjectClass` – The type of AD object this is.
- `ObjectGUID` – The ID of the AD object.
- `SamAccountName` – This was the login name up to Windows NT4.0
- `SID` – Another type of Object ID.
- `Surname` – The user’s last name.
- `UserPrincipalName` – The user’s login name.
In your report, you probably don’t need all of these properties. By default, `Get-ADUser` also returns the built-in domain Administrator and Guest accounts. You almost certainly want to exclude those. You’ll learn how in the following sections.
## Limiting Searches to OUs with the `SearchBase` Parameter
AD users can be spread across sometimes dozens of organizational units (OUs). Sometimes, you need to limit the search to only a particular OU. To do that, you can use the `SearchBase` parameter. The `SearchBase` parameter allows you to specify a single OU as a starting point to search for users.
For example, perhaps you have an ATA-Users OU with various department OUs inside, as shown below. Inside the department OUs contains all of the user accounts you’d like to include in your export to CSV.
Example AD OU structure
You can define the `SearchBase` argument as ATA-Users OU’s distinguished name (DN) like below to limit the search to the ATA-Users OU and all OUs inside.
```powershell
Get-ADUser -Filter * -SearchBase "OU=ATA-Users,DC=ATA,DC=local"
```
The output above displays many different properties for each user, but let’s limit that down a bit only to show the properties you might be interested in. To do this, use the `Select-Object` cmdlet only to return the `Name` and `UserPrincipalName` properties.
Related:[Back to Basics: Understanding PowerShell Objects](https://adamtheautomator.com/powershell-objects/)
```powershell
Get-ADUser -Filter * -SearchBase "OU=ATA-Users,DC=ATA,DC=local" | select Name,UserPrincipalName
```
Perhaps you’d like to only export Active Directory users to CSV in the Sales OU. To do that, specify the `Sales` OU in the `SearchBase` parameter like below.
```powershell
Get-ADUser -Filter * -SearchBase "OU=Sales,OU=ATA-Users,DC=ATA,DC=local" | select Name,UserPrincipalName
```
## Filtering AD User Accounts from `Get-ADUser`
Up to this point, you have ignored the `Filter` parameter by simply specifying an asterisk to return all users. But if you need to query only certain users matching specific criteria, the `Filter` parameter is your friend.
Let’s say you’d like to eventually export all Active Directory users to a CSV inside of the ATA-Users OU, but only if they have their `Department` AD attribute set to `Sales` like the example user account below.
An AD user account with Sales as a Department attribute
Using the `Filter` parameter on `Get-ADUser`, specify the AD attribute (`Department`), the operator `-eq` equating to “equal to” and the value of the `Department` attribute (`Sales`).
Related:[Understanding PowerShell Comparison Operators By Example](https://adamtheautomator.com/powershell-like/)
```powershell
Get-ADUser -Filter {Department -eq "Sales"} -SearchBase "OU=ATA-Users,DC=ATA,DC=local"| select Name,UserPrincipalName
```
If you have users inside the ATA-Users OU with the `Department` attribute set to `Sales`, `Get-ADUser` will only return those users.
Get-ADUser only returning Sales users
Maybe you’d like to include the `Department` attribute in the output. To do that, you’d typically specify the `Department` property as another property to show via the `Select-Object` (`select`) cmdlet, as shown below. But notice the `Department` property doesn’t show up.
Including Department Attribute
By default, the `Get-ADUser` cmdlet does not return all properties. To return all non-default properties, you must use the `Properties` parameter. In this case, tell `Get-ADUser` to return the `Department` property.
```powershell
Get-ADUser -Filter {Department -eq "Sales"} -SearchBase "OU=ATA-Users,DC=ATA,DC=local" -Properties Department | select Name,UserPrincipalName
```
Now that you have a basic filter, you can continue to add more criteria to the `Filter` as necessary, combining them with the PowerShell `and` and `or` operators. Below, for example, `Get-ADUser` will return all AD users that are enabled that are either in the `Sales` or `Finance` departments.
Adding Criteria to the Filter
In the tutorial’s environment, Steve James is an account in the `Sales` department, but his account is not enabled, so his account will not show up via the command above.
Account not Enabled
## Exporting Active Directory Users to CSV
You now have the foundational knowledge to retrieve AD users with PowerShell. The final step is to export those Active Directory users to a CSV file to create a report you can share.
Related:[What is a CSV File, How to Create, Open and Work with Them](https://adamtheautomator.com/csv-file/)
Let’s say you’ve built your `Get-ADUser` command, and it’s returning the users you’d like to include in your CSV report like below.
This command:
1. Retrieves all AD users in the ATA-Users OU and all child OUs.
2. Outputs extra properties like `Department`, `PasswordLastSet`, and `PasswordNeverExpires`.
3. Limits the properties returned via `Select-Object` to include in the report like `Name`, `UserPrincipalName`, `Department`, and any property that begins with `Password`.
> *Notice `password*` in this example. Using an asterisk with `Select-Object` tells `Select-Object` to return all properties that start with `password`.*
```powershell
Get-ADUser -Filter * -SearchBase "OU=ATA-Users,DC=ATA,DC=local" -properties Department,PasswordLastSet,PasswordNeverExpires | Select-Object Name,UserPrincipalName,Department,password*
```
To export the Active Directory users, this command returns to CSV, pipe the objects to the `Export-Csv` cmdlet. The `Export-Csv` cmdlet is a PowerShell cmdlet that allows you to send various objects to (AD user accounts in this example) and then append those objects as CSV rows.
Related:[Export-Csv: Converting Objects to CSV Files](https://adamtheautomator.com/export-csv/)
To export each AD user returned in the command above, append `| Export-Csv .csv` to the end. This action pipes all of the objects that `Select-Object` returns and “converts” them into a CSV file.
```powershell
Get-ADUser -Filter * -SearchBase "OU=ATA-Users,DC=ATA,DC=local" -properties Department,PasswordLastSet,PasswordNeverExpires | Select-Object Name,UserPrincipalName,Department,password* | Export-CSV pass_report.csv
```
You’ll see below that `Export-Csv` creates a CSV file called *pass\_report.csv* that includes headers as object property names and one row per AD user account.
Example output from Export-CSV
## Customizing CSV Headers with `Select-Object`
The report you can now generate contains all the required information, but the CSV headers are not grammatically correct and can be misleading. A manager may not know what a `UserPrincipalName` is, and having column headings with multiple words without spaces is good English.
To export the Active Directory users to CSV and create custom CSV headers, use the `Select-Object` cmdlet’s [calculated properties](https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_calculated_properties?view=powershell-7.1). The calculated properties feature is a way you can define custom property names and values.
The `Select-Object` cmdlet’s calculated properties feature requires you to define a hashtable with two key/value pairs; `Name` to indicate the name of the property and `Expression` to represent the code to manipulate the original object property value or simply the actual property name.
In this example, let’s say you’d like the CSV to show a header name of:
- `Login Name` instead of `UserPrincipalName`
- `Password Last Set Date` instead of `PasswordLastSet`
- `Password Never Expires` instead of `PasswordNeverExpires`
- `Password Last Set Date` instead of `PasswordLastSet` that’s represented with a [short date](https://docs.microsoft.com/en-us/dotnet/api/system.datetime.toshortdatestring?view=net-5.0).
To make these changes, you’d first build a hashtable for each property like below.
```powershell
@{Name="Login Name";Expression="UserPrincipalName"}
@{Name="Password Last Set Date";Expression="PasswordLastSet"}
@{Name="Password Never Expires";Expression="PasswordNeverExpires"}
@{Name="Password Last Set Date";Expression={$_.PasswordLastSet.ToShortDateString()}}
```
Now that you have the hashtables add them to the list of properties you provide to the `Select-Object` cmdlet just like you would a typical property name.
> *The `Select-Object` cmdlet’s `Property` parameter accepts an array. If you have many properties to pass, you can create an array first and then pass that array to the `Property` parameter for easier readability.*
```powershell
$properties = @(
Name,
@{Name="Login Name";Expression="UserPrincipalName"},
Department,
@{Name="Password Last Set Date";Expression="PasswordLastSet"},
@{Name="Password Never Expires";Expression="PasswordNeverExpires"},
@{Name="Password Last Set Date";Expression={$_.PasswordLastSet.ToShortDateString()}}
)
Select-Object -Property $properties
```
Combining `Get-ADUser` with the new `Select-Object` construct created above gives you the below code snippet.
```powershell
$properties = @(
Name,
@{Name="Login Name";Expression="UserPrincipalName"},
Department,
@{Name="Password Last Set Date";Expression="PasswordLastSet"},
@{Name="Password Never Expires";Expression="PasswordNeverExpires"},
@{Name="Password Last Set Date";Expression={$_.PasswordLastSet.ToShortDateString()}}
)
Get-ADUser -Filter * -SearchBase "OU=ATA-Users,DC=ATA,DC=local" -Properties Department,PasswordLastSet,PasswordNeverExpires | Select-Object -Property $properties | Export-CSV pass_report.csv
```
Once complete, PowerShell will create a CSV file for you that looks like the example below.
CSV export of AD users using calculated properties
> Manage and Report Active Directory, Exchange and Microsoft 365 with ManageEngine ADManager Plus. [Download Free Trial!](https://www.manageengine.com/products/ad-manager/tp/windows-active-directory-management-tool.html?utm_source=ata&utm_medium=website-listing&utm_campaign=admp-exchangegroup)
Conclusion
PowerShell is a powerful tool for reporting on Active Directory Users. This tutorial showed you how to find and filter users based on various criteria and create a CSV file from that output using just a few lines of code.
Now that you have the foundational knowledge to query AD users and export Active Directory users to CSV, where do you see yourself using this knowledge in your daily work life?