Use ADO for Easy AD Searches

Active Directory (AD) and organizational units (OUs) let administrators who manage large numbers of accounts create object hierarchies to organize users, groups, and computers. Although these Windows features make account management more flexible, they also make finding account information more challenging. I’ve written some Windows Script Host (WSH) scripts that use ActiveX Data Objects (ADO) to make it easier to search AD to find the account information you need. Here, I’ll first introduce the three objects used when searching with ADO. Then I’ll walk you through the three steps to writing ADO search scripts. Finally, I’ll explain the differences between LDAP query searches and SQL query searches and how to use both query options.

ADO’s Three Objects
ADO is a common object model that allows ActiveX–compatible languages, such as WSH, scripts to retrieve provider data using Active Directory Service Interfaces (ADSI). Table 1 shows the three objects typically used in an ADO search—Connection, Command, and Recordset—including their program IDs (ProgID) and descriptions. The following steps are a general outline of the tasks your script must perform to search AD using ADO:

The script in Listing 1, ListAccts.vbs, shows an example of using ADO to perform an AD search. This short, practical script, which I wrote using WSH and VBScript, uses ADO to list the usernames and email addresses for domain users. You can download the script file by clicking the Download the Code Here button at the top of the article.

Steps for Writing ADO Scripts
Now that you have a general idea of what objects you use to search AD with ADO, let's take a more detailed look at the steps and scripts necessary to accomplish the search.

      Step 1: Configure the Connection object. Callout A in Listing 1 shows how ListAccts.vbs creates the Connection object, sets its provider property, and calls its Open method. (For information about specifying alternate credentials in the Connection object, see the sidebar “Specifying Alternate Credentials in ADO,” InstantDoc ID 98915.)

      Step 2: Configure the Command object. Callout B shows how the ListAccts.vbs script configures the Command object. First the script creates the Command object, then it sets the Command object's ActiveConnection property to the Connection object. Next the script configures two properties for the Command object. Table 2 lists some of the possible properties.

      The syntax for setting a Command-object property is

command.Properties.Item("name") = value

Command is the Command object, name is the property name from Table 2, and value is the property’s new value. ListAccts.vbs disables result-caching and enables paging because the number of results might be large. Then the script sets the Command object's CommandText property to a string containing an LDAP query. (I'll discuss how to write an LDAP query later.)

      Step 3: Return the Recordset object. Callout C shows how ListAccts.vbs calls the Command object's Execute method to return a Recordset object. As noted in Table 1, a Recordset object is a set of records representing the query’s results. Think of a Recordset as a dynamically created database containing only the fields you need. In this case, I defined only two of the LDAP-query fields: sAMAccountName and mail. As a result, the Recordset generated by the Command object's Execute method will look like the table in Table 3 (the numbers and field names aren't part of the data). The Recordset has two fields (columns) and multiple records (rows).

      The Recordset object’s Fields property contains a collection of the field names. Use the Item property in the Fields collection to specify the field name and read the field's Value property to get the field data. The command

recordset.Fields.Item("fieldname").Value 

reads the data for fieldname from the current record in recordset.

      As callout C shows, the Recordset object's End of File (EOF) property will be true if there are no more records. (This is also true if the query didn't return any results.) The MoveNext method is also pretty self-explanatory: It moves to the next record in the Recordset.

      Note that you’ll need to use CScript to run ListAccts.vbs at a command prompt because the script uses the WScript object Echo method to return its output. Also, make sure that you change the base distinguished name (DN) variable at the top of the script to reflect your AD domain. When you run the script, it generates a tab-delimited list of usernames and their mail addresses.

LDAP and SQL-Style Queries
You have two query options when using ADO: LDAP-style and SQL-style queries. An LDAP query string contains four parts, separated by semicolons:

      The base DN. The base DN sets the search starting point. For example, to specify the wascorp.net domain, you’d use the following base DN:

DC=wascorp,DC=net

To specify just the sales OU in the wascorp.net domain, you’d use this base DN:

OU=Sales,DC=wascorp,DC=net

You must specify LDAP:// (in uppercase) before the DN, as show in the following example:

LDAP://DC=wascorp,DC=net

      The LDAP search filter. An LDAP search filter defines the search criteria. Table 4 breaks down and describes the components in an LDAP search filter. (For more details about search filter syntax, see http://msdn2.microsoft.com/en-us/library/aa746475.aspx.)

      The attribute names. These are the attribute names you want to retrieve, separated by commas. For example, the ListAccts.vbs script retrieves the sAMAccountName and mail attributes.

      The search scope. Search scope options are Base, Onelevel, or Subtree. Base searches only the Base DN, returns at most one result, and is less frequently used than Onelevel and Subtree. Onelevel searches only one level under the Base DN. Subtree searches the Base DN and all levels under it. The default search is Subtree and is the most frequently used search scope.

      For example, to extend the ListAccts.vbs script so that it searches only for user accounts with mail addresses, change the LDAP query string search filter section to the code in Listing 2.

      For your convenience, ListAccts-2.vbs, which you can also download using the Download the Code Here button, contains the preceding changes. When you run this script—again, using the CScript host from the command prompt—it will output only accounts that have mail attributes. If the mail attributes are not populated for any AD accounts, the query won't return any results.

      In addition to supporting LDAP querying, ADO supports using an SQL-like query to search AD. The SQL query style might be more familiar to many script authors. To use an SQL query instead of an LDAP query, simply set the CommandText property of the Command object to your SQL query. The SQL query general form is

SELECT attributes FROM 'LDAP://base DN' WHERE filter

in which attributes is a comma-separated list of attributes, base DN is the search starting point, and filter specifies the search filter. To modify the ListAccts.vbs script to use an SQL query instead of an LDAP query, change the Command object's Command property to the example code in Listing 3.

You’ll notice that the single quotes are required. Also, note that, unlike an LDAP query, an SQL-style query doesn't let you specify the search scope as part of the query, so you’ll need to modify the Command object’s SearchScope property (see Table 2) if you want a search scope other than Subtree. The ListAccts-SQL.vbs script, also available in the code-download file for this article, contains these changes. For more information about SQL-style queries, see Microsoft’s SQL Dialect Web page at http://msdn2.microsoft.com/en-us/library/aa746494.aspx.

ADO Can Be Imposing
ADO is a fairly complex general-purpose technology that has many uses besides searching AD. This complexity makes it initially difficult to find the minimum steps you need for searching AD. Based on the information in this article, you can write your own scripts to easily search AD for the information you need.