Selecting the Properties to Export

So far we managed to identify the set of objects we are interested in. However very often we are not interested in retrieving all object properties. Let's say we just want the list of Exchange mailboxes legacyExchangeDN for an ExMerge. We can instruct the export tools to only return specific properties through the -l parameter. Here is an example:
ldifde -d dc=vertical,dc=local -f c:\temp\legacy.txt -r "(& (objectClass=user) (legacyExchangeDN=*))" -l legacyExchangeDN

In all cases both when using LDIFde and CSVde the export will always include the object DN. So in the previous example the export will output the DN and legacyExchangeDN properties for all matched objects.

The -l parameter may also be supplied a comma separated list of property names as follows:
ldifde -d dc=vertical,dc=local -f c:\temp\legacy.txt -r "(& (objectClass=user) (legacyExchangeDN=*))" -l "legacyExchangeDN,mail"

A highly requested export is for extracting all email addresses. We can do this by exporting the proxyAddresses property. We will also use another filter from ESM under Recipients | All Global Address Lists | Default Global Address List. Here is how a CSVde export command looks like:
CSVde -d dc=vertical,dc=local -f c:\temp\alladdresses.txt -r "(& (mailnickname=*) (| (&(objectCategory=person) (objectClass=user) (!(homeMDB=*)) (!(msExchHomeServerName=*))) (&(objectCategory=person) (objectClass=user) (|(homeMDB=*) (msExchHomeServerName=*))) (&(objectCategory=person) (objectClass=contact)) (objectCategory=group) (objectCategory=publicFolder) (objectCategory=msExchDynamicDistributionList) ))" -l proxyAddresses

This will export all addresses including secondary SMTP and X400 addresses. The CSV file will save multiple addresses as a semicolon separated list in order to retain the comma as a property value separator.

It is not unusual for a single user to have multiple SMTP addresses. In this case the primary address, the one appearing as the email sender, will have the address type "SMTP" in upper case. All other addresses should be listed with the address type "smtp" in lower case.

You may be wondering how would you know which property is right for a particular job. What I usually do is to export all properties for one object of the type that interests me. Here I use LDIFde since the format is easier to follow when opened in notepad. Looking at the property name/value pairs returned, I then try to identify the necessary properties. In all cases, when in doubt there is the MS site for property specific documentation.

Viewing the Export Results

One advantage of CSVde over LDIFde is the ability to review the export through MS Excel or MS Access. Access of course gives us the extra power of queries.

When opening the CSVde output from Excel 2003 File | Open, choose the Text file type and select the data file. This takes us to the Text Import Wizard. Make sure to specify the comma as the value separator and double-quotes as the text qualifier.

Complete the Wizard and the result should look something like this:

Opening the file in Access 2003 is nearly identical. Again start from File | Open, choose the Text file type and browse to the data file. This takes us to the Link Text Wizard:

In the first step keep the default and click Next. The wizard should detect the file as comma separated. Make sure to set the check box for 'First Row Contains Field Names'. Also here change the text qualifier to double quotes.

Finish the Wizard and we should have the CSV file linked to an Access Table.

It is now up to our querying skills to single out objects.

Final Tips

This completes our two part article series in exporting Active Directory information. I hope these serve as a starting point for more Administrators to benefit from the available tools.

LDIFde and CSVde provide even more power through their import functionality. We could create new objects or edit thousands of objects with a single command. Of course one has to be very careful considering the important role Active Directory plays.

References

Extracting Information from Active Directory (Part 1)

LDAP Query Basics