20.3 Understanding Search Filters

When you use the LDAP dialect with the ADSI OLE DB provider to conduct a search, you must use an LDAP search filter to specify your search criteria. In a simple case, (objectclass=user) would be used to select every object with the user objectclass under the search base. You can in fact use a filter to match the presence of a value (or not) for any attribute of an object. This enables you to create powerful searches with complex criteria. For example, you can search for any group object that has a certain user as a member and that has a description matching a certain substring.

Filters must follow the format specified in RFC 2254. You can download RFC 2254 from http://www.ietf.org/rfc/rfc2254.txt.

Although filters let you conduct powerful searches, working with them can seem complex because of the format used, known as prefix notation. To make it easier to understand, we have divided the discussion of filters into two parts: items within a filter and items connecting filters.

20.3.1 Items Within a Filter

Within a filter, you can have three types of items:

Operators

A filter can include one of three operators. The equal-to (=) operator checks for exact equivalence. An example is (name=janet). The greater-than-or-equal-to (>=) and less-than-or-equal-to (<=) operators check for compliance with a range. Examples are (size>=5) and (size<=20).

Attributes

You can include attributes in filters when you want to determine whether an attribute exists. You simply specify the attribute, followed by the = operator and an asterisk (*). For example, the (mooseHerderProperty=*) filter searches for objects that have the mooseHerderProperty attribute populated.

Substrings

You can include substrings in filters when you want to search for objects with specific strings. Test for substrings by placing the attribute type (e.g., cn for common name, sn for surname) to the left of the = operator and the substring you're searching for to the right. Use the * character to specify where that substring occurs in the string. The (cn=Keith*) filter searches for common name (CN) attributes that begin with the substring "Keith"; the (cn=*Cooper) filter searches for CN strings that end with the substring "Cooper". Depending on the search, the latter form of substring searches can take a long time to return. Under Windows Server 2003, the substring searches perform much better than previously.

You can place several substrings together by using an asterisk character several times. For example, the (cn=Kei*Coo*) filter searches for two substrings in the string: the first substring begins with "Kei", followed by the second substring that begins with "Coo". Similarly, the (cn=*ith*per) filter searches for strings that have two substrings: the first substring ends in "ith" followed by the second substring that ends in "per".

The resultset of a substring search might contain objects that you don't want. For example, if you use the filter (cn=Kei*Coo*) to search for the object representing "Keith Cooper", your resultset might contain two objects: one representing "Keith Cooper" and another representing "Keith Coolidge". To address that issue, you can connect multiple filter strings together to refine your search even more.

20.3.2 Connecting Filters

Compound filters can be created by using the ampersand (&), the vertical bar (|), and the exclamation mark (!). Let's start by creating a filter to find all groups whose common name begins with the letter a. The following is the filter for this search:

(&(objectclass=group)(cn=a*))

This filter actually consists of two filters: (objectclass=group) and (cn=a*), but because you're enclosing the filters in parentheses, you're treating them as one filter. The & prefix specifies the use of the logical AND operator. In other words, you're searching for objects that are in the group objectclass and have a CN that begins with the letter a.

You can continue to add additional filters to narrow the search even more. Suppose that in groups whose CN begins with the letter a, you want to find only those users whose surnames begins with the letter d. To perform this search, you use the following filter:

(&(objectclass=group)(cn=a*)(sn=d*))

You also can widen a search. Instead of using the & operator, you use the | prefix, which specifies the logical OR operator. For example, if you want to find all group or user objects, you use the following filter:

(|(objectclass=group)(objectclass=user))

You can nest sets of filters, as long as each filter conforms to the correct notation. For example, if you want to find all groups whose CN begins with the letter a or whose description begins with the substring "Special groups", you use the following filter:

(&(objectclass=group)(|(cn=a*)(description=Special groups*)))

So far, we've been searching for objects that have a certain characteristic. You can also search for objects that don't have a certain characteristic. Use the ! prefix, which specifies the NOT, or negation, operator. For example, you can search for all objects that do not have an objectclass equal to User with the following filter:

(!(objectclass=User))

By combining the &, |, and ! operators, you can perform powerful searches. For example, consider the following query:

(&
  (|(objectclass=container)(objectclass=organizationalUnit))
  (!(MyCorpSpecial=*))
  (|(cn=*cor*)(cn=J*))
)

This query is searching for any container or organizational unit (OU) that doesn't contain the MyCorpSpecial property and whose CN contains the letters "cor" or starts with the letter J. Here's how to include this filter in a script:

filterStr = _
  "(&(|(objectclass=container)(objectclass=organizationalUnit))" & _
    "(!(MyCorpSpecial=*))" & _
    "(|(cn=*cor*)(cn=J*))" & _
  ")"

There are no spaces in the string, yet the quotation marks do not overly detract from the formatting.

As you can see, this is a very powerful specification.

If a value you are trying to match contains an asterisk or parenthesis, which are special characters used in filters, those characters must be preceded by a backslash (\)