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:

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.

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