Export Active Directory User Information to Excel

How do I export Active Directory user information to Excel?

Exporting information from Active Directory (AD) has been possible since its inception in Windows 2000 Server, but over time this task has become easier with the addition of the DS commands in Windows Server 2003 and – starting in Windows 2008 R2 Server – the PowerShell AD module. In this article, I’ll show you how to extract user information and export the data to an Excel spreadsheet.

Enumerate AD users and specific properties

Logon to a Windows Server 2012 domain controller (DC) and open PowerShell using the blue icon on the desktop Taskbar. Run the following command to return all AD users and attributes:

get-aduser –filter * -property *

While this information is useful, it’s not very digestible. If nothing else, you can see all the AD user properties listed so you know how to refer to them when writing more complex PowerShell commands. We can expand the previous command to hone down on specific information and format the output in a more readable form. This is an integral part of the export Active Directory users process:

get-aduser –filter * -property * | Select-Object Name, LastLogonDate

Export the results to an Excel spreadsheet

If you need to store the results of the last command or send the information to a manager, you can export the data to a comma delimited file that can be opened in Excel:

get-aduser –filter * -property * | select-object Name, LastLogonDate | export-CSV adusers.csv -NoTypeInformation -Encoding UTF8

The resulting adusers.csv file will be saved in the current PowerShell directory, i.e. the directory shown when you run commands in the PowerShell window.

Filter by AD group

Now let’s get information just for users that are a member of the Administrators group.

get-adgroupmember administrators | where-object –FilterScript {$_.objectClass –eq “user”} | get-aduser -property * | select-object Name, LastLogonDate

If you run the get-adgroupmember administrators command on its own, you’ll see that it outputs not only user accounts that are members of the Administrators group, but also other AD groups. That causes a problem when piping the results to the get-aduser cmdlet, because it doesn’t know how to process AD group information. Therefore, we need to filter out the groups using where-object.

In this example, the where-object cmdlet ensures that only user objects in the Administrators group are piped to get-aduser. Also see that in the above command, I omitted the –filter parameter from get-aduser, because I’m using get-adgroupmember to tell get-aduser exactly which users I want it to report on. This precise selection is a best practice when you export Active Directory users to ensure you’re getting the data you need.

Finally, you can add export-csv to generate an Excel spreadsheet.

get-adgroupmember administrators | where-object –FilterScript {$_.objectClass –eq “user”} | get-aduser -property * | select-object Name, LastLogonDate | export-CSV adusers.csv -NoTypeInformation -Encoding UTF8

FAQs

How can I export Active Directory users with custom properties not mentioned in PowerShell commands?

To export Active Directory users with custom attributes, you can modify the Select-Object parameter in your PowerShell command to include any AD attributes like department, title, manager, or custom attributes. Simply add these properties to your export command, separated by commas.

Can I schedule automatic exports of Active Directory users to multiple file formats?

Yes, you can create a scheduled task to export Active Directory users automatically using PowerShell scripts. The exports can be saved in various formats including CSV, XML, or JSON, and can be configured to run at specific intervals.

What’s the best way to export Active Directory users from multiple domains simultaneously?

To export Active Directory users from multiple domains, use the -Server parameter in your PowerShell commands and create a foreach loop that iterates through your domain controllers. This allows for consolidated export of user data from all domains.

How can I export Active Directory users’ password expiration and account status information?

When exporting Active Directory users, include properties like PasswordLastSet, PasswordNeverExpires, and Enabled in your Select-Object statement to track password and account status. This helps maintain security compliance and account management.

Is it possible to export Active Directory users based on their login history or inactivity?

Yes, you can export Active Directory users based on their last login date or inactivity period by using the LastLogonTimeStamp attribute and incorporating a filter in your export command to identify dormant or active accounts.