Windows

Video: Simplify admin tasks by exporting Active Directory data with CSVDE

Active Directory can hold valuable user data that you can use for everyday tasks, like auditing user accounts and checking software licenses. In this IT Dojo video, Bill Detwiler shows you an easy way to extract the data stored in Active Directory using the Comma Separated Value Data Exchange (CSVDE) command.

Active Directory can hold valuable user data, including contact information, their departments, and even office locations. Using this data source for administrative purposes can save you time when performing everyday tasks like auditing user accounts and checking software licenses. In this IT Dojo video, I show you an easy way to extract the data stored in Active Directory using the Comma Separated Value Data Exchange (CSVDE) command.

The CSVDE command is a tool you may never know about, unless you just stumble across it. But once you find it, you'll find all kinds of cool things it can do. For example, you can generate a dataset from an OU called 'Directors' that contains each user's given-name, surname, and default e-mail address using the command (all on one line):

csvde -d "OU=Directors,DC=domain,dc=local" -f test.csv -r "(&(objectClass=user)(objectCategory=person)" -l " givenName, sn, mail"

This is just one of many ways to use the CSVDE command. For more information on using the CSVDE command read Justin Fielding's article, "Simplify admin tasks by exporting Active Directory data with CSVDE." From the article page, you can print Justin's tip, save it to your TechRepublic Workspace, e-mail it to a friend or colleague, and even Digg it.

For more Active Directory advice, check out the following TechRepublic Resources:

About

Bill Detwiler is Managing Editor of TechRepublic and Tech Pro Research and the host of Cracking Open, CNET and TechRepublic's popular online show. Prior to joining TechRepublic in 2000, Bill was an IT manager, database administrator, and desktop supp...

10 comments
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?

stephen.yale
stephen.yale

Can CSVDE show me group membership? I am looking for a fast tool to get me group membership. I want to list out all members of an AD group with member attributes such as samaccountname, email, first name, last name etc. I am using VBA and ADO to get to the data right now but I want to get the data using SQL Server and getting around the 1000 row limit. I will pass a set of group names and have it return the users with the user detail.

klewis
klewis

Nice, but why not make things easier still and use the free utility AD Query from SysOp Tools > www.sysoptools.com? Some of the important data in AD is in binary or tick-string format, so if you want to look at things like last logon date, password last set date, etc you have to do other (painful) conversions. AD Query does it all for you. (Thanks I will take my black belt now..)

TexasJetter
TexasJetter

I use LDIF to extract AD to a file for a backup, but I was unaware of CSVDE. It certainly presents it in a more usable format for reports. However unless the CSV file can be imported (as the user above asks) I would consider it an addition, not replacement for my current tasks.

zen116
zen116

Hi, Is it possible to extract the user accounts from the SMS server in CSVDE and import this into the Active Directory?

Bill Detwiler
Bill Detwiler

In this IT Dojo video, I show you an easy way to extract the data stored in Active Directory using the Comma Separated Value Data Exchange (CSVDE) command. Original post: http://blogs.techrepublic.com.com/itdojo/?p=133 For example, you can generate a dataset from an OU called 'Directors' that contains each user's given-name, surname, and default e-mail address. Do have a creative use for the CSVDE command?

Dark_Knight
Dark_Knight

Can this tool export data and filter it on required attributes? I have looked at the site and I am unable to determine this. The use of this tool for one a couple of objects if all you want to do is look at their settings, but when you need to find all objects in you AD that contain certain attributes and have the ability to use logic statements like AND, NOT, OR, I do not see this tool delivering the required results that I and many domain admins of large deployments need. I could be wrong, have been in the past and will be in the future, this is how I lean.

Dumphrey
Dumphrey

is only fr pulling out data from AD, and as such, you are right, its not a one stop shop tool, but it does have a ton of possibility for reporting and data mineing.

klewis
klewis

yep you can export the results to an xml file, and searchs are saved in a memory buffer so results of all the searchs can be exported together. It is designed IMO as a surgical tool to quickly see all schema attributes for a given user or computer object. There are a number of schema attributes that if exported without converting to "human readable" format look like gibberish. The AD Query tool does the conversion automatically. I'd say if you just need a general bulk list of AD names and plain-text data then use the method in the thread topic. AD Query's value seems to come into play when you are troubleshooting object issues or just need to reference object data not viewable in the ADUC MMC. For example, kerberos tickets and delegated trust, last logon time, password last set, home MTA, exchange attributes, etc etc. - Anyway I've found it very handy. There's a user guide for AD Query located on the support page of their website.

Dark_Knight
Dark_Knight

Though I have not used the add function of CSVDE it can be used to create (Add) new objects, it can NOT be used to modify them. LDIF can do that, but it is a little cumbersome. I have been using CSVDE to get quick exports from my AD of over 10K user objects for years. Combined with a vbscript that I wrote to update the data I have yet to need anything else. Additionally you can use CSVDE to filter out information by not only combining different types of attributes, but by also using other logic such as NOT and OR. Bottom line this is a tool all AD administrators should know.