ADO Search Tips

ADO is an acronym for ActiveX Data Objects. ADO provides Active Directory query technology to VBScript (and VB) using the ADSI OLE-DB provider. Searches using ADO are only allowed in the LDAP namespace.

Active Directory searches using ADO are very efficient. The provider retrieves records matching your query criteria in one operation, without the need to bind to many objects. However, the resulting recordset is read-only, so ADO cannot be used to modify Active Directory objects directly. If you need to modify attribute values, you will have to bind to the object.

ADO returns a recordset. Each record in the recordset is a collection of the values of the attributes requested. The attribute values are from the objects that meet the conditions specified by an ADO query. The ADO query string can use either SQL or LDAP syntax. This page only covers the LDAP syntax. See the link at left for examples of SQL syntax queries. The LDAP query string includes up to 5 clauses, separated by semicolons. The clauses are:

The search base - The AdsPath to start the search, enclosed in angle brackets. For example, to start the search in the Sales OU of the MyDomain.com domain you might use a search base as follows: "<LDAP://ou=Sales,dc=MyDomain,dc=com>". The AdsPath can use either the LDAP or GC providers. You would use the GC provider to search for information in other trusted domains, but only attributes replicated to the Global Catalog are available.

The search filter - A clause that specifies the conditions that must be met for records to be included in the resulting recordset. The attribute values for all objects meeting the conditions are included in the recordset. The syntax of the search filter is explained below. An example to filter for all user objects would be "(&(objectCategory=person)(objectClass=user))".

The attributes to return - A list of Active Directory attributes separated by commas. Use the LDAP display names of the attributes. An example would be "sAMAccountName,displayName,description". Note that most property methods cannot be returned by ADO. For example, LastName is a property method whose value cannot be returned by ADO. The only property methods that can be returned by ADO are "Name" and "AdsPath".

The search scope - This can be one of three values. "Base" means that only the search base is included in the search. No child containers or OU's are included. "OneLevel" means the search base and any immediate children (sub containers or OU's) are included. "Subtree" (the default) means the search includes the entire Active Directory structure below the search base.

The Range Limits - Specifies which records in a multivalued attribute are to be returned. This clause is optional, but if it is used, it must be the fourth clause in the query string - between the attribute list and the search scope. As an example, to include records indexed by 0 through 999, you would use: "Range=0-999".

An example query string, with no Range Limits, would be:

"<LDAP://ou=Sales,dc=MyDomain,dc=com>;(objectCategory=computer)" _

& ";sAMAccountName;Subtree"

An example with Range Limits would be:

"<LDAP://cn=Users,dc=MyDomain,dc=com>;(objectCategory=group)" _

& ";member;Range=0-999;Base"

Only the Base and Attribute clauses are required. If there is no Filter clause, use two semicolons between the Base and Attribute clauses. The recordset will include all objects specified by the Base and Scope clauses. If there is no Scope clause, the search scope defaults to Subtree. A simple query string to return the sAMAccountName (NT name) of all objects in Active Directory would be:

"<LDAP://dc=MyDomain,dc=com;;sAMAccountName"

The only part of the query string that is case sensitive is the LDAP or GC provider name, which must be in all capitals, and any Boolean values, which must also be in all capitals. The query string is assigned to the "CommandText" property of the ADO Command object. An ADO Connection object specifies the provider used to connect to Active Directory. The Execute method of the Command object executes the query and returns a RecordSet object. See the link above for alternative methods to retrieve recordsets using ADO.

You enumerate the records in the RecordSet object in a loop. For example, a complete program to retrieve the sAMAccountName and cn attributes of all user objects in the domain is shown below. To make this example more generic, the RootDSE object is used to retrieve the default naming context, which is the DNS name of the domain the computer has authenticated to. You could hard code the Distinguished Name of the domain instead.

Option Explicit

Dim adoCommand, adoConnection, strBase, strFilter, strAttributes

Dim objRootDSE, strDNSDomain, strQuery, adoRecordset, strName, strCN

' Setup ADO objects.

Set adoCommand = CreateObject("ADODB.Command")
Set adoConnection = CreateObject("ADODB.Connection")
adoConnection.Provider = "ADsDSOObject"
adoConnection.Open "Active Directory Provider"
adoCommand.ActiveConnection = adoConnection

' Search entire Active Directory domain.

Set objRootDSE = GetObject("LDAP://RootDSE")

strDNSDomain = objRootDSE.Get("defaultNamingContext")
strBase = "<LDAP://" & strDNSDomain & ">"

' Filter on user objects.
strFilter = "(&(objectCategory=person)(objectClass=user))"

' Comma delimited list of attribute values to retrieve.
strAttributes = "sAMAccountName,cn"

' Construct the LDAP syntax query.
strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"
adoCommand.CommandText = strQuery
adoCommand.Properties("Page Size") = 100
adoCommand.Properties("Timeout") = 30
adoCommand.Properties("Cache Results") = False

' Run the query.
Set adoRecordset = adoCommand.Execute

' Enumerate the resulting recordset.
Do Until adoRecordset.EOF

' Retrieve values and display.
strName = adoRecordset.Fields("sAMAccountName").Value

strCN = adoRecordset.Fields("cn").value

Wscript.Echo "NT Name: " & strName & ", Common Name: " & strCN

' Move to the next record in the recordset.
adoRecordset.MoveNext
Loop

' Clean up.

adoRecordset.Close

adoConnection.Close

You step through the recordset in a loop, using the MoveNext method of the Recordset object to advance to the next record. If you forget to call the MoveNext method, the "Do Until" loop will never meet the EOF (End Of File) condition and the loop will never end. You retrieve values with the Fields collection of the Recordset object. You specify the name of the attribute you are retrieving with the Fields collection. The Value property of the Fields collection is the default property. In the example above I specified several properties for the Command object. These are not necessary, but can improve performance. It is good practice to close the Recordset and Connection objects when you are done.

The search filter specifies all conditions that must be met for a record to be included in the RecordSet. Each condition is in the form of a conditional statement, such as "(cn=TestUser)", which has a Boolean result. Each such condition is enclosed in parenthesis. The general form of a condition is an attribute and a value separated by an operator, which is usually the equals sign "=". Other operators that can separate attributes and values are ">=", and "<=" (the operators "<" and ">" are not supported). Conditions can be combined using the following operators.

& - The "And" operator (the ampersand). All conditions operated by "&" must be met in order for a record to be included.

| - The "Or" operator (the pipe symbol). Any condition operated by "|" must be met for the record to be included.

! - The "Not" operator (the exclamation point). The condition must return False to be included.

Conditions can be nested using parenthesis. In addition, you can use the "*" wildcard character in the search filter. However, the wildcard character cannot be used with Distinguished Name attributes (attributes of data type DN), such as the distinguishedName, memberOf, directReports, and managedBy attributes.

If the filter includes any attribute whose value has the "*" character, it must be replaced by the equivalent "\2A" to avoid having the "*" interpreted as the wildcard character. The backslash is an escape character and the ASCII hex equivalent for "*" is "2A".

Search filter examples:

To return all user objects with cn (Common Name) beginning with the string "Joe":

"(&(objectCategory=person)(objectClass=user)(cn=Joe*))"

To return all user objects. This filter is more efficient than the one using both objectCategory and objectClass, but is harder to remember:

"(sAMAccountType=805306368)"

To return all computer objects with no entry for description:

"(&(objectCategory=computer)(!description=*))"

To return all user and contact objects:

"(objectCategory=person)"

To return all group objects with any entry for description:

"(&(objectCategory=group)(description=*))"

To return all groups with cn starting with either "Test" or "Admin":

"(&(objectCategory=group)(|(cn=Test*)(cn=Admin*)))"

To return all objects with Common Name "Jim * Smith":

"(cn=Jim \2A Smith)"

To retrieve the object with GUID = "90395FB99AB51B4A9E9686C66CB18D99":

"(objectGUID=\90\39\5F\B9\9A\B5\1B\4A\9E\96\86\C6\6C\B1\8D\99)"

To return all users with "Password Never Expires" set: