Intelligent Message Filter, Content Filter, can do more...

WinDeveloper IMF Tune
WinDeveloper IMF Tune
  • Home
  • General
  • Extracting Information from Active Directory (Part 2)

Extracting Information from Active Directory (Part 2)

Alexander Zammit

Alexander Zammit Photo

A Software Development Consultant with over 20 years of experience. Many of his projects involved Exchange integrated applications, including a FAX server, a mail security product and anti-spam products.

  • Published: Feb 27, 2007
  • Category: General
  • Votes: 3.0 out of 5 - 1 Vote
Cast your Vote
Poor Excellent

An Active Directory export may easily return an overwhelming number of results. Filtering the exported objects, limiting the set of returned properties, and loading the data in Excel or Access allows us to stay in control.

In the first part of this article we learnt how to perform basic directory exports. We did this by identifying the distinguished name DN of the object or container and dumping everything to a file. However when dealing with a large number of objects, it is normal to require a little more control on what is exported. For example if we are only interested in email addresses, we don't have to export all the object properties. Also we might only be interested in users from a specific department.

Selective Object Export

Let's say we want to export all users from all organizational units OUs. We could use one of the following commands:
ldifde -d dc=vertical,dc=local -f c:\temp\allusers.txt -r (objectClass=user)
csvde -d dc=vertical,dc=local -f c:\temp\allusers.txt -r (objectClass=user)

Here I started the export from the root of the Domain Naming Context (dc=vertical,dc=local) but filtered the exported objects using the -r parameter. Without the filter we would have exported all objects as from the root. This leads to a few questions. How do we construct these filtering conditions? What is objectClass by the way?

The filter is a condition that must be tested against all objects that are about to be exported. "objectClass" is a property name and "user" is the required property value for the object to be exported. Constructing these conditions can get very complex for the following reasons:

  1. We need to understand the meaning of the properties used in the filter (objectClass in this example).

  2. We need to understand how to correctly encode these filters. This becomes complex especially when combining multiple conditions with the AND, OR and NOT operators.

Constructing complex conditions by hand is something I prefer to avoid. We don't really have to either, since Exchange provides us with a nice wizard. Start by opening the Exchange System Manager and browse to the address lists.

ESM Address Lists

Open the properties for the All Users address list, to get an idea of how a complex filter condition looks like.

All Users Address List Filter

We could easily reuse this filter to see which users match the All Users Address List. I will just replace the filter in the previous commands with the one copied from the ESM. Note that I also enclosed the filter in double quotes.
ldifde -d dc=vertical,dc=local -f c:\temp\allusers.txt -r "(& (mailnickname=*)(| (& (objectCategory=person) (objectClass=user) (!(homeMDB=*)) (!(msExchHomeServerName=*))) (& (objectCategory=person) (objectClass=user) (| (homeMDB=*) (msExchHomeServerName=*) ))))"

We can also use the ESM to create a filter from scratch. Right click the All Address Lists and select New | Address List.

New Address List

Enter a Filter Name and click on the Filter Rule button.

Filter Construction UI

This takes us to the standard Active Directory Search dialog. Use this to identify the objects of interest starting from the Find drop-down list at the top. Even though the Exchange team meant this to create an address list filter, we could construct filters for any type of AD object under the Domain Naming Context.

Click the Find Now button to see which objects match the filter and click OK when ready. Clicking Finish in the Create Exchange Address List will save the new list. Now we can get to the filter raw text by opening the newly created address list object properties. When done just delete the new address list.

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.

Excel Text Import Wizard

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

Exported Data in Excel

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:

Access 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.

Access CSV Configuration

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

Exported Data in Access

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

Copyright © 2005 - 2024 All rights reserved. ExchangeInbox.com is not affiliated with Microsoft Corporation