[Previous] [Next]

Lesson 1: Overview of Queries

The SMS Administrator console includes a powerful, user-friendly query tool called Query Builder to extract virtually any systems management data stored in the site database. In its simplest form, a query provides a way to peer into a site database in order to extract or display specific data. Queries in SMS, however, go beyond merely displaying data. For example, a query can be built in SMS in the form of a membership rule in a dynamic collection. In this case, the query is able to determine which client computers in the database contain more than 32 MB of RAM and 300 MB or more of free disk space. SMS can then be instructed to distribute software on client computers that meet these criteria.

After this lesson, you will be able to Estimated Completion Time: 20 minutes

What Is an SMS 2.0 Query?

A query allows an administrator, using SMS functions such as collections, to search for information in the site database. All information stored in the site database can be queried, including specific data pertaining to computer resources, software distribution, sites, users, and groups. A query is made up of an understandable expression or multiple expressions conveyed using the WBEM Query Language (WQL). Queries are executed against a site database in order to retrieve data. Using the WBEM schema, the SMS Provider retrieves all data in the site database tables that meet the criteria defined in the expression. The result of a query run from Query Builder is reported in the SMS Administrator console.

SMS uses the results of a query as an extension to its software distribution and reporting functions. A query may be used to determine which client computers in the enterprise should receive a particular software package. For example, a query can identify which client computers on the network are running Windows 2000. This query is then used in the membership rules of a collection. A membership rule determines which resources are included in the collection when it is updated. The rule can be based on a static assignment (added manually), or a dynamic assignment (added as the result of a query). A collection using a dynamically assigned membership rule is specified when an advertisement is created. Thereafter, whenever a client computer in the site is updated to Windows 2000, the advertisement automatically appears in the APM (Advertised Programs Manager).

The query facility in SMS is extensible. Any new object type, attribute class, or attribute defined using custom MIFs is stored in the site database and can be queried with the Query Builder application or included in reports created with Crystal Info. Object types, attribute classes, and attributes are discussed in the section entitled "Query Construction" later in this lesson. At this point in your studies, consider object types, attribute classes, and attributes as ways to categorize data in the site database. New attribute classes and attributes are selected when building query expressions or collection membership rules. Ultimately, the query expression determines which records are selected from the database when a query is executed.

New object types cannot be used to create collection membership rules.

Query Construction

When the domain of information about a particular subject is large, it makes most sense to find the information you are seeking about that subject by categorizing the data within the domain. "Vehicle" is an example of a subject about which there exists a large domain of information. What information is available about it? You know that there are many different types of vehicles: some have two doors, others have four; some vehicles are trucks, others are cars. If your goal is to determine the make and manufacturer of a vehicle, the number of doors it has and the type of wiper blades it uses are irrelevant. By categorizing the data, you don't wade through a lot of irrelevant data to obtain the information you seek.

Data is categorized in SMS 2.0 by object types, attribute classes, and attributes. In the analogy given above, vehicle is the domain of data, car is an object type, engine is an attribute class of car, and air filter is an attribute of engine. In SMS 2.0 resource is the domain of data and attributes are simply labels that define the data stored in the site database. Any attribute that exists in the site database can be queried.

Attributes, Attribute Classes, and Object Types

A site database contains hundreds of attributes, which are categorized by attribute class to make it easier to recognize their purpose. Attribute classes are contained in SQL Server tables and WBEM classes. Attributes are contained in SQL Server table columns and WBEM properties. Rows within the SQL Server tables contain the data that can be queried.

Attribute classes belong to any of seven categories—categories which in SMS are referred to as object types. A query can be defined using only one object type at a time. The first step in defining a query is to choose the object type that contains the data you seek.

The seven predefined object types in SMS—System Resource, Site, Package, Advertisement, Program, User Resource, and User Group Resource—are described below. Of these seven object types, System Resource contains multiple attribute classes; the remaining six contain a single attribute class.

See Appendix E of the SMS Administrator's Guide for a list of the most useful attribute classes in the System Resource object type. Five attribute classes and their attributes are listed in Appendix E also. For a list of all attribute classes and attributes, see the documentation included with the Microsoft SMS 2.0 Toolkit.

The Package, Advertisement, and Program object types are used to view information about software distribution.

The User Resource and the User Group Resource object types are used to view attributes of discovered domain users and domain groups.

After the object type is selected, the administrator determines which attribute classes or attributes within a class are required to obtain specific data from the site database. Attributes are stored within tables in the site database. Using the SMS Provider and WQL, Query Builder accesses data in the site database. Data is retrieved from the site database and returned to the administrator through the WBEM interface. Figure 8-1 shows sample attribute classes and attributes with their relationship to the object types.

Click to view at full size

Figure 8-1. The seven object types with sample attribute classes and attributes.

Relational Operators and Comparison Values

After it has been determined which attributes are to be queried, the query expression is built. The query expression is refined using relational operators and comparison values. Perhaps you want to know which client computers ran a software scan on the ninth day of the month. To complete this query, create a query expression using the Last Scan Date attribute. To refine the query, specify that the query should only return values where the Last Scan Data is equal to 9.

A relational operator describes how an attribute can meet the criteria specified in the query. For example, "is greater than," "is less than," and "is equal to," are all legal relational operators. The specific relational operators available depend on the type of attribute. Time-based attributes contain date operators such as "day is equal to" or "minute is less than," whereas text-based attributes contain string operators such as "is like" or "is equal to." The data types are numeric, string, and date/time.

A comparison value is a target value for which the query searches. Whenever a relational operator is used in an expression, it must be compared against something. The comparison value provides the expression with that "something" against which it is compared.

Query Types

Queries are classified as either simple or complex. A simple query contains a single expression or criterion while complex queries contain multiple expressions or criteria.

Simple Queries

Simple queries contain only one expression or criterion. For example, a query may be configured to search the database for all client computers that run Windows NT. The attribute class is "System Resource," the attribute is "Operating System Name and Version," the relational operator is "is like," and the comparison value is "Microsoft Windows NT %4.0." The percentage sign in the comparison value indicates to the query that no characters nor any string of one or more characters may be placed legally where the percentage sign is located. So, for example, both Windows NT Workstation 4.0 and Windows NT Server 4.0 would be included in the results of the query.

Here is a look at the expression used for the example given above:

Attribute Relational operator Comparison value
Operating System Name and Version is like Microsoft Windows NT %4.0

The query result would list all client computers in the site running Microsoft Windows NT version 4.0.

Complex Queries

Expressions can be combined to form complex queries. For example, a complex query can be generated to search for all client computers running Windows NT that do not contain Intel network interface cards. In this case, the expressions could be read as " Operating System - Name is 'Windows NT'" and " Network Adapter - Manufacturer is not like 'Intel.'" The expressions are combined using "and." Here is the corresponding expression:

Attribute Relational operator Comparison value
Operating System Name is like %Windows NT%
Manufacturer is not like %Intel%