Create Active Directory User Reports with PowerShell
Create Active Directory User Reports with PowerShell. For many Active Directory (AD) administrators, retrieving users from AD was an entry point to PowerShell. However, searching for and returning AD users with PowerShell is just the beginning. So let’s take what we usually do and build and export Active Directory user reports to a Comma-separated value or CSV file.
This article will discuss using Windows PowerShell to run some straightforward AD queries and generate valuable reports. We will also discover how to format output using PowerShell by renaming columns, combining text fields and running computations to create valuable reports.
Let’s start with how Create Active Directory User Reports with PowerShell.
Create AD User Reports with PowerShell
Prerequisites
For this article Create Active Directory User Reports with PowerShell, we will mostly use the Active Directory Powershell module. To use the Get-ADUser or any other AD commands, be sure we have the following prerequisites:
- Logged into a domain AD-joined computer with a domain user.
- PowerShell – This tutorial uses the latest PowerShell Version 7, but any version newer than PowerShell 4 should work.
- Windows Remote System Administration Tools (RSAT)
Get-ADUser
We must first determine how to locate the AD users you want to export Active Directory users to CSV before you can start making reports. We will employ the Get-ADUser cmdlet to do that. The PowerShell Active Directory module includes a cmdlet called Get-ADUser.
Run the Get-ADUser cmdlet in a PowerShell console with the Filter argument set to an asterisk (*). The Get-ADUser command will return all AD users if the Filter option is specified with an asterisk.
Get-ADUser -Filter *
Additionally, these are the most frequently queried and used Get-ADUser properties by an administrator:
- Enabled – Determines the user’s status with boolean values, True or False. If the value is True, the user is allowed to log in to the domain, otherwise, tagged as False.
- DistinguishedName (DN) – The complete Lightweight Directory Access Protocol (LDAP) name of the user object
- ObjectGUID – The unique ID of the AD object.
- SID – The security identifier of the Object GUID.
- UserPrincipalName (UPN) – The user’s primary login name.
- SamAccountName – The legacy UPN, most widely used since Windows NT 3.0.
Most likely, we won’t include all of the other properties in our report, but if you want to check all available properties of this command, you can go to this official Microsoft Documentation link to view it on-demand.
Filtering Users with Get-AD
AD users may be dispersed over several organizational tiers. To restrict the search to only a specific organizational unit or OU, we can use the SearchBase argument. We can use it to designate a single OU as the place to start your user search.
As seen below, you might, for instance, have a Users OU that contains several department OUs. All the user accounts you want to include in your CSV export are inside the Department OUs.
To restrict the search to the Users OU and any OUs therein, you can define the SearchBase argument as the distinguished name (DN) of the Users OU, as shown below.
Get-ADUser -Filter * -SearchBase "OU=Users,DC=Example,DC=local"
For each user, the output above shows a wide range of different features, but let’s narrow that down to reveal the properties you would be interested in. Use the Select-Object cmdlet to return the Name and UserPrincipalName properties to accomplish this.
Get-ADUser -Filter * -SearchBase "OU=Users,DC=Example,DC=local" | select Name,UserPrincipalName
As you have noticed, we only specified an asterisk in the Filter parameter to return all users. But the Filter option is a powerful argument if we need to query only a subset of people who meet a given set of requirements. Suppose all Active Directory users had their Department AD attribute set to Sales, as in the sample user account below.
We can use the operator -eq, which means “equal to,” the value of the Department attribute, the AD attribute (Department), and the Filter argument on Get-ADUser (Sales) like the following below.
Get-ADUser -Filter {Department -eq "Sales"} -SearchBase "OU=Users,DC=AExample,DC=local"| select Name,UserPrincipalName
To learn more about the Get-ADUser command and its other users, you may check our other article focusing primarily on the Get-ADUser command.
Exporting Active Directory Users to CSV
Now that we have tackled the basics, we can use PowerShell to obtain AD users. Then, to build a report we can share, the last step is to export those Active Directory users to a CSV file. Consider a scenario in which we have developed your Get-ADUser command, and it is returning the users you want to include in your CSV report, as seen below.
- The command can retrieve all AD users in the Users OU and all child OUs.
- In addition the command can export properties like Department, PasswordNeverExpires status, and other properties.
- Moreover the command can limit the properties returned by piping it to the Select-Object command. To include extended properties in the report like Name, UserPrincipalName, Department, and any property that begins with Password.
Note the password* in the example below. The Select-Object command is instructed to return all attributes that begin with the password when an asterisk is used.
Get-ADUser -Filter * -SearchBase "OU=Users,DC=Example,DC=com" -properties Department, PasswordNeverExpires |
Select-Object Name, UserPrincipalName, password*
The following command passes the objects to the Export-Csv cmdlet and returns a CSV file with the Active Directory users exported. Benefit of PowerShell’s Export-Csv cmdlet enables you to send different objects to (in this case, AD user accounts) and then attach those items as CSV rows.
Pipe the Export-Csv command at the end of each AD user given by the command above to export them all. What this command does it converts all the objects that the Select-Object command returns into a CSV file.
Get-ADUser -Filter * -SearchBase "OU=Users,DC=Example,DC=com" -properties Department, PasswordNeverExpires |
Select-Object Name, UserPrincipalName, Department, password* |
Export-CSV ad_users_report.csv
The Export-Csv command above generates a CSV file named ad_users_report.csv with one row for each AD user account and headers that serve as the names of object properties.
Customizing CSV Headers with Select-Object
Make use of the calculated properties of the Select-Object cmdlet to export the Active Directory users to CSV and provide unique CSV headers. You can define unique property names and values using the computed properties feature.
The computed properties functionality of the Select-Object cmdlet needs you to create a hashtable with two key/value pairs:
- Name to represent the property name; and
- Expression to represent the code to modify the original object property value or just the property’s name.
For this example, let us say we would like the CSV to show a:
- Username header instead of UserPrincipalName
- For example Password Last Set Date header instead of PasswordLastSet
- Or Password Never Expires header instead of PasswordNeverExpires
- Another example Password Last Set Date header instead of PasswordLastSet
We would first build a hashtable for each property like below to make these changes.
@{Name="Username";Expression="UserPrincipalName"}
@{N="Password Last Set Date";E="PasswordLastSet"}
@{N="Password Never Expires";E="PasswordNeverExpires"}
@{N="Password Last Set Date";E={$_.PasswordLastSet.ToShortDateString()}}
If you notice, we can use the key pairs’ short form of N and E for Name and Expression, respectively. With the hashtables in hand, you can now add them to the list of properties you give the Select-Object cmdlet in the same way you would a regular property name.
The Property parameter of the Select-Object cmdlet takes an array. To make the Property parameter easier to read, you can create an array if you have a lot of properties to pass.
$properties = @(
Name,
@{N="Username";E="UserPrincipalName"},
Department,
@{N="Password Never Expires";E="PasswordNeverExpires"},
@{N="Password Last Set Date";E={$_.PasswordLastSet.ToShortDateString()}}
)
Select-Object -Property $properties
Combining Get-ADUser with the new Select-Object command constructed and created above gives you the below code snippet.
$properties = @(
Name,
@{N="Username";E="UserPrincipalName"},
Department,
@{N="Password Never Expires";E="PasswordNeverExpires"},
@{N="Password Last Set Date";E={$_.PasswordLastSet.ToShortDateString()}}
)
Get-ADUser -Filter * -SearchBase "OU=Users,DC=Example,DC=local" -Properties Department, PasswordLastSet, PasswordNeverExpires |
Select-Object -Property $properties |
Export-CSV adusers_sample_report.csv
Once finished, PowerShell will create a CSV file for you that looks like the example below.
InfraSOS Monitor User Reports and Perform Health Check
Consider our Reporting and monitoring tool by developed by Active Directory experts. With AD reporting solution you get detailed reports on Active Directory, Office 365, Azure AD on all your Active Directory Objects and attributes. That is the only SaaS reporting platform for Active Directory Auditing.
Our unique only Saas platform InfraSOS is the leading active directory reporting and monitoring tool on the market. In there you can have 200+ reports for Office 365 plus other comprehensive AD reports that can be exported in various formats.
You can find your users that are active or inactive, blocked, locked out or have their accounts disabled. Information that can be reported includes the last time they had logged on or changed their passwords.
It aids the Admin that can access information about the current status of user accounts, their security permissions, password expiry dates (or when they had changed their passwords), failed login attempts, and much more.
That tool offers unique settings, so that you can create custom filters to search for AD attributes – including missing attributes – based on user attribute entries.
Not to mention Active Directory Health Check reports that report on the status of the domain controller (DC) itself and any Domain Nameservers (DNS) with the ability to set alerts on AD DC replication statuses.
That’s it. Thank you for reading how to Create Active Directory User Reports with PowerShell.
Create Active Directory User Reports with PowerShell Conclusion
To summarize reporting on Active Directory Users may be done effectively with PowerShell. This article demonstrated how to use a few lines of code to discover and filter people based on various criteria, then export the results to a CSV file.
To learn more about the Get-ADUser command and its other users, you may check our other article focusing primarily on the Get-ADUser command.
