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.

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.

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.