Using Saved Queries for Active Directory Management

Executive Summary:

Saved queries are part of the Microsoft Management Console (MMC) Active Directory Users and Computers snap-in and can be used to quickly find and administer specific sets of Active Directory (AD) objects by constructing simple LDAP queries. With a query string such as

(objectCategory=computer)

you can get a complete listing of all the computers in your domain, then administer each one of those computer objects if you need to. Saved queries were introduced in Windows Server 2003 and can be deployed on Windows XP SP2 by installing the Windows Server 2003 Administration Tools Pack. However, saved queries still don't seem to be as widely used as they could be—possibly because administrators don't know how useful they are or how to create them. Let's see if we can change that.

Why Use Saved Queries?
Some of you might be thinking, “I’ve used the Find function within Active Directory Users and Computers as well as Dsquery for years now, and those work fine for performing AD queries and giving me the information I need. Why add something different to the mix?” If you start using saved queries, you'll see that the sheer ease of use is reason enough and well worth the effort of learning how to use them.

Saved queries are functionally equivalent to using the Find interface in Active Directory Users and Computers. The Find feature, as the name implies, offers a convenient way to quickly find specific users, groups, computers, and so forth. However, saved queries have the added benefit of being reusable, so you don't have to rekey Find queries time and time again.

Dsquery also provides results similar to saved queries; it searches AD and returns collections of objects. However, Dsquery is a command-line utility. I have nothing against command-line utilities and use them quite often, but saved queries have a slight advantage because they are easily accessible and can be organized in a centralized location. You can even organize saved queries so that they're in categorized folders under the main Saved Queries folder. Having a GUI with an organized list of my saved queries certainly makes at least some of my repetitious administrative duties simpler to perform than if I had to remember specific Dsquery syntax or hunt around for Dsquery batch files.

Saved queries can be particularly helpful to those administrators who aren't scripters because they offer an easy method for gathering, administering, and reporting on specific groups of AD objects without having to know how to write scripts. Also, saved queries are surprisingly uncomplicated to create, use, and share with other administrators. After constructing and testing your saved queries, you can export them to XML files, then pass them along to others. All recipients have to do is import the XML file into their Saved Queries folder to make it available for them to use.

When you want to produce down-and-dirty reports that don’t have a lot of fancy formatting, you can use the Export List feature to export AD collections returned by queries as comma-separated value (CSV) or tab-delimited text files.For instance, I use this feature for a weekly report that I send to my security department. The report simply shows accounts that are members of administrative groups (e.g., Enterprise Admins, Domain Admins) and service accounts that are in special administrative-level groups.

Setting Up for Saved Queries
The saved queries functionality of Active Directory Users and Computers is available only on Server 2003 and XP. You’ll probably want to read the “known issues” article for the Administration Tools Pack, particularly if you’ve done an in-place upgrade from Windows 2000. Please see “How to use the Administration Tools Pack to remotely administer computers that are running Windows Server 2003, Windows XP, or Windows 2000” at http://support.microsoft.com/default.aspx?scid=kb;[LN];304718. If you're using MMC with Server 2003, you can run dsa.msc from the Run utility or you can access Start, Programs, Computer Management, then select Active Directory Users and Computers. You’ll see saved queries up near the top of the Active Directory Users and Computers window.

If you're using XP to administer your domain, you first need to install the AD portion of the Windows Server 2003 Administration Tools Pack. The latest version of the tools pack is available on Microsoft's Web site; I recommend using the download version, which contains service pack fixes, rather than the software from the Server 2003 installation CD-ROM. You can download the tools pack at http://www.microsoft.com/downloads/details.aspx?familyid=E487F885-F0C7-436A-A392-25793A25BAD7. Before you install this package, however, note that you must uninstall any previous version of the Windows Server 2003 Administration Tools Packs that might be in place. After the tools are installed on your XP workstation, you’ll find the Active Directory Users and Computers snap-in under Computer Management, or you can launch it by typing dsa.msc in the Run utility just as you would on Server 2003.

You can install the Administration Tools Pack on Windows Vista, but the install is chaotic. More important, some key tools simply don’t work, and other tools display odd behavior. Needless to say, I’m not using Vista to administer my domain. But if you’re really interested in installing the tools pack on Vista, or just want to read about how to do it and what the limitations are, you can check out the Microsoft article "You experience installation errors and compatibility problems when you install Windows Server 2003 management tools on a Windows Vista-based computer" at http://support.microsoft.com/?scid=kb;en-us;930056&x=13&y=13. I suspect Microsoft will release a new version of the tools pack at some point, but you never know.

For the purposes of this article and to keep software and tool installation to a minimum, we’ll extract the installation package and install just the AD tools from the Administration Tools Pack. The downloaded installation file, WindowsServer2003-KB304718-AdministrationToolsPack.exe, isn't a typical executable but rather is a Win32 Cabinet Self-Extractor file. Basically it’s a Windows CAB file that can run the enclosed adminpak.msi installation package when it's executed. Because this is a CAB file, which is similar to a .zip or compressed file, you can opt to extract the contents of the file. To do so, change the directory from a command prompt to the directory with the downloaded executable, and run the command

WindowsServer2003-KB304718 -AdministrationToolsPack.exe /C

(Although this command wraps here, you would enter it all on one line. The same holds true for the other multiline commands and queries in this article.) You’ll then be presented with an input box so you can choose the location to extract the files to. Key in the path or use Browse to locate your intended folder, then click OK to extract adminpak.msi.

The adminpak.msi file contains the complete installation package of the Windows Server 2003 Administration Tools Pack. To install just the AD tools, change the directory from a command prompt to the folder that contains the adminpak.msi file, then run the command

msiexec /i adminpak.msi ADDLOCAL=FeADTools /qb

The abbreviated server administration tool name, FeADTools, is case sensitive and must be entered exactly as shown.

For more details about extracting specific components from the tools pack, visit http://support.microsoft.com/kb/314978. This Web page also gives you some information about the tools that are available in the Administration Tools Pack.

Creating Basic Queries
To create saved queries, launch Active Directory Users and Computers by clicking Start, Run, entering dsa.msc, and clicking OK. Figure 1 shows the Active Directory Users and Computers console; the name of your domain appears underneath the Saved Queries folder. The left pane displays container objects, and the right pane (or results pane) displays the container contents. Saved queries can be considered containers because they contain individual LDAP queries. However, you might see saved queries referred to as folders in technical and Help references. As you add new queries, they'll show up in the Saved Queries folder with an icon that looks like a magnifying glass over a book. Figure 2 shows what several saved queries might look like.

To create a new saved query, right-click the Saved Queries folder and select New, Query. You'll see the New Query dialog box, which Figure 3 shows. As you can see, there are basically five areas you can work with. Name and Description let you give your new query a name and describe its use. You can use the Query root Browse button to pick a specific AD container for your query. For the most part, I leave the query root at the default, which is my AD domain container. Selecting the Include subcontainers check box indicates that you want to query the root container, all of its objects, and all of the objects in all of the subcontainers underneath it. If it's not selected, you’ll get results only for the specified container. Define Query is used to create your LDAP query; most of the real work is done here. This option offers an extensive amount of built-in flexibility for creating your AD queries.

Let’s create a simple query to list all users. In the New Query dialog box, enter “All Users” for both Name and Description, then click the Browse button and select your domain container, which will be the topmost visible container within the Browse dialog box. Figure 4 shows the domain container represented by “YourDomain.” By default, the topmost domain container name should already appear in the Query root box next to the Browse button, so in most cases you won’t have to browse to establish a query root.

Make sure the Include subcontainers check box is selected because we want to query all the AD containers in the domain. Next, click Define Query to open the Find Common Queries dialog box, which Figure 5 shows. The types of queries you can create with this interface are nearly limitless. As you can see in Figure 5, we're creating a query based on a Common Queries theme. Take a second and click the Find drop-down button. As Figure 6 shows, there are quite a few other AD-related categories you can choose. Each of these selections has specific functionality and offers different options pertaining to that category. Click the Name drop-down button to see the various attributes you can use to further define your queries, such as Starts with, Ends with, Is (exactly), Is not, Has a value, and Has no value. For example, if you select Starts with and enter “admin” in the adjacent box, your query searches for objects that have a name starting with Admin. With these options, you can pick and chose various types of objects to query on and further define your queries to include specific object attributes.

For the query to list all users, select Has a value from the Name drop-down list and click OK. The interface constructs an LDAP query for you, which appears in the Query string box of the New Query dialog box. The LDAP query created for the All Users saved query is

(&(objectCategory=person) (objectClass=user)(name=*))

The query string is pretty straightforward. It's designed to return AD objects that are categorized as “person,” that are of the class “user,” and that have a name. The built-in interface takes your criteria and does the rest of the work for you—work that might have taken you 20 to 30 lines of VBScript or JScript code.

To complete the query, click OK. A new saved query called All Users will be in your Saved Queries folder. To perform the query, right-click it and select Refresh. You'll get a list of all the users in your domain in the results pane of the console. You can now double-click any of the returned user objects to administer that user account or right-click any object for a list of actions you can perform.

LDAP Matching Rules
I’ll show you one more example using the wizardlike options to create saved queries. Let’s use the Common Queries dialog box to create a query that finds all disabled accounts in your domain. First, enter “Disabled Accounts” in both the Name and Description boxes. Make sure the Include subcontainers check box is selected and the query root is your topmost domain container. Click Define Query. Now all you need to do is select the Disabled Accounts check box in the Find Common Queries dialog box, then click OK. To complete the saved query for finding disabled accounts, press OK on the New Queries dialog box.

Take a look at the LDAP query string that's generated:

(objectCategory=person) (objectClass=user) (useraccountcontrol: 1.2.840.113556.1.4.803:=2)

You might be wondering, “What in the world is useraccountcontrol:1.2.840.113556.1.4.803:=2?" It's an LDAP Matching Rule. Some AD object attributes, such as userAccountControl and groupType, are composed of bitwise flags. When certain bits are enabled, they represent attributes of an object, such as “account locked out” or “password does not expire.” When you want to query objects for certain conditions, you might need to use a bitwise operator to return only objects that match a particular bit or combination of bits being set.

In this example, the string of numbers between the colons is the userAccountControl's matching rule Object Identifier (OID) and the number at the end represents a userFlag value. UserAccountControl is an attribute of many AD objects. It's made up of assigned numerical values that represent certain account characteristics, such as a disabled account, a locked-out account, or an account that has a password that doesn't expire. The userFlag value indicates which characteristics are enabled. If the account is disabled, it has a userFlag value of 2. If the account is set with a nonexpiring password, it has a userFlag value of 65536. UserFlags are cumulative so if you want to check for accounts that have nonexpiring passwords or accounts that are disabled, you could query on a userFlag value of 65538 (65536 + 2)—but you’d need to use a different matching rule OID.

There are two possible matching rule values you can use for userAccountControl:

In the query just created, the LDAP_MATCHING_RULE_BIT_AND rule was used with a userAccountControl userFlag of 2, which translates to “return only disabled accounts.” If you want to find disabled accounts or accounts with nonexpiring passwords, you would use the LDAP_MATCHING_RULE_BIT_OR rule. The LDAP matching rule would be

useraccountcontrol:1.2.840 .113556.1.4.804:=65538

Creating Custom Queries
Now let’s create custom queries. You can use the Find Custom Search dialog box to define queries that gather whatever objects you like simply by entering your own LDAP queries—including queries you might not otherwise be able to create from the available prebuilt selections. You begin a Custom Search saved query the same way you began a basic saved query: Right-click Saved Queries; select New, Query; enter an appropriate Name and Description; make sure the query root is at the domain level or top of the tree; select the Include subcontainers check box; then click Define Query.

In the Find dialog box, click the Find drop-down arrow and select Custom Search. Figure 7 shows you the options available in the new dialog box. Select the Advanced tab and enter the following LDAP string in the Enter LDAD query box:

(groupType:1.2.840.113556.1.4.803:=1)

Click OK, enter “System Created Groups” in the Name and Description boxes, then click OK. As Figure 8 shows, you should now see all the groups that were created by the system in the results pane.

Next, right-click the System Created Groups saved query and select Copy. Right-click the Saved Queries folder and select Paste. With this copy, you can quickly create a similar saved query to find groups with global scope. Right-click the Copy of System Created Groups saved query and select Edit. Enter “Global Scope Groups” for both the Name and Description, click Define Query, then select the Advanced tab. Now, just change =1 in the LDAP query to =2, then click OK twice. Right-click the Global Scope Groups query and select Refresh. You should see all the global scope groups in your domain.

Now I’ll show you how to get both system and global scope groups. To devise a query that returns objects that have a groupType attribute of either 1 or 2, you simply add the two values together and use the LDAP_MATCHING_RULE_BIT_OR rule. So, copy and paste the System Created Groups query again, edit it so the Name and Description read “System Created OR Global Scope Groups,” then click Define Query. On the Advanced tab, change 803 in the query string to 804 and change =1 to =3. Click OK twice, right-click the new query and select Refresh, then check out your results. Right-click or double-click any of the result objects to see what you can do with them. Pretty cool stuff.

Easier Administration Through Saved Queries
After you’ve created a few saved queries, you can see how to export that list of objects to a text file. Simply right-click one of your saved queries and select Export List, enter the path and filename for your exported list, and choose the tab- or comma-delimited file type from the Save as type drop-down menu, then click Save. It’s that easy. You'll get a text file containing all the objects and columns that were returned from your saved query. If you’re not satisfied with the information provided in the default columns of the results pane, select View, Add/Remove Columns, then add available columns to the Displayed columns list. Whatever columns you select to appear will be exported when you use Export List.

If you want to give other administrators one or more of your saved queries, you could either send them the query string and let them enter it as a new query, or you could export the query and have them import it into their saved queries. To export one of your queries, right-click the specific query and select Export Query Definition, specify the location and name of the file, then click Save. Now, just send the exported file to the other administrators. To import a saved query, right-click the Saved Queries folder and select Import Query Definition, locate the XML file, then click Open. It’s that simple.

I keep all my saved queries directly under the Saved Queries folder, but if you prefer to organize them so that you don’t have 30 or 40 queries to peruse, you can create subfolders by right-clicking the Saved Queries folder and selecting New, Folder. Then, right-click the new folder and select New, Query if you want to create a query under your new folder, or you can drag and drop existing queries into other folders.

Well, I have to wrap this up now, but I’m going to leave you with nice long list of useful queries, which you can find in the sidebar, "Simple Saved Queries for Active Directory." All you have to do is create new custom queries and copy the query strings into the Enter LDAP query box on the Advanced tab of the Find Custom Search dialog box. I hope you’ve found this article helpful and informative and that what you gather from it helps make some of your administrative tasks a little bit easier.