Simplify admin tasks by exporting AD data with CSVDE
Depending on how thoroughly you use it, Active Directory can hold some very valuable information on your company and its staff. Not only does the directory contain the basic credentials of each of your users, but if fully populated, it will hold the users’ contact information, including e-mail addresses and telephone numbers, their department and even office location. This information can be very useful from an administrative point of view. Quite often projects will require a list of all users within a specific group or department-manually compiling this information can be a lengthy process, and it can quickly become out of date. Wouldn’t it be great if we could extract the information stored in Active Directory for use in spreadsheets and checklists? Actually we can and it’s not complicated at all!
CSVDE, what is it?
CSVDE stands for Comma Separated Value Data Exchange. CSVDE is a small command-line tool that is included in Windows 2003 installs by default - usually found in the %windir%/system32 directory. The application can also be installed on a Windows XP workstations as part of Active Directory Application Mode (ADAM).
The comma-separated value (CSV) file format is relatively simple. Usually presented in plain text format, the CSV file contains data that has been separated into columns with a standard comma (,) separating each one. Each row of data is presented on a new line. CSV is often a good file format to use when extracting raw data as a majority of applications designed to import and manipulate data (such as Microsoft Excel or OpenOffice Calc) will accept it without modification and neatly organise it into rows and columns.
CSVDE has two core functions: importing data from a CSV formatted file into Active Directory and exporting data from Active Directory into a CSV formatted file. While I have seen many people discussing CSVDE’s import functions, I would generally advise against this unless you are one hundred percent familiar with both CSVDE and Active Directory and there is no other way to complete your task. It’s the export function of CSVDE that draws my interest-let’s take a look at what we can do with it.
Exporting data from the directory
As we are only going to look at CSVDE’s export functions, there’s no chance of damaging the directory or accidentally corrupting its data. Therefore I think the best way to understand it is to jump right in:
The basic export will dump all records from Active Directory into a CSV file without any filtering of results. By default CSVDE will bind to AD using the credentials of the users currently logged on-to specify different credentials use the -a flag (-a UserDN [Password | *]).
To export all data from Active Directory into a file called test.csv:
csvde -f test.csv
After the export has completed a message will tell you how many entries were exported. These ‘entries’ translate into rows of data, and you will probably notice that there are a great deal more entries than users. This is because without any filtering CSVDE will simply dump the entire contents of your directory into a CSV file; that includes groups, system accounts, and other hidden values which may or may not be of use to you. If you open test.csv with notepad, you will be able to take a quick look at the data and all will become clear.
Narrowing down results
There are three ways of narrowing down and filtering the output of CSVDE. The first way of excluding unwanted results is to filter down to a specific part of the directory using the -d flag. Using this we can focus the export on a particular OU within the directory structure:
csvde -d “OU=MyOU,DC=domain,dc=local” -f test.csv
If you arrange your users into a specific OU then this can rapidly reduce the number of unwanted records exported to your CSV file.
To further reduce the number of unwanted records exported, we can use the -r flag. The -r flag is used to filter out rows of data based on the specific properties of that record. In this example I want to find users and computers only:
csvde -f test.scv -r objectClass=user
Or to use multiple filters:
csvde -f test.csv -r “(&(objectClass=user)(objectCategory=person)”
This will return the users without computers.
If you aren’t sure about the names and/or values of specific property fields, I would recommend using adsiedit.msc to browse the directory.
As might be expected, both of these filters can be combined. Let’s say I want to export a record of users within the OU called ‘Directors’:
csvde -d “OU=Directors,DC=domain,dc=local” -f test.csv -r “(&(objectClass=user)(objectCategory=person)”
Now you’ve probably noticed that even if you filter down to only the rows of data that you specifically want, we still end up with a lot of unwanted information. Let’s take a look at how we can resolve that.
In the same way that the -r flag will filter out rows of unwanted information, we can use the -l flag to filter out columns of unwanted data. Let’s say that I want the same results returned as in the previous query but I only want the given-name, surname, and default e-mail address of the user to be exported:
csvde -d “OU=Directors,DC=domain,dc=local” -f test.csv -r “(&(objectClass=user)(objectCategory=person)” -l ” givenName, sn, mail”
That was straightforward wasn’t it! Again if you are unsure of the property name for a specific piece of information take a look at the directory structure with adsiedit.msc; just be careful not to accidentally modify anything!