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!”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!
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)
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.
Get-ADUser -Filter *
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.0SID
– 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.
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.
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.
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.
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.
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
).
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.
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.
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.
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.
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.
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.
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:
- Retrieves all AD users in the ATA-Users OU and all child OUs.
- Outputs extra properties like
Department
,PasswordLastSet
, andPasswordNeverExpires
. - Limits the properties returned via
Select-Object
to include in the report likeName
,UserPrincipalName
,Department
, and any property that begins withPassword
.
Notice
password*
in this example. Using an asterisk withSelect-Object
tellsSelect-Object
to return all properties that start withpassword
.
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.
To export each AD user returned in the command above, append | Export-Csv <csv file name>.csv
to the end. This action pipes all of the objects that Select-Object
returns and “converts” them into a CSV file.
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.
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. 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 ofUserPrincipalName
Password Last Set Date
instead ofPasswordLastSet
Password Never Expires
instead ofPasswordNeverExpires
Password Last Set Date
instead ofPasswordLastSet
that’s represented with a short date.
To make these changes, you’d first build a hashtable for each property like below.
@{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’sProperty
parameter accepts an array. If you have many properties to pass, you can create an array first and then pass that array to theProperty
parameter for easier readability.
$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.
$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.
Manage and Report Active Directory, Exchange and Microsoft 365 with ManageEngine ADManager Plus. Download Free Trial!
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?