Creating a list of Users and their e-mail addresses in Exchange 2000 (2)

In my previous article I presented a script that iterated through all the OUs to export mail enabled users’ mail addresses to a CSV file. Active Directory allows you, instead of going through each and evey the OUs, to perform directory wide searches by constructing an LDAP query. LDAP is a pretty complex and powerful query language. The more precise you want to get in your query, the more it gets difficult to control a query. For example, if I want to query all users in the finance department in Ohio that have mailboxes I would use the following LDAP query:

(&(&(&(& (mailnickname=*) (| (&(objectCategory=person)(objectClass=user)(|(homeMDB=*)(msExchHomeServerName=*))) )))(objectCategory=user)(department=Finance)(physicalDeliveryOfficeName=Ohio)))

As you can see it’s not the easiest thing to construct this, though a little easier to understand, once you get the hang of it. The most difficult thing to get right with LDAP queries is the use of parentheses and ampersands. Miss one and the query will fail or provide different results than those you expected.

Luckily for us Exchange admins we have a very good LDAP query constructor built right into Exchange 2000, so we don’t have to wake up at night breaking in sweat because we can’t count parentheses well.

To create a well constructed LDAP query with Exchange 2000, run Exchange System Manager and create an Address List.

Right click All Address Lists and choose New… Address List

Give the address list a name and press the Filter Rules button.

This easy to use dialog box allows you to easily construct a query.

After entering the query that you desire press the OK button and then Finish.
Now you may ask, “Where is LDAP query”? On the property page of the newly created address list you can view it.

You can also press the Preview button to see the results of your Query.

Such LDAP query above can be copied and used in your script.

Now, back to our script. The beginning remains the same. We want to get the domain name and create a text file.

Dim rootDSE, domainObject
Set rootDSE=GetObject(“LDAP://RootDSE”)
DomainContainer = rootDSE.Get(“defaultNamingContext”)
Set fs = CreateObject (“Scripting.FileSystemObject”)
Set userFile = fs.CreateTextFile (“c:\users.csv”)

Now we want to open a channel to Active Directory:

Set conn = CreateObject(“ADODB.Connection”)
conn.Provider = “ADSDSOObject”
conn.Open “ADs Provider”

After opening a channel we construct the LDAP query. To construct this query I used the built-in All Users address list LDAP string (shown here in bold).

ldapStr = “<LDAP://” & DomainContainer & “>;(& (mailnickname=*) (| (&(objectCategory=person)(objectClass=user)(!(homeMDB=*))(!(msExchHomeServerName=*)))(&(objectCategory=person)(objectClass=user)(|(homeMDB=*)(msExchHomeServerName=*))) ));adspath;subtree”

Then we actually execute the LDAP query.

Set rs = conn.Execute(ldapStr)

So, now we’ve got an array of user records (rs) that we can use. We go through every member of rs and write its properties to the file.

While Not rs.EOF
    Set oUser = GetObject (rs.Fields(0).Value))
    userFile.Write oUser.displayName & “,” & _
   oUser.sAMAccountName & “,” & oUser.userprincipalname & “,”
      for each email in oUser.proxyAddresses
          userFile.Write email & “,”
       next
      userFile.WriteLine “”
      rs.MoveNext
Wend

For your convenience, here is the complete script:

Dim rootDSE, domainObject
Set rootDSE=GetObject(LDAP://RootDSE)
DomainContainer = rootDSE.Get(“defaultNamingContext”)

Set fs = CreateObject (“Scripting.FileSystemObject”)
Set userFile = fs.CreateTextFile (“c:\users.csv”)

Set conn = CreateObject(“ADODB.Connection”)
conn.Provider = “ADSDSOObject”
conn.Open “ADs Provider”

ldapStr = “<LDAP://” & DomainContainer & “>;(& (mailnickname=*) (| (&(objectCategory=person)(objectClass=user)(!(homeMDB=*))(!(msExchHomeServerName=*)))(&(objectCategory=person)(objectClass=user)(|(homeMDB=*)(msExchHomeServerName=*))) ));adspath;subtree”

Set rs = conn.Execute(ldapStr)

While Not rs.EOF

Set oUser = GetObject (rs.Fields(0).Value)
userFile.Write oUser.displayName & “,” & oUser.sAMAccountName & “,”
      for each email in oUser.proxyAddresses
          userFile.Write email & “,”
       next
        userFile.WriteLine “”
      rs.MoveNext
Wend