Wednesday, February 24, 2010

CSVDE command to export User Accounts in Active Directory

Example 1: The plain CSVDE -f export switch

Our mission: To export all users and computers into a file called adusers.csv and then to read this export data in Excel.

Preparation: Open up a command prompt, I use Start, Run, CMD.

Type this command: CSVDE -f adusers.csv

To see instantly the result of your command, type: notepad adusers.csv. For a more useful view of your export, open a spreadsheet like Excel, click File (menu) Open, select: Files of Type and change to All files, now browse to the directory where you created adusers.csv.

Learning Points

Note 1: The -f switch specifies the filename.

Example 2: Filter the output with CSVDE -d

What the -d filter does is focus the export on one particular area. For example, suppose you only wish to export one OU and not the whole Active Directory domain.

Command: CSVDE -d "OU=Newport,DC=cp,dc=com" -f Newport.csv.

If you try this filter, make sure you substitute your OU for Newport and your domain for dc=cp. Observe carefully the syntax with those speech marks and commas between the elements. Avoid the trap of being so concerned with the -d switch that you forget the -f filename.csv.

Example 3a: Filter the rows with CSVDE -r

If you use CSVDE with only the -f switch, then you probably get too much data. For instance, you may only be interested in the user objects and not the OU or binary objects. In which case you need a filter to remove the unwanted data.

There are two way to filter the output, row-wise or column-wise. With the -r switch you can select rows which meet your criteria, for example object = user.

Command: CSVDE -f adusers.csv -r objectClass=user

Learning Points

Note 1: Let us concentrate on the syntax of the filter switch: -r objectClass=User. There is no comma in this command, just a space between the switch and the parameter. What I mean is there should be no space between the minus sign and the switch. -r is correct. However, - r (with a space) would be a mistake.

Note 2: A Surprise. Even though you specifically ask for objectClass=user, CSVDE returns both user and computer objects. I didn't know why this happened until Jon de R. kindly wrote in and said we need the objectCategory=person filter.

Jon de R. suggests: CSVDE -f onlyusers.csv -r objectCategory=person.

Clive B points out: objectCategory=person also includes 'contacts'.

Warning: Sometimes when you copy and paste these CSVDE commands they do not work. What happens is that ‘copy’ picks up a phantom ° symbol, which blocks the proper execution. All you get is a list of the commands, just as if you had typed CSVDE /? My answer was to check in ‘Word’ for any extra symbols. If all else fails, I type the command manually at the command prompt.

Sometimes closing the CMD prompt window, then re-opening magically clears the problem. When you start a second CMD prompt, CSVDE suddenly works, whereas previously it failed. I only solved my latest mystery error by logging off and logging on again. I say again if CSVDE fails or encounters an error it seems to freeze when you subsequently issue a perfectly valid command.

Example 3b Fine tuning the CSVDE -r filter

Conclusion: after hours of frustration, my advice is avoid these advanced features. While it is possible to refine the -r switch, my advice is stick to the basics of Example 2a.

O.K., so really want to master this -r switch. If you add brackets, speech marks and & (ampersand) then you can produce a more focused output. In my humble opinion, the normally excellent help is a bit sketchy on this -r switch. I found experimenting yielded results whereas slavishly following the instruction lead to disappointment.

Here is a command that filters users whose surname = Thomas.

CSVDE -f export.csv -r "(&(objectClass=user)(sn=Thomas))"

Here is a variation using a wild card *

CSVDE -f export.csv -r "(&(objectClass=user)(sn=Thom*))"

Note: Suggestion from Vinnie

I though you might find it useful that this command string only produced, users and no contacts.
CSVDE -f onlyusers.csv -r "(&(objectClass=user)(objectCategory=person))"

No comments:

Post a Comment