|Become a Columnist Microsoft Exchange Site Microsoft Support SiteMSDN Exchange Site|
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
To get the GALQueryFilter string for your Global address list in Exchange System Manager look at the properties of your Global Address list and then cut and past the filter rules. If you want to create a custom filter for say to create a recordset for just users from one particular office using Exchange System manager you can create a temporary address list with the parameters you want to define and then just cut and paste the filter rules it creates for that address list into your scripts.
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
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 & ")))"
Disclaimer: Your use of the information contained in these pages is at your sole risk. All information on these pages is provided "as is", without any warranty, whether express or implied, of its accuracy, completeness, fitness for a particular purpose, title or non-infringement, and none of the third-party products or information mentioned in the work are authored, recommended, supported or guaranteed by Stephen Bryant or Pro Exchange. OutlookExchange.Com, Stephen Bryant and Pro Exchange shall not be liable for any damages you may sustain by using this information, whether direct, indirect, special, incidental or consequential, even if it has been advised of the possibility of such damages.
Copyright Stephen Bryant 2008