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
- Explain what a query is and how it is used in SMS 2.0.
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.
NOTE
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.
- System Resource
This is the object type used for running queries on discovered resources and client computer inventory (hardware and software). This object type contains over 100 attribute classes. Attribute classes contain attributes that describe the class. For example, the Logical Disk attribute class contains a free space attribute, and the Memory attribute class contains a total physical memory attribute.
NOTE
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.
- Site
This object type is used to view the types of sites in the hierarchy, the version of SMS running on the site servers, the site name, and the server name.
The Package, Advertisement, and Program object types are used to view information about software distribution.
- Package
- Advertisement
- Program
This object type is used to track where packages are installed so that they can be removed or refreshed as necessary.
This object type is used to query package advertisement attributes, primarily for the status of advertisements.
This object type is used to view the programs configured for packages.
The User Resource and the User Group Resource object types are used to view attributes of discovered domain users and domain groups.
- User Resource
- User Group Resource
A query using this object type retrieves data about the domain users' accounts in all domains managed by SMS. The user query provides SMS with a collection target other than client computers.
A query using this object type retrieves data about the global groups in the domains managed by SMS. This global group query provides SMS with a collection target other than client computers. Only global groups, not local groups, are attributes within the User Group Resource object type.
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.
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% |
| and | ||
| Manufacturer | is not like | %Intel% |
EAN: 2147483647
Pages: 107