ADO Search Tips

Windowsmanager

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. Instead, you will have to bind to each object to be modified.

ADO returns a recordset. Each record in the recordset is a list 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. The LDAP query string includes up to 5 clauses, separated by semicolons. These 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.

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. You enumerate the records in the RecordSet object. For example, a complete program to retrieve the sAMAccountName and cn attributes of all user objects in the MyDomain.com domain could be:

Option Explicit

Dim objCommand, objConnection, strBase, strFilter, strAttributes

Dim strQuery, objRecordset, strName, strCN

Set objCommand = CreateObject("ADODB.Command")
Set objConnection = CreateObject("ADODB.Connection")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
objCommand.ActiveConnection = objConnection
strBase = "<LDAP://dc=MyDomain,dc=com>"

strFilter = "(&(objectCategory=person)(objectClass=user))"
strAttributes = "sAMAccountName,cn"
strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"
objCommand.CommandText = strQuery
objCommand.Properties("Page Size") = 100
objCommand.Properties("Timeout") = 30
objCommand.Properties("Cache Results") = False
Set objRecordSet = objCommand.Execute

Do Until objRecordSet.EOF
strName = objRecordSet.Fields("sAMAccountName").Value

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

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

objConnection.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 met 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, so you don't need to specify it. 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 Connection object 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.

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 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:

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

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

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

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: