Active Directory How-To
Exporting Data from an Active Directory Organizational Unit
A look at exporting your user information from Active Directory.
- By Troy Thompson
- 01/14/2015
There will be many times when you or someone else will need a list of users. There are many ways and many tools that can be used to achieve an export of users, but if you're looking for something fast and easy without having to develop complex code or use the command prompt, the Active Directory Users and Computers console is the way to go. You'll be able to export a list from AD, import it into Excel and sort the data in about a minute. For the purpose of this article, I'll look at the Active Directory Snap-in in the Microsoft Management Console built-in export feature. I'm assuming you have the Active Directory Users and Computers console installed on your computer. If you don't, you can download it for Windows 7. You can also perform this function from a domain controller, which will already have the console installed.
An export from the Active Directory console will be saved in a .txt or .csv format by default. You can export data and organize into a format that suits your needs. I use the formulas, macros and functions in Excel a great deal to combine and vet data.
To export the data, launch Active Directory Users and Computers. Navigate to the domain structure of the Organizational Unit you wish to export and click on it. From the menu, select the Export List icon (see Figure 1). At this point, you'll have to choose whether you want a .csv or .txt format, choose a location for the file and name it.
[Click on image for larger view.] Figure 1. Selecting a file format.
The data that's exported using this method will be limited to the fields shown in the right-hand pane (see Figure 2). If you're primarily looking for the user account names, this is a quick and easy way to get that information.
[Click on image for larger view.] Figure 2. Limited exported information.
Your data will look similar to the following using the .txt format:
Name Type Description
Troy Thompson User Writer
Brien Posey User Director of Metal
Joan Smith User Assistant Director
Madison Tuck User Counselor
McKenzie Cuba User Food Service
You can copy and paste this information directly into Microsoft Excel or open the .txt file in Excel. The quickest way is to copy and paste. Once the data is in the spreadsheet, you can perform a variety of manipulations to it. For instance, if you wanted the first name in a separate cell, you could use the function =LEFT(A2,FIND(" ",A2,1)-1) to pull the first name and use the =RIGHT(A2,LEN(A2)-FIND(" ",A2,1)) function to return the last name. You would have to modify the formula to reflect where the data resides. If the name Troy Thompson were in the A3 cell, you would simply replace all instances of A2 in the formulas with A3. With the first and last name separated, you could then perform a sort on either field as needed.
As mentioned earlier, there are several ways to retrieve data from Active Directory. The method I just discussed is quite remedial, but is also fast and effective if you're looking for a simple export. You might want consider some of these other methods for exporting data (some are command-line and others are GUI-based):
- LDAP Data Interchange Format (LDIF) utility. Active Directory provides another built-in tool for bulk user operations, called LDIFDE. It has more options and is more powerful than CSVDE. It can also modify and delete existing objects and even extend the Active Directory schema. The problem with LDIFDE is that the necessary input file, which is referred to as an LDIF file with the extension .ldf, uses a more complex format than the simple .csv file.
Running this command exports all OUs except domain controllers into a file named ExportAD.ldf:
ldifde -f exportAD.ldf -s Server1 -d "dc=Export,dc=com" -p subtree -r "(objectClass=organizationalUnit)" -l "cn,objectclass,ou"
- Csvde is a command-line tool that's built into Windows Server 2008 in the %windir%/system32 folder. It's available if you have the Active Directory Domain Services (AD DS) or Active Directory Lightweight Directory Services (AD LDS) server role installed. To use csvde, you must run the csvde command from an elevated command prompt. To open an elevated command prompt, click Start, right-click Command Prompt, and then click Run as administrator. Some examples of using this command can show you how it can become overly complicated for the novice user. The following example exports AD data to a file named search.txt, sets the search scope to subtree, and lists the sAMAccountName, CN and distinguished name attributes for each object that's found in the search:
csvde -f search.txt -p subtree -l SamAccountName,CN,Distinguishname
Because AD is based on LDAP standards, you can find a lot of tools that can speak LDAP. Many third-party vendors have released free or fee-based tools to assist you in administering AD. If you need a quick list from an AD OU, look no further than the Active Directory Users and Computers console.
About the Author
Troy Thompson has worked in network administration for over 25 years, serving as a network engineer and Microsoft Exchange administration in Department of Defense, writing technology articles, tutorials, and white papers and technical edits. Troy is a Cisco Certified Academy Instructor (CCAI), and has numerous other certifications including CCNA, MSCE+I, Network+, A+ and Security+. Troy has also traveled the world playing music as the guitarist for the band Bride. Contact information is [email protected].