Microsoft

Simplify admin tasks by exporting Active Directory 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:

Basic export

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!

Conclusion

CSVDE is one of those little applications that you don't really appreciate or even know exists until you have a specific need to use it. Once you become familiar with it, you will find it's a very quick and easy way of extracting information about specific users or groups of users to be used with other applications or databases. I discovered CSVDE when I needed to audit all users in a specific group-rather than manually compiling a list from certain ‘unreliable' data sources, I thought there must be a way of extracting up to date information from Active Directory and stumbled across this tool.

Now that I'm familiar with CSVDE, I think I'll find use for it on a regular basis, even for simple tasks like cross-checking user accounts with HR's staff directory, checking up on licensing numbers, etc.

Maybe you've found other uses for CSVDE? Leave a comment and let me know how you make use of it.

16 comments
Swin
Swin

It's a shame that you don't outline how to use CSVDE to export user in a specific Group as you indicated that this was the primary reason that lead you to use CSVDE in the first place. This is exactly the need I have, but cannot for the lif of me seem to figure out how to filter the 'memberOf' field.

cdenysschen
cdenysschen

Hi. I'm trying to export only contacts with data in the lelephone number field. -r "(Not IsEmpty(&(telephoneNumber)))" doesn't work. Any ideas

fredtd
fredtd

Justin, Thanks for the tip on how to concatenate filters; this will help me run a more complicated query with one command line. By any chance, do you know if it's possible to append the output to an existing file (if we want to combine the results of multiple queries that we run from a batch file)? Thanks!

merumaru
merumaru

With the AD console open, where do I go to enter the command? for example, this command: csvde -f test.csv as I'm only level 1 support, I don't have access to our Server computer, I only have the AD snap-in installed on my computer.. can somebody enlighten me please?

larry.siegle
larry.siegle

There's also another handy way to "tap" the contents of AD. Win XP users can double-click "My Network Places", then click "Search Active Directory" in the left pane." Between the obvious built in queries & using LDAP queries, you can get an amazing amount of information. For example I can find all the employees of a particular class (secretaries) that work for a particular company, then EMail the resulting list. I can even save the queries for my own use or allow others to run them.

ejsbr
ejsbr

Hi, the steps you show is very good, but how I do to import to A.D.?

saintbklyn
saintbklyn

I think that this is a great tool. I am wondering if anyone can answer a question for me. In my organization AD has been set up in a very backwards manner. What I mean is in order to search through active directory you must know a user first intitial, middle initial and then last name (JMDOE). As I am sure that you can understand this can be truly problematic if you don't have the person middle initial. Is there some way that you can use CSVDE to export the usernames (single column only) stored in active directory and use a program like Excel or acess or maybe word to manipulate the information and then import it back in to AD as last name, first initial, middle initial (DOEJM)?

James Jelinek
James Jelinek

Actually, this was useful. I was trying to do some searches via AD and wanted to export the data to reconcile, goes to show you learn something new every day! Thanks for the good tip!

cdenysschen
cdenysschen

Found it: -r "(&(telephoneNumber=*))" does the trick

PurpleSkys
PurpleSkys

The 'Discussion' forum is for matters of general discussion, not specific problems in search of a solution. The 'Water Cooler' is for non-technical discussions. You can submit a question to 'Q&A' here: http://www.techrepublic.com/forum/questions/post?tag=mantle_skin;content There are TR members who specifically seek out problems in need of a solution. Although there is some overlap between the forums, you'll find more of those members in 'Q&A' than in 'Discussions' or 'Water Cooler'. Be sure to use the voting buttons to provide your feedback. Voting a '+' does not necessarily mean that a given response contained the complete solution to your problem, but that it served to guide you toward it. This is intended to serve as an aid to those who may in the future have a problem similar to yours. If they have a ready source of reference available, perhaps won't need to repeat questions previously asked and answered. If a post did contain the solution to your problem, you can also close the question by marking the helpful post as "The Answer". .

aaronjsmith21
aaronjsmith21

You can use the Windows Power Shell to achieve this. I am not sure of the specifics, but I have used it to create Exchange 2007 accounts. You will have to know some scripting/programming though to get this done. If you are willing to learn, this is a great tool for administrators and scripters. BUT, make sure you really need this import ability a lot, or the time you spend leaning and setting up for it will not be worth the time. You might be better off just doing it the old fashion way.

aaronjsmith21
aaronjsmith21

You do know this method would affectively change your users login names! I left a post for another person on here that wanted to import data, what I talked about there might help you also, but depending on the size of your AD, if it is small, 100 user or less, you might be better off doing this by hand. Also, if your users have email accounts through exchange, this might effect that also, including email addresses!

anders_emajl
anders_emajl

Thanks, I'm currently creating scripts to perform export to CSV. This tool helps alot, I hope. Once again Thanks for a great tip. /a

Justin Fielding
Justin Fielding

Thanks James I'm glad it helped. I only recently came across this little tool and have really found it useful too.

Editor's Picks