Microsoft Systems Management Server 2.0 Training Kit

An administrator creates ("builds") queries or edits existing queries using Query Builder. Once created, a query runs from the Queries node and acts as the foundation for membership rules in dynamic collections. SMS 2.0 is shipped with pre-built queries.

• Create queries using Query Builder.
• Query the site database to locate specific information.

Creating a Query

Queries are created in Query Builder, which is accessed from the Queries node in the SMS Administrator console. Figure 8-2 shows how to navigate to Query Builder.

Figure 8-2. Navigating to the Query Builder's Query Properties dialog box.

To define a query, you must name it in the 'Name' field, select an object type, and then click the Edit Query Statement to define the query. The 'Comment' box and 'Collection Limiting' options shown in Figure 8-2 are optional. The options under the General tab are described in the following paragraphs.

Name

Use a descriptive name to identify this query. You can also use an existing query as a starting point by choosing the Browse button and selecting an existing query. If you do this, create a new name for the query to distinguish it from the existing query. If you edit an existing query directly, rather than create a new query, you lose the original query.

Comment

Although this field is optional, it is helpful to include a list of the criteria used to define the query expression. These comments can be easier to read than the query criteria. Query criteria are defined from the dialog box that appears when you click Edit Query Statement.

Object Type

Before you begin defining a query, you must choose which object type you are looking for. As discussed earlier, seven predefined object types exist in the SMS database. In most cases, you will query the System Resource object type, which defines computers, but it is possible to run queries against the other object types as well.

Collection Limiting

This option allows you to limit a query to a collection, rather than running the query against the entire site database. If you want to limit the query to more than one collection, create a new collection that contains all collections of interest. You can also have the query prompt for a collection each time it is run.

Like all object classes and instances appearing in the SMS Administrator console, the query facility and queries can be secured so that only certain users or groups are able to create queries or run existing queries. Individual queries are secured from the Security tab in Query Builder (see Figure 8-2). Class and instance security are discussed in Chapter 12, "Configuring SMS Security."

Clicking Edit Query Statement displays the Query Statement Properties dialog box, which is illustrated in Figure 8-3. Three tabs appear in the Query Statement Properties dialog box: General, Criteria, and Joins.

Figure 8-3. Defining the Query Statement.

The simplest query consists of at least one attribute from an attribute class, which is defined from the General tab. From this tab, an administrator defines which attributes of the query (the Results Format) should appear in the details pane when the query is run.

To create anything but the simplest query, you must define query criteria from the Criteria tab.

NOTE

Figure 8-4 shows how to navigate to the Criterion Properties dialog box to select a type of criterion, an attribute, relational operator, and a comparison value.

Figure 8-4. Defining a query criterion.

Criterion Type and Value

The criterion type indicates what you will compare the attribute to. It may be, for example, a simple value or a null value. Your choice of criterion type determines what types of comparison values can be part of the expression. For example, the Subselected values criterion type allows you to define a query based on the results of another query. The comparison values in this case are the other query. The criterion types in Query Builder are:

• Null value

The attribute is compared to null or not null.

• Simple value (shown in Figure 8-4)

The attribute is compared to a simple value (numeric, string, or date/time).

• Prompted value

When the query is run, you are prompted to enter an actual value to which the attribute is compared.

• Attribute reference

The attribute is compared to another attribute.

• Subselected values

The attribute is compared to results returned by another query. This comparison value allows for nested queries.

• List of values

The attribute is compared to a list of values. If the attribute matches one of the values, then it is a match.

Where

The attribute class and attribute for the expression appears in the 'Where' field. Clicking Select displays the Select Attribute dialog box. From this dialog box, you select the attribute class and attribute. You may also choose to create an alias for the attribute class from the Select Attribute dialog box.

Operator

When you choose an attribute, the possible relational operators are displayed. The type of relation depends on the type of value to which the attribute is being compared. Simple values are numeric, string, or date/time.

The Joins tab displays database joins that are required if the query contains attributes from different attributes classes. A join is a query constructed to retrieve data from more than one database table. Each attribute class is contained within its own table in the site database. Most queries are defined using the General and Criteria tabs. However, advanced SQL Server users can create more sophisticated queries from the Joins tab.

NOTE

Editing a Query

Building complex queries requires an understanding of query editing. Adjustments to a query expression's structure is done from the 'Criteria' toolbar below the Criteria tab, as shown in Figure 8-5.

Figure 8-5. Editing a query statement from the Criteria tab.

The toolbar below the Criteria tab allows you to perform the functions listed next. Note that the numbers following the descriptions correspond to the numeric labels in figure 8-5.

• Add a new criterion (1).
• Edit the properties of a selected criterion (2).
• Delete a selected criterion (3).
• Place parentheses around a selected set of criteria (4).
• Remove parentheses from a selected set of criteria (5).
• Toggle between AND and OR to connect criteria (6).
• Toggle the NOT operator on or off (7).

Depending on what is highlighted in the 'Criteria' box, different criteria toolbar options are available. For example, as shown in Figure 8-5, if the entire expression is highlighted, buttons 1, 3, 4, 6, and 7 are valid selections. Button 2 is not available because more than one criterion is selected, and button 5 is not available because more than the parentheses are selected.

Criteria within parentheses are evaluated first, and the rest of the criteria in the Query Statement are then evaluated from the top of the expression to the bottom of the expression.

Show Query Language

Clicking Show Query Language displays the WBEM Query Language (WQL). A complete description of WQL is found in the Microsoft SMS 2.0 Toolkit.

The Query Results Format

Before a query is run, you must decide what attributes with their associated values should appear in the details pane of the SMS Administrator console. Choose which attributes to display and in what order they should appear in the Results box under the General tab (Figure 8-3). All attributes of the object type being queried will be made available in query result. For example, if your query expression is set up to determine how many disk drives contain less than 300 MB of free space (Logical Disk - Free Space (MBytes), you can include the computer name in the results format. That allows you to determine the location of the disk drives with less than 300 MB of free space.

You can also specify a sort order for the results returned from the query. The sort order is based on certain attributes so that, for example, the results display the client computers with the least amount of disk space first. To sort client computers by least to most disk space available, sort Logical Disk - Free Space (MBytes) in ascending order.

NOTE

If no attributes are displayed on the General tab, which is the default for a new query, then all attributes of the object type will be displayed. If you do not want all attributes displayed, then add at least one attribute to the 'Results' box.

Running a Query

After a query is defined, it is run from the Queries node in the SMS Administrator console. The results of a query display in the details pane are shown in Figure 8-6.

Figure 8-6. Running a query from the SMS Administrator console.

IMPORTANT

Run Query Special

The Run Query Special task allows you to specify the maximum number of records to be displayed. This is useful when the site database contains a large number of attribute records that are being queried. If you create a query using client computers, for example, you may want to limit the query to only a portion of all client computer records in the site database. After selecting a query from the Queries node, the Run Query Special command can be run from the Action menu, as shown in Figure 8-7.

Figure 8-7. Navigating to the Run Query Special command.

Query limits are not saved. So every time you run the Run Query Special command, you must configure the limits of the query.

Exercise 44: Creating a Query

In this exercise, you will create a query to find all Microsoft Windows NT Workstation version 4.0 computers on your local subnet. This query can be used as the membership rule for a collection.

NOTE

In the following steps, you will configure the query results in the format in which they will be displayed in the details pane when the query runs.

1. In the SMS console tree, select Queries.

The list of queries appears in the details pane.

2. On the Action menu, select New, and then choose Query.

The Query Properties dialog box displays General settings for the new query.

3. In the 'Name' field, type Local Windows NT 4.0 Client Computers.
4. In the 'Comment' box, type All Windows NT 4.0 Workstation client computers on the local subnet, and then click Edit Query Statement.

The Local Windows NT 4.0 Client Computers Query Statement Properties dialog box appears. Notice that in the 'Results' box, no attributes are listed. Since no attributes are listed, the default query results will include all attributes of the System Resource object type.

5. Click the yellow star button on the toolbar.

The Result Properties dialog box appears. You will select attributes from here that will be displayed when the query runs.

6. Click Select.

The Select Attribute dialog box appears.

7. From the 'Attribute class' drop-down list box, select System Resource.

System Resource is both an object type and an attribute class. In this case you selected the System Resource attribute class.

8. From the 'Attribute' drop-down list box, select Name, and then click OK.

The Result Properties dialog box appears. Notice the attribute System Resource - Name is listed.

9. From the 'Sort' drop-down list box, select Ascending, and then click OK.

The Local Windows NT 4.0 Client Computers Query Statement Properties dialog box appears. Notice the Name attribute in the System Resource class is displayed.

10. Using steps 5 to 9 as a guide, add the following two attributes to the 'Results' box:

Attribute class	Attribute	Sort
System Resource	IP Subnets	<Unsorted>
System Resource	Operating System Name and Version	<Unsorted>

The Local Windows NT 4.0 Client Computers Query Statement Properties dialog box appears.

You have designated the computer name, IP subnet, and operating system name to be displayed in the results list when the query runs. The query results will be sorted by computer name.

In the following steps, you will create the criteria used to retrieve data about all Windows NT Workstation version 4.0 computers on the local subnet.

1. Select the Criteria tab.

The Local Windows NT 4.0 Client Computers Query Statement Properties dialog box appears displaying the criteria used to determine which resources to display when the query runs. By default, there is no criteria, which means all resources will be displayed.

2. Click the yellow star button on the toolbar.

The Criterion Properties dialog box appears. From this dialog box, you will assign the criterion of the query.

3. Click Select.

The Select Attribute dialog box appears.

4. From the 'Attribute class' drop-down list box, select System Resource.
5. From the 'Attribute' box, select IP Subnets, and then click OK.

The Criterion Properties dialog box appears. Notice that the criterion type, attribute, and relational operator are selected. The first part of the expression is " System Resource - IP Subnets is equal to."

6. Click Values.

The Values dialog box appears displaying all subnets assigned to the local site.

7. Click 128.1.0.0 and then click OK.

The Criterion Properties dialog box appears. Notice the 'Value' box shows 128.1.0.0.

8. Click OK.

The Local Windows NT 4.0 Client Computers Query Statement Properties dialog box appears displaying the criterion or criteria used to build the query. Notice the query expression appearing in the 'Criteria' box.

9. Click the yellow star button on the toolbar.

The Criterion Properties dialog box appears.

10. Click Select.

The Select Attribute dialog box appears.

11. From the 'Attribute class' drop-down list box, select System Resource.
12. From the 'Attribute' drop-down list box, click Operating System Name and Version, and then click OK.

The Criterion Properties dialog box appears. Notice that the criterion type, attribute, and relational operator are selected. The first part of the expression is "System Resource - Operating System Name and Version is equal to."

13. Click Values.

The Values dialog box appears displaying all operating systems that have been discovered in the local site.

14. Select Microsoft Windows NT Workstation 4.0, and then click OK.

The Criterion Properties dialog box appears. Notice the 'Value' box now displays Microsoft Windows NT Workstation 4.0.

15. Click OK.

The Local Windows NT 4.0 Client Computers Query Statement Properties dialog box appears displaying the criteria used to determine which resources to query. Notice the last expression was added to the criteria, with an "and" to join the two expressions.

16. Click OK.

The Query Properties dialog box displays General settings for the new query.

17. Click OK.

The Local Windows NT 4.0 Client Computers query appears in the list of queries below the Queries node. Keep the Queries node expanded, as the query you just created will be run next.

In the following steps, you will run the query in the SMS Administrator console.

1. On the Action menu, click Refresh.

The list of queries appears in the SMS console tree.

2. From the SMS console tree, select Local Windows NT 4.0 Client Computers. Then from the Action menu, choose Run Query.

The Windows NT Workstation 4.0 computer (COMPUTER2) on the local subnet appears in the details pane. Verify that your query includes only the Windows NT Workstation client computer. Notice the columns displayed in the details pane are the three attributes (name, subnet ID, and operating system) that you selected for the query results format.