Part One – CSVDE
Often, using command line utilities are far more convenient and powerful than their graphical counterparts – you all know that. But, the learning curve to become accoomplished at them is typically far more difficult. And, because many of us use the command line utilities somewhat infrequently, memorizing syntax can become a burden. Hence the GUI is often chosen. In this post I’d like to focus attention on certain CLU particularly when dealing with Microsoft’s Active Directory database.
Over the last several years, I’ve captured some nice information regarding managing and manipulating AD databases. Of course you all know AD is based on LDAP so the utilities must be able to interface with LDAP. And, because LDAP is a standard – for the most part – throughout the industry, you can even use non-Microsoft LDAP tools. This you also know, of course when you choose to use software from such companies as Quest.
This is the first part of several articles that will be posted to this blog. Part One covers CSVDE, while future articles will highlight other tools and strategies. Part Two will particularly deal with LDIFDE.
I’d like to focus on certain utilities and strategies to enter, modify, delete and export information into and out of AD. One point that bears mentioning is that often consultants will use virtualization via either a Microsoft virtual product or a VMware product. These are valuable tools to provide POC (Proof of Concept) for capabilities and functionality – but not scalability or performance. Therefore, it’s necessary to populate these test or pilot virtual environments with "real data" and these utilities can do just that.
Some of the tools I’ll showcase in the series of articles are Microsoft born and bred. Others are from other parties. I’ll mention the origin and author of each utility when possible and whatever information I know of to provide more value.
I would rate this article at a level of 100 or maybe 200. This information should already be very well known by seasoned admins/consultants. If you are an experienced admin/consultant, much of this information will already be known to you.
Depending on where this article may appear, I’ll provide my e-mail address here for those wanting to send comments or complaints or to tell me my boo-boos. You can reach me at: mmyers@pointbridge.com.
One last thought – as you all know, there are many ways to accomplish a given task in the Windows world. This article discusses object manipulation from a command line-centric point. There are many GUI tools available; some from Microsoft and some others from other parties. And, the scripting world offers plenty assistance when it comes to maniuplating Active Directory database contents. In other articles, I’ll provide some insite into using these other alternative methods to manipulate AD besides the command shell.
CSVDE Csvde.exe is included in both Microsoft Windows 2000 and Server 2003 Operating systems, located in the SystemRootSystem32 folder after you install the products. Csvde.exe is similar to it’s cousin Ldifde.exe, but CSVDE extracts information in a comma-separated value (CSV) format, rather than a line-delimited format as LDIFDE does. You can use Csvde to import and export Active Directory data that uses the comma-separated value format. You an use a spreadsheet program such as Microsoft Excel to open this .csv file and edit the file easily.
Note, although Csvde is similar to Ldifde, Csvde has a significant limitation: it can only import and export Active Directory data by using a comma-separated format (.csv). Microsoft recommends that you use the Ldifde utility for Modify or Delete operations. Additionally, the distinguished name (also known as DN) of the item that you are trying to import must be in the first column of the .csv file or the import will not work. In case you’re not familiar with this format, you’ll see an example below shortly.
CSVDE gets it name from:
Comma – Delimit data with a comma. CSV is an ideal format for Excel.
Separated – Split a string of data, so that each property fits into an LDAP column.
Value – The focus is on the values, not properties.
Data – Again concentrates on the data, and not about the objects.
Exchange – Import/export data from the spreadsheet into Active Directory (or vica versa)
CSVDE works with AD data in only two directions: importing and exporting. The data must exist in CSV format when importing, and the data will be placed in CSV format when it’s exported. The syntax with CSVDE and LDIFDE are pretty simple and consistent – almost all of the command syntax for CSVDE is identicle to LDIFDE. You learn one and you’ve got it down for the other.
To successfully execute CSVDE or LDIFDE commands, you can use the utilities directly from a domain controller or a member server. However, Windows XP workstations will not have these two utility files by default. Simply copy over the file structures, csvde.exe and ldifde.exe from the %windir%system32 directory from a DC or member server to your XP machine.
Basic Export Operations
To get started, log onto a AD domain from a member workstation or server, and open a command prompt and type
csvde -f <drive><path><filename.csv.
The -f switch indicates a file is to be used – in this case, you are exporting from the AD domain the workstation authenticated to. If you don’t specify an import operation, the default is to export the data. The -f switch followed by the <filename.csv> file name creates this file with your AD data within. You may call the file anything you wish with any extension you wish.
Of course, you can open this file in Notepad or some editor now, but if you have access to Microsoft Excel, it’s easier to read the CSV file from within Excel. You will see upon opening the file, each record or AD object will be one per row in the file. The first row is reserved for the LDAP field names with a particular order. The data records will conform to the field order with values – each value separated by a comma.
Basic Import Operations
To import contents from a CSV file into AD, you will use the -i switch. An example would be:
csvde -i -f c:CSVDEImport.csv
This imports the file called CSVDEImport.csv. There are some common-sense details you need to know about executing an import operation. I’ll cover some of them a little later, but for now remember, when importing, you are creating objects in the AD database. Existing duplicate-named objects will cause errors, as well as trying to execute the commands while logged on with inadequate credentials.
For example, if you attempt to import objects that already exist in AD, you’ll see an error message CSVDE generates similar to the following:
C:WINNTsystem32>csvde -i -f CSVDEImport.csv
Connecting to "(null)"
Logging in as current user using SSPI
Importing directory from file "CSVDEImport.csv"
Loading entries.
Add error on line 2: Already Exists
The server side error is "An attempt was made to add an object to the directory with a name that is already in use."
0 entries modified successfully.
An error has occurred in the program.
You will be required to either logon with an account with proper permissions, or use the runas method of executing the command when logged on with an account lacking permissions to create objects in AD.
I found my LDAP knowledge a little lacking, so one trick is to perform an export, open the file and study the LDAP field names and the values to which those field pertain to. If your LDAP field name knowledge is a little shaky, simply study the first row of the CSVDE export file to learn the LDAP names for the data you are interested in. Knowing these field names will provide you with the syntax needed later on when you need to have CSVDE, or LDIFDE, act on only the fields you wish to pick on.
If CSVDE is fairly new to you, then start by exporting from a test AD forest environment. Remember, when you import, you will be attempting to make changes to AD. Adding test objects not needed to an existing production AD forest is very bad. Thoroughly explore the import and export operations before executing the commands in a production forest.
Export a Given Type or Object Operations
A common desire is to export objects meeting a given object type. For example, the command
csvde -f CSVDExport.csv -r objectClass=user
will export all user objects ignoring most other objects. An interesting issue is when using the objectClass=user designation, you will also see computer objects as well.
You can combine LDAP properties as in this example here:
CSVDE -f CSVDExport.csv -r "(&(objectClass=user)(sn=Jacobs))"
which exports all user objects with a last name, or surname, of Jacobs.
You are also allowed to incorporate wild cards at times, as in this example:
CSVDE -f CSVDExport.csv -r "(&(objectClass=user)(sn=Jac*))"
which will retrieve users with a last name starting with the characters JAC and including all other combination of characters.
Scope Export Operations
The above CSVDE examples will act upon AD data from the entire domain. Often, you’ll want to only export data from a given OU. The -d switch sets the distinguished name of the search base for data export. The -d switch is used here, as in this example:
CSVDE -d "OU=Sales,DC=Acme,DC=com" -f CSVDExport.csv
Note that the -d switch will act upon the named container and include all sub-containers as well. If the Sales OU has several child OUs within, those will also be included in the export – not just the Sales OU contents.
When using the -d switch (meaning BaseDN), you might find it valuable to add the -p switch (Scope). In this next example, I’ve added the -p Base switch which means the export will ONLY include the BaseDN object; i.e. only the OU Sales and nothing within:
CSVDE -d "OU=Sales,DC=Acme,DC=com" -p Base -f CSVDExport.csv
This is of little value in this specific example, because I’m looking for all objects in the Sales OU. This export will only export the properties of the Sales OU object only.
Probably more effective is the switch -p OneLevel which acts upon the Sales OU and objects directly child to the OU, but not child OUs. Here’s an example:
CSVDE -d "OU=Sales,DC=Acme,DC=com" -p OneLevel -f CSVDExport.csv
Targeting Specific Servers
Typical daily use of this command would be from an XP machine. Therefore the specific DC being queried would be left to the authenticating DC the XP machine connected to. To target a given DC, use the -s switch (ServerName) as in this example:
CSVDE -d "OU=Sales,DC=Acme,DC=com" -p OneLevel -f CSVDExport.csv -s DomCont1
Note the server name entered is the netBIOS name – not the FQDN.
Verbose Mode Operations
Some other switches that are available for use with CSVDE are listed below. Often you want more information than what is displayed by default. Include the -v switch to kick the command into verbose mode. This will echo on the screen more information, such as every object processed. This verbose option will not add to the export file contents that I’ve seen, but it does make the echo on-screen more informative.
Most administrators/consultants will want some proof of what the command accomplished. Use the -j switch to set a log file location. The default location is the current path but point to a different drive mapping/path if you want to log file to be created in a different directory. Of course, remember share and NTFS permissions must allow the file to be written. The file will be called csv.log, and it will contain the connection/authentication information required to connect to the DC, as well as entries echoing the objects exported. Lastly, it will include the last line indicating if an error occurred or that the command executed successfully. Note that the -j switch allows you to specify the log file path not the log file name. The log is named csv.log.
Specifying Ports
For more granular control of the service you wish to query you can incorporate the -t switch. The -t switch will allow you to select the port the query is executed against. Of course, port 389 is the default Domain Controller port while 3268 is the Global Catalog port. By targeting the CSVDE command to the 3268 port, you are purposely choosing the Global Catalog service to be queried rather than the DC or domain partition to be queried.
Handling Errors
One very irritating result of a CSVDE command is an error that gets produced when something goes wrong. Now, I’m not complaining that I get an error – error messages can be very welcome when something goes wrong. But, if I’m trying to import 5000 user objects into Active Directory and two out of the 5000 objects have some configuration that CSVDE doesn’t like, the first error encountered will halt the entire operation. By default, the first error to occur will cause CSVDE to immediately abort the operation. Sometimes what I want is the error to get logged and the CSVDE tool to just keep going. This is where the -k switch can come in handy. This switch will allow the utility to generate an error message but yet keep the import/export process going none the less. So in my earlier example, 4,998 objects would get imported and two would not.
Let me give you a real-world example. Lets assume I want to import some 5000 user objects into AD. Study for a moment the excerpt of a sample CSV file that could be used to populate the utility with the users I wish to create below:
Notice the third row for Monica Brink. Let’s assume her OU was "OU=NWTaders Users". Now her record in the excerpt from above is in fact accurate and syntactically correct, but note in this paragraph my reference to her OU being mispelled. I have typed here "OU=NWTaders"; I’ve left out the "r". So again, assume I’ve spelled it wrong in the CSVDE import file. When the import command is given, the first object, Olinda Turner is created and Keith Harris is also created. But, if Monica’s OU was mispelled like "NWTaders" and there is no such OU spelled this way, CSVDE will generate an error indicating it could not find an OU with that name, and the utility will abort the operation only creating the first two user objects. OK, no big deal – you quickly determine your error and you would fix the spelling for Monica’s OU path – probably using something like Notepad to edit the file. Now you close the corrected edited file and save your changes. Now, you’ll probably revisit your command window and hit the up arrow key to re-run the previous command. So now the CSVDE utility kicks off, and yet another error gets generated. Can you tell what the error is now? By you simply re-running the CSVDE command against the same import csv file, you will generate an error for the first line – Olinda Turner. Her object was successfully created the first time you ran the utility. Now, re-running it will attempt to create a user object with the same name. At this point you have two options – either open up ADUC and delete both Olinda and Keiths user object and re-run the utility, or open up the CSVDE text file to delete the first two user rows, and re-run the utility. Either option is not ideal if you ask me. So, here’s where the -k switch comes in. When you run this utility, use the -k switch. Any errors will result in an entry flashing on your screen, but the rest of the 4998 users would be created leaving you with the task of creating the two remaining user objects. Very nice.
Authentication Credential Operations
If you want CSVDE to run under a different identity for authentication purposes, you can incorporate the -a and -b switches. Note the -a switch sets the UserDistinguishedName Password combination while the -b switch sets the UserName Domain Password combination. By default, the command runs using the credentials of the user currently logged on to the network.
Works with Exchange Data
One CSVDE detail worth mentioning here is that it works great at exporting AD information having Microsoft Exchange properties and values. For example, the following command selects to export information from AD including proxyAddresses and physicalDeleiveryOfficeName properties.
csvde -f c:CSVDExport.csv -r "(&(objectClass=user)(proxyAddresses=*))" -l "mailNickname,samAccountName,physicalDeliveryOfficeName,proxyAddresses" -j c:log_path -v
Miscellaneous Switches You Might Find Helpful
By using the -n switch, you will be asking CSVDE to export data and omit any data that would be represented in binary format – likely data you wouldn’t be interested in anyway. Speaking of perhaps uninteresting data, by using the -m switch, you are omitting AD property information such as the ObjectGUID, objectSID, pwdLastSet and samAccountType attributes. Some of these attributes – such as the SID – could be very valuable to admins and consultants.
