Creating an ADO Recordset from an Exchange 2000 Address list

The following article is a side article to my Web GALMOD article to detail the way Active directory can be queried to return recordsets that can be used in this and other applications. It should give you a starting point to the methods available for doing this as well as giving a guide to customising the Com object used in my other article.

Querying active directory via ADSI and LDAP

There are two ways to query active directory via LDAP (that I know of anyway) to create an ADO recordset.

The first way is to use an LDAP filter, Within Exchange 2000 if you look at the properties of any Addresslist via Exchange system manager  you will see the LDAP string that is used to produce this address list. As shown below

This is what I have referred to as an Ldap filter essentially all of your address lists in Exchange are just different LDAP filters that are applied to Active Directory. These LDAP query strings are called  the "purportedSearch" property in Active directory and you will find that all your address list objects have this property which will contain these Ldap query strings (when you view the object in ADSI edit).  Lets look at an example of creating a recordset that contains the Global Address List on your Exchange server

DomainName = "your.domain.net.au"
strDefaultNamingContext = "DC=your,DC=domain,DC=net,DC=au"

GALQueryFilter =  "(& (mailnickname=*) (| (&(objectCategory=person)(objectClass=user)(!(homeMDB=*))(!(msExchHomeServerName=*)))(&(objectCategory=person)(objectClass=user)(|(homeMDB=*)(msExchHomeServerName=*)))(&(objectCategory=person)(objectClass=contact))(objectCategory=group)(objectCategory=publicFolder) ))"
strQuery = "<LDAP://" & DomainName & "/" & strDefaultNamingContext & ">;" & GALQueryFilter & ";distinguishedName,TelephoneNumber,homephone,pager,facsimiletelephonenumber,mobile,postalcode,GivenName,sn,title;subtree"

Set oConn = CreateObject("ADODB.Connection") 'Create an ADO Connection
oConn.Provider = "ADsDSOOBJECT"              ' ADSI OLE-DB provider
oConn.Open "ADs Provider"

Set oComm = CreateObject("ADODB.Command") ' Create an ADO Command
oComm.ActiveConnection = oConn
oComm.Properties("Page Size") = 1000
oComm.CommandText = strQuery
oComm.Properties("Sort on") = "givenname"

Set rs = oComm.Execute

With Sorting the recordset that is produced by the above script you need to use the "Sort on" property of the ADO command object. Active directory only supports sorting on one Indexed field. So in the above example my recordset will be sorted on FirstName but for example if there where two people with the same first name the sort order for these records will be based on creation time (For this type of idea its better to include displayName in your query and sort on this property this is how the Outlook address book does it) . To support sorting on multiple fields eg FirstName and LastName you need to look at using the SQL syntax in your LDAP query this allows you to do a Ordery By in you query. So to rewrite the above example in SQL syntax this would look like.

Set oConn = CreateObject("ADODB.Connection")
Set oComm = CreateObject("ADODB.Command")
oConn.Provider = "ADsDSOOBJECT"              ' ADSI OLE-DB provider
oConn.Open "ADs Provider"
oComm.ActiveConnection = oConn
oComm.Properties("Page Size") = 1000   
oComm.CommandText = "select distinguishedName,TelephoneNumber,homephone,pager,facsimiletelephonenumber,mobile,postalcode,GivenName,sn,title from 'LDAP://yourserver/DC=your,DC=domain,DC=net,DC=au' where objectClass= 'user' order by givenname,sn"
 
Set rs = oComm.Execute

The following script will create a recordset of all the user accounts in Active
directory and sort it via givenName and Surname. The one disadvantage i have found with
using the SQL syntax vs query filters is the SQL syntax doesn't seem
to except NUL queries. So if I put a where NUL or where ''" to show all the
mailboxes that don't have the postcode set these type of queries don't work. So in 
some cases with this type of query you end up with a bigger recordset then you wanted and you  have to
put some logic in your recordset processing code they can deal with filtering or highlighting certain active directory properties that you couldn't filter in the where clause.

Getting the field Names for your Query's

One of the hardest things when writing one of these queries is finding out what the Active Directory property fieldnames are called. For example the Active Directory field called L refers to the users city or sn  refers to Surname. This can be a bit confusing at times the best way to get a list of all the properties available for you is just to do a CSVDE dump of one of your Active directory OU's and then just read the header file it creates. Or you can find the field names using ADSI edit to look at the properties of a user object or just have a look in the latest Platform SDK.

What to do with the Recordset once its produced

Now you've produced a recordset what can you do with it ? one idea is to display the results as a web page. In this case it would be an active serverpage and you would have to rewrite some of the above code to use server.createobject to work inside an ASP page. The other thing to remember if you where going to use this code by itself inside a web page is that if you leave anonymous authentication enabled on the directory where this page is located the code itself will run under the context of the anonymous web account on your web server which is more then likely not going to have the rights necessary to query Active directory. Other useful things you could do with a recordset is it to populate a database, excel spreadsheet or word mail merge.

Changing the Queries defined in the GALMOD Com object

With the Gal Mod Com object from my other article the showGal function in this object performs two queries using code simular to what I have shown above. The first query is used to search active directory for the user who submitted the query so it can get the users department they belong to so this can be used in the next query. The next query uses this information that was derived from the first to create a recordset of all users in the submitting users department. If you wanted to change the way these queries work to return all the users in say a users office then you would have to change a few things in the Com object code. The first thing you need to do is to add the properties you want to filter on too the first query in the ShowGal function. eg to add office it would look like

strQuery = "<LDAP://" & DomainName & "/" & strDefaultNamingContext & ">;" & GALQueryFilter & _
";distinguishedName,displayName,physicaldeliveryofficename,msExchHideFromAddressLists;subtree"

You then need to create a new LDAP query string to be used by the second query as I have mentioned already in this article the best way to do this is to go into Exchange System Manager and create a temporary address list and then cut and past the query rules it creates. You then need to split apart these filter rules to include  the variable data created from the first query An example of doing this with the office field would look like this.

GALQueryFilter = "(&(&(&(& (mailnickname=*) (| (&(objectCategory=person)(objectClass=user)(!(homeMDB=*)) _
(!(msExchHomeServerName=*)))(&(objectCategory=person)(objectClass=user)(|(homeMDB=*)(msExchHomeServerName=*)))_
 )))(objectCategory=user)(Department=" & rs.Fields("physicaldeliveryofficename").Value & ")))"