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.
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.
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.
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.
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.
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
If you are not familiar with the meanings of the terms "object type," "attribute class," "attribute," "relational operator," and "comparison value," review Lesson 1 of this chapter.
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.
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:
The attribute is compared to null or not null.
The attribute is compared to a simple value (numeric, string, or date/time).
When the query is run, you are prompted to enter an actual value to which the attribute is compared.
The attribute is compared to another attribute.
The attribute is compared to results returned by another query. This comparison value allows for nested queries.
The attribute is compared to a list of values. If the attribute matches one of the values, then it is a match.
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.
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
To learn about joins and creating database queries using joins, read the following titles from Microsoft Press: Microsoft BackOffice Resource Kit Part 2 and Microsoft SQL Server Training, a self-paced training guide.
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.
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.
Clicking Show Query Language displays the WBEM Query Language (WQL). A complete description of WQL is found in the Microsoft SMS 2.0 Toolkit.
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 you specify a sort order, the results cannot be sorted differently by clicking a column head in the details pane.
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.
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
The results of a query are based on the latest data (since the collection was last evaluated) stored in the site database. To produce up-to-date results, make sure that inventory has been collected recently. If the query is run against a collection, update the collection first.
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.
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
Complete this exercise from the SMS Administrator console.
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.
The list of queries appears in the details pane.
The Query Properties dialog box displays General settings for the new query.
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.
The Result Properties dialog box appears. You will select attributes from here that will be displayed when the query runs.
The Select Attribute dialog box appears.
System Resource is both an object type and an attribute class. In this case you selected the System Resource attribute class.
The Result Properties dialog box appears. Notice the attribute System Resource - Name is listed.
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.
| 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.
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.
The Criterion Properties dialog box appears. From this dialog box, you will assign the criterion of the query.
The Select Attribute dialog box appears.
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."
The Values dialog box appears displaying all subnets assigned to the local site.
The Criterion Properties dialog box appears. Notice the 'Value' box shows 128.1.0.0.
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.
The Criterion Properties dialog box appears.
The Select Attribute dialog box appears.
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."
The Values dialog box appears displaying all operating systems that have been discovered in the local site.
The Criterion Properties dialog box appears. Notice the 'Value' box now displays Microsoft Windows NT Workstation 4.0.
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.
The Query Properties dialog box displays General settings for the new query.
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.
The list of queries appears in the SMS console tree.
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.