ADO Search Tips

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". Also, the "tokenGroups", "tokenGroupsGlobalAndUniversal", and "tokenGroupsNoGCAcceptable" attributes cannot be retrieved by ADO. These are the only SID syntax operational attributes. If any of these attributes are listed in the attribute clause, the resulting recordset is empty. Other operational attributes, such as "canonicalName" and "modifyTimeStamp" can be retrieved using ADO. For the "tokenGroups" attributes, you must retrieve the "distinguishedName", bind to the corresponding object, then use the GetInfoEx method to load the attribute values into the local property cache.

The search scope - This can be one of three values. "Base" means that only the object represented by the search base is included in the search. No child containers, OU's, or objects (like users) are included. This is used to check for the existence of the base object. You might assign the ADsPath of a user object as the base of a search and use a scope of "base" to check for existence of the user. "OneLevel" means the search only includes immediate children of the base, like the users in an OU. If the base of the search is the ADsPath of an OU, and the filter is to return only organizational unit objects, then a scope of "OneLevel" will return all child OU's of the base, but not the base OU. "Subtree" (the default) means the search includes the base, all children of the base, and the entire Active Directory structure below the search base.

The Range Limits - Specifies which records in a multi-valued 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 distinguished names of all objects in Active Directory would be:

"<LDAP://dc=MyDomain,dc=com>;;distinguishedName"

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 (either TRUE or FALSE), 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.

Several properties of the ADO command object can be assigned values to make the query more efficient. In particular, you can assign a value to the "Page Size" property. This specifies the number of rows of the Recordset object that are retrieved at one time. If no value is assigned, a maximum of 1000 rows will be retrieved. You can assign any value to "Page Size" up to 1000. This turns on paging, which means that ADO retrieves the number of rows you specify repeatedly until all rows are retrieved, no matter how many there are. It has been found that it makes very little difference what value you assign, as long as you assign a value so that paging is enabled.

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"
Set 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 in parentheses, such as "(cn=TestUser)", which has a Boolean result. The general form of a condition is an attribute name and a value separated by an operator, which is usually the equals sign "=". The attribute cannot be operational (also known as constructed), since the values of these attributes are only calculated by the Domain Controller on demand and are not saved in Active Directory. Other operators that can separate attribute names 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 value in a filter includes any of the following characters, the character must be escaped, since it has special meaning in filters:

* ( ) \

These characters are escaped using the backslash escape character, "\", and the 2 digit ASCII hex equivalent of the character. Replace the "*" character with "\2A", the "(" character with "\28", the ")" with "\29", and the "\" character with "\5C". For example, to find all objects where cn is equal to "James (Jim)" you can use the filter:

(cn=James \28Jim\29)

To find all objects where description is equal to "5 * 3 \ 2" use the filter:

(description=5 \2A 3 \5C 2)

Actually, you can escape any character in this manner. Some search filter examples follow.

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:

"(&(objectCategory=person)(objectClass=user)" _
& "(userAccountControl:1.2.840.113556.1.4.803:=65536))"

To return all users with disabled accounts:

"(&(objectCategory=person)(objectClass=user)" _
& "(userAccountControl:1.2.840.113556.1.4.803:=2))"

To return all distribution groups:

"(&(objectCategory=group)" _
& "(!groupType:1.2.840.113556.1.4.803:=2147483648))"

To return all users with "Allow access" checked on the "Dial-in" tab of the user properties dialog of Active Directory Users & Computers. This is all users allowed to dial-in. Note that "TRUE" is case sensitive:

"(&(objectCategory=person)(objectClass=user)" _
& "(msNPAllowDialin=TRUE))"

To return all user objects created after a specified date (09/01/2007):

"(&(objectCategory=person)(objectClass=user)" _
& "(whenCreated>=20070901000000.0Z))"

To return all users that must change their password the next time they logon:

"(&(objectCategory=person)(objectClass=user)" _
& "(pwdLastSet=0))"

To return all users that changed their password since 2/5/2004. See the link below for a function to convert a date value to an Integer8 (64-bit) value. The date 2/5/2004 converts to the number 127,204,308,000,000,000:

"(&(objectCategory=person)(objectClass=user)" _
& "(pwdLastSet>=127204308000000000))"

To return all users with the group "Domain Users" designated as their "primary group":

"(&(objectCategory=person)(objectClass=user)" _
& "(primaryGroupID=513))"

The group "Domain Users" has the primaryGroupToken attribute equal to 513. To return all users with any group other than "Domain Users" designated as their "primary" group:

"(&(objectCategory=person)(objectClass=user)" _
& "(!primaryGroupID=513))"

To return all users not required to have a password:

"(&(objectCategory=person)(objectClass=user)" _
& "(userAccountControl:1.2.840.113556.1.4.803:=32))"

To return all users that are direct members of a specified group. You must specify the Distinguished Name of the group. Wildcards are not allowed:

"(&(objectCategory=person)(objectClass=user)" _
& "(memberOf=cn=TestGroup,ou=Sales,dc=MyDomain,dc=com))"

To return all computers that are not Domain Controllers:

"(&(objectCategory=Computer)" _
& "(!userAccountControl:1.2.840.113556.1.4.803:=8192))"

There is a new filter, called LDAP_MATCHING_RULE_IN_CHAIN, but it is only available if your Active Directory is installed on Windows 2003 SP2 or Windows 2008 (or above). This filter can only be used with DN attributes, like member or memberOf, but walks the hierarchical chain of objects to reveal nesting. For example, to find all groups that a specific user is a member of, even due to group nesting:

"(member:1.2.840.113556.1.4.1941:=cn=Jim Smith,ou=Sales,dc=MyDomain,dc=com)"

Or to find all members of a specified group, even due to group nesting:

"(memberOf:1.2.840.113556.1.4.1941:=cn=Test Group,ou=West,dc=MyDomain,dc=com)"

To return all user accounts that do not expire. The value of the accountExpires attribute can be either 0 or 2^63-1:

"(&(objectCategory=person)(objectClass=user)" _
& "(|(accountExpires=9223372036854775807)(accountExpires=0)))"

See the link below for a program that converts a date time value to the equivalent Integer8 (64-bit) value. This program converts the date 2/5/2004 to the equivalent Integer8 value of 127204308000000000 (depending on your time zone, and whether daylight savings time is in affect).

DateToInteger8.txt

When your filter clause includes objectCategory or objectClass, ADO does some magic to convert the values for your convenience. For example, the usual filter for all user objects is:

"(&(objectCategory=person)(objectClass=user))"

But of course, the objectCategory attribute never has the value "person". In reality, the filter should be:

"(&(objectCategory=cn=person,cn=Schema,cn=Configuration,dc=MyDomain,dc=com)" _
& "(objectClass=user))"

In fact, you can filter on objectCategory equal to "user", which is not really possible, but ADO will deal with it. The following table documents the result of ADO converting several filter combinations:

objectCategory	objectClass	Result
person	user	user objects
person		user and contact objects
person	contact	contact objects
	user	user and computer objects
computer		computer objects
user		user and contact objects
	contact	contact objects
	computer	computer objects
	person	user, computer, and contact objects
contact		user and contact objects
group		group objects
	group	group objects
person	organizationalPerson	user and contact objects
	organizationalPerson	user, computer, and contact objects
organizationalPerson		user and contact objects

I would recommend using the filter that makes your intent most clear. Also, if you have a choice between using objectCategory and objectClass, it is recommended that you use objectCategory. That is because objectCategory is both single valued and indexed, while objectClass is multi-valued and not indexed (except on Windows Server 2008 and above). A query using a filter with objectCategory will be more efficient than a similar filter with objectClass. Windows Server 2008 and later domain controllers have a special behavior that indexes the objectClass attribute. You can take advantage of this if all of your domain controllers are at least Windows Server 2008, or if you specify a Windows Server 2008 or above domain controller in your query.

You can use the program linked below to experiment with various filters in your domain. The program prompts for the base of the ADO query, the LDAP syntax filter, and the comma delimited list of attribute values to retrieve. The program displays the values of the specified attributes for all objects matching the specified filter in the specified base (and child containers).

GenericADO.htm

Most Active Directory attributes have string values, so you can echo the values directly, or assign the values to variables. Some Active Directory attributes are not single-valued strings. Multi-valued attributes are returned by ADO as arrays. Examples include the attributes memberOf, directReports, otherHomePhone, and objectClass. In these cases, the Value property of the Fields collection will be Null if there are no values in the multi-valued attribute, and will be an array if there is one or more values. For example, if the list of attributes includes the sAMAccountName and memberOf attributes, you could enumerate the Recordset object with a loop similar to:

Do Until adoRecordset.EOF
    strName = adoRecordset.Fields("sAMAccountName").Value
    Wscript.Echo "User: " & strName
    arrGroups = adoRecordset.Fields("memberOf").Value
    If IsNull(arrGroups) Then
        Wscript.Echo "-- No group memberships"
    Else
        For Each strGroup In arrGroups
            Wscript.Echo "-- Member of group: " & strGroup
        Next
    End If
    adoRecordset.MoveNext
Loop

It should be pointed out that the "description" attribute of user objects is actually multi-valued. However, it can only have one value. It is treated as a normal string by ADSI, but not by ADO. ADO returns either a Null (if the "description" attribute has no value) or an array of one string value. You must use code similar to above for this attribute.

If any attribute value can be missing, be sure to account for the possibility that a Null is retrieved from the recordset. An error will be raised if you attempt to echo a Null. For example, if you retrieve the value of the displayName attribute of all objects in Active Directory, many objects will not have this attribute. Other objects will have the attribute, but no value will be assigned. Neither of these situations will raise an error when the recordset is retrieved, but you may need to convert the Null value into a string to avoid a type mismatch error. For example:

Do Until adoRecordset.EOF
    ' The displayName attribute may not have a value assigned.
    ' Appending a blank string, "", converts a Null into a blank string.
    strName = adoRecordset.Fields("displayName").Value & ""
    Wscript.Echo strName
Loop

Other Active Directory attributes are Integer8. This means that they are 64-bit (8 byte) values, usually representing dates. These must be treated using the techniques at this link - Integer8 Attributes. For example, the pwdLastSet attribute is Integer8. If you use ADO to retrieve Integer8 attribute values, the following code will not invoke the IADsLargeInteger interface and will raise an error:

Do Until adoRecordset.EOF
    ' This does not invoke the IADsLargeInteger interface.
    Set objDate = adoRecordset.Fields("pwdLastSet")
    ' This statement raises an error.
    lngHigh = objDate.HighPart
    ' Likewise, the Intger8Date function, documented in the
    ' link above, raises an error.
    dtmDate = Integer8Date(objDate, lngTZBias)
    adoRecordset.MoveNext
Loop

You must either specify the Value property of the Field object and use the Set keyword:

Do Until adoRecordset.EOF
    ' Specify the Value property of the Field object.
    Set objDate = adoRecordset.Fields("pwdLastSet").Value
    ' Invoke methods of the IADsLargeInteger interface directly.
    lngHigh = objDate.HighPart
    ' Or use the Integer8Date function documented in the link above.
    dtmDate = Integer8Date(objDate, lngTZBias)
    adoRecordset.MoveNext
Loop

Or, you must assign the value to a variant, and then use the Set keyword to invoke the IADsLargeInteger interface:

Do Until adoRecordset.EOF
    ' Assign the value to a variant.
    lngDate = adoRecordset.Fields("pwdLastSet")
    ' Use the Set keyword to invoke the IADsLargeInteger interface.
    Set objDate = lngDate
    ' Invoke methods of the IADsLargeInteger interface directly.
    lngHigh = objDate.HighPart
    ' Or use the Integer8Date function documented in the link above.
    dtmDate = Integer8Date(objDate, lngTZBias)
    adoRecordset.MoveNext
Loop

Some attributes are Boolean, such as msNPAllowDialin and IsDeleted. If you retrieve the value of such an attribute, it will be either True or False. For example:

Do Until adoRecordset.EOF
    strName = adoRecordset.Fields("sAMAccountName").Value
    blnAllow = adoRecordset.Fields("msNPAllowDialin").Value
    If (blnAllow = True) Then
        Wscript.Echo "User " & strName & " is allowed to dial in"
    End If
    adoRecordset.MoveNext
Loop

If you use ADO to retrieve the Distinguished Names of objects, all characters that must be escaped will be properly escaped, with the exception of any forward slash "/" characters. This should be rare, but if you attempt to bind to the corresponding object, an error will be raised if the forward slash is not escaped with the backslash escape character "\". For more on characters that need to be escaped, see this link:

CharactersEscaped.htm

However, the forward slash is the only character that is not properly escaped when retrieved by ADO. If it is possible for the forward slash character to be found in a Distinguished Name, use code similar to this example:

Do Until adoRecordset.EOF
    ' Retrieve user Distinguished Name from recordset.
    strUserDN = adoRecordset.Fields("distinguishedName").Value

    ' Escape any "/" characters with backslash escape character.
    ' All other characters that need to be escaped will be escaped.
    strUserDN = Replace(strUserDN, "/", "\/")

    ' Bind to the user object in Active Directory with the LDAP provider.
    Set objUser = GetObject("LDAP://" & strUserDN)

    adoRecordset.MoveNext
Loop

Finally, some attributes are OctetString, which is a byte array. The array must be converted to a hexadecimal string before it can be displayed. Examples include logonHours, and objectGUID. For an example of a function to convert OctetString values to a hexadecimal string, see this program - IsMember Function 8.

For a complete discussion of ADO and searching Active Directory see the following links:

http://www.microsoft.com/resources/documentation/windows/2000/server/scriptguide/en-us/sas_ads_emwf.mspx

http://www.microsoft.com/resources/documentation/windows/2000/server/scriptguide/en-us/sas_ads_jgtf.mspx

http://www.microsoft.com/resources/documentation/windows/2000/server/scriptguide/en-us/sas_ads_shpc.mspx

http://support.microsoft.com/kb/187529/en-us

http://msdn2.microsoft.com/en-us/library/Aa746471.aspx

http://msdn2.microsoft.com/en-us/library/Aa746475.aspx