Active Directory

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:

Sponsored Content

Passwords Haven’t Disappeared Yet

123456. Qwerty. Iloveyou. No, these are not exercises for people who are brand new to typing. Shockingly, they are among the most common passwords that end users choose in 2021. Research has found that the average business user must manually type out, or copy/paste, the credentials to 154 websites per month. We repeatedly got one question that surprised us: “Why would I ever trust a third party with control of my network?

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

Using PowerShell to get information about users in the Administrators group

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

Related Topics:

IT consultant, Contributing Editor @PetriFeed, and trainer @Pluralsight. All about Microsoft, Office 365, Azure, and Windows Server.
13 Email Threat Types to Know About Right Now

As email threats evolve and multiply, keeping track of them all—and staying protected against the many different types—becomes a complex challenge. Today, that requires more than just the traditional email gateway solution that used to be good enough.

In this eBook you will learn:

  • What are the most common and challenging email attacks for organizations?
  • How to defend against sophisticated email threats, such as spoofing, social engineering, and fraud
  • How to protect employees at the inbox level with the right technologies and security-awareness training
  • How to use a multilayered protection strategy to reduce susceptibility to email attacks and better defend your business and employees

Sponsored by: