As you know, the premise behind any database query is the return of information based on a set of criteria. In other words, you define what information you are trying to obtain in the form of a query statement. The query engine then searches the database for entries that match your criteria. The query result then displays the data that matched your criteria.
The same is true for SMS queries. To define a simple SMS query, you would specify an SMS object to search on, one or more attributes of the object, an operator of some kind, and a value. For example, suppose you are querying for computers with processors greater than 155 MHz. In this case, computer is an object, processor is an attribute of the object, greater than is the relational operator, and 155 MHz is the value.
SMS queries can be used for a variety of purposes. Generally, we think of queries as a means of reporting on data in the database. Indeed, we might use SMS queries to find all the computers that meet a certain memory, disk space, and platform requirement before sending out a package to them. And as we've seen, queries are particularly useful in defining collection memberships. Collections whose members are based on the results of a query can be updated periodically to keep them current. Any programs advertised to a collection are automatically made available to the collection's members. As the query runs and updates the collection, new members automatically receive any advertisements that targeted the collection, and deleted members no longer receive the advertisements.
SMS queries can be generated a couple of different ways. The easiest way to create and run a query—and the easiest method to learn—is using SMS Query Builder, which is built into the SMS Administrator Console. This interface provides you with a point-and-click method for building your query. You could also write the query statements yourself; however, this method entails learning a query language—specifically, WBEM Query Language (WQL).
Unlike other SQL Server databases, SMS relies on the Windows Management Instrumentation (WMI) layer to expose its database information to the SMS Administrator Console and other tools. Therefore, you cannot use regular SQL queries or commands to extract data from the SMS database.
CAUTION
Don't even try using SQL queries against your SMS database. If you do, you might corrupt the database.
What you need is a tool that can connect to WMI to access the SMS Provider and collect the information you require. Any utility that is WBEM ODBC-compliant can be used for this purpose. Because the SMS Administrator Console uses WMI and the SMS Provider to manage the database, its query functions and its installation of Crystal Info for SMS are probably the easiest way to display information contained in the SMS database. However, you could use a reporting tool such as Microsoft Excel 97 or Microsoft Access 2000 with the WBEM ODBC drivers to report on SMS data. We will review those methods in the section "Other Reporting Tools" later in this chapter.
SMS 2.0 loads 33 predefined queries, some of which are shown in Figure 15-1. As you can see, these predefined queries are fairly general in scope and are meant to be more globally oriented, perhaps as the target of an advertisement. However, you can certainly create your own queries—for example, to assist with certain management tasks, including populating and updating collections and viewing client status messages.
Figure 15-1. Some of the predefined queries in SMS 2.0.
Before we review the steps for creating a query, let's take a look at the individual elements that make up a query. The relationship between these elements is illustrated in Figure 15-2. As mentioned, you begin your query definition by selecting an object to query on. SMS provides several object types for generating queries. An object type has specific attribute classes that describe it. For example, the System Resource object type is defined by its memory, environment, logical disk, processor, and network attribute classes, and so on. An attribute class is essentially a category of attributes and contains an attribute list. For example, the System Resource attribute class includes the IP Addresses, IP Subnets, NetBIOS Name, Operating System Name and Version, and SMS Assigned Sites attributes.
Figure 15-2. The relationship between objects, their attribute classes, and the attributes of each class.
Table 15-1 lists the more frequently used object types, some of their attribute classes, and a short list of attributes.
Table 15-1. SMS objects and some of their attribute classes and attributes
Object Type | Attribute Classes | Attributes |
---|---|---|
Advertisement | Advertisement | Advertisement ID Advertisement Name Collection ID Package ID Program Name |
Package | Package | Description Manufacturer Name Package ID Priority |
Program | Program | Command Line Comment Disk Space Required Package ID Working Directory |
Site | Site | Build Number Install Directory Server Name Site Code Site Name |
Software Product Compliance | Software Product Compliance | Category Product Company Product Name Product Version Type |
System Resource | Collected File | Collection Date File Name File Path |
Logical Disk | File System Free Space Volume Name | |
Memory | Total Pagefile Space Total Physical Memory Total Virtual Memory | |
Network Adapter | Adapter Type MAC Address Manufacturer | |
Operating System | Build Number Manufacturer Version | |
Processor | Family Manufacturer Max Clock Speed | |
System Resource | IP Addresses NetBIOS Name Operating System Name And Version | |
User Group Resource | User Group Resource | Name Resource ID SMS Assigned Sites User Group Name Windows NT Domain |
User Resource | User Resource | Full User Name Resource ID SMS Assigned Sites User Name Windows NT Domain |
MORE INFO
For a complete list of objects, attribute classes, and their attributes, refer to Appendix E in the Systems Management Server Administrator's Guide. When you define your query to SMS, in addition to a name you will need to supply the object type, attribute class, and attributes you want to search on. You will usually need to supply criterion types and values; relational and logical operators; and sometimes attribute class joins, order of preference, or WBEM Query Language.
The criterion type defines what you are comparing the attribute with. The six criterion types are listed in Table 15-2.
Table 15-2. Criterion types
Type | Description |
---|---|
Null Value | Used when the attribute value may or may not be null |
Simple Value | Constant value against which the attribute is compared |
Prompted Value | Prompts you to enter a value before the query is evaluated |
Attribute Reference | Lets you compare the query attribute to another attribute that you identify |
Subselected Values | Lets you compare the query attribute to the results of another query |
List Of Values | List of constant values against which the attribute is compared |
Along with the criterion type, you will select a relational operator and supply a value to search for. This value can be null, numeric, a string, or a date/time. The list of relational operators is pretty much what you would expect: Is Equal To, Is Not Equal To, Is Greater Than, Is Less Than, and so on. However, the kinds of operators that are available depend on whether the attribute is null, numeric, string, or date/time. Table 15-3 outlines the subtle differences between these operators.
Table 15-3. Relational operators
Data Type | Relational Operators |
---|---|
Null | Is Null Is Not Null |
Numeric | Is Equal To Is Not Equal To Is Greater Than Is Less Than Is Greater Than Or Equal To Is Less Than Or Equal To |
String | Is Equal To Is Not Equal To Is Like Is Not Like Is Greater Than Is Less Than Is Greater Than Or Equal To Is Less Than Or Equal To |
Date/Time | Unit Is Equal To Unit Is Not Equal To Unit Is Greater Than Unit Is Less Than Unit Is Greater Than Or Equal To Unit Is Less Than Or Equal To Unit Is |
Date/Time | Unit Is Not Unit Is After Unit Is Before Unit Is On Or After Unit Is On Or Before |
(Unit is a date or time unit—millisecond, second, minute, hour, day, week, month, or year.)
When string values are used in a query, the exact string must be provided, without quotation marks unless the quotation marks are part of the string. If you use either the Is Like or Is Not Like relational operator, you can use wildcard characters as part of the string. Acceptable wildcard characters include those shown in Table 15-4.
Table 15-4. Wildcard characters
Symbol | Meaning |
---|---|
% (percent) | Any string of characters |
_ (underscore) | Any single character |
[ ](brackets) | Any character within a specified range of characters |
^ (caret) | Any character not within the specified range of characters |
For example, if we wanted to query the database for all SMS clients that contained the string FIN in the client name, we might use the value %FIN%. String operators are not case-sensitive unless the SQL code page you are using uses case-sensitive comparisons.
In real life, your queries will probably be more complex and will consist of several query statements. These statements are connected using logical operators and are grouped for evaluation using parentheses. The three primary logical operators used with SMS queries are AND, OR, and NOT.
An AND operation finds all data that matches two query statements connected by the AND. AND operations generally result in a more restricted search since every expression must be satisfied to generate a result.
An OR operation finds all the data that matches any portion of the two statements connected by the OR. As you might expect, OR operations generally result in a broader search since any expression may be satisfied to generate a result.
A NOT operation finds all the data that does not satisfy the statement preceded by the NOT. For instance, in our sample query we might have wanted to exclude all the computers running a lower version number of Windows 95 for upgrade purposes.
Now that you've gotten your feet wet, let's put some of these SMS query elements to use by creating a query. Our test query will search for all computers running Windows 95 that have at least 300 MB of free disk space (perhaps so that we can install Microsoft Office 2000 or upgrade to Windows NT or Windows 2000).
As we've seen in previous chapters, you can create a query from a number of locations—for example, you can create or reference a query when you define the membership of a collection, or you can create a status message query in the Status Message Queries folder in the SMS Administrator Console. The process is essentially the same wherever the query is created. For this example, we'll create a query from the Queries folder in the SMS Administrator Console. To do so, follow these steps:
Figure 15-3. The Query Properties window.
NOTE
As described in Chapters 11 and 16, you can create SMS security rights so that administrators have access to various objects in the database, including specific collections. If an administrator cannot access a collection, the query will not run.
Figure 15-4. The Query Statement Properties window.
Figure 15-5. The Result Properties window.
Figure 15-6. The Select Attribute dialog box.
Figure 15-7. The Criterion Properties window.
Figure 15-8. The Values dialog box.
Figure 15-9. The completed Criterion Properties window.
Figure 15-10. A sample query statement using logical operators and parentheses.
Notice that this example also specifies more precisely the version number of computers running Windows 95 and that the collection is more generic. Notice too that the operating system name and version are grouped together to ensure that we evaluate clients as those running Windows 95 version 4.0.1111 instead of clients that are running Windows 95 and clients that have any operating system whose version is 4.0.1111.
The Joins tab of the Query Statement Properties window, shown in Figure 15-11, displays the links made between the attribute classes. This linking is done for the most part automatically by SMS as you select attributes from different attribute classes. Sometimes, however, because of the nature of the query, you may need to create joins between different attribute classes manually.
Figure 15-11. The Joins tab.
To create your own joins to different attribute classes, follow these steps:
Figure 15-12. The Attribute Class Join Properties window.
MORE INFO
Working with joins requires a better than good understanding of SMS attribute classes and attributes. For a complete treatment of joins, refer to Chapter 11 in the Systems Management Server Administrator's Guide. You should also refer to the SMS 2.0 Toolkit for a complete discussion of WQL. A sample of the toolkit is included with the Microsoft BackOffice 4.5 Resource Kit.
Figure 15-13 shows our sample query using WQL. You can display the WQL version of any query by clicking the Show Query Language button on the General, Criteria, or Join tab of the Query Statement Properties window. As you can see, writing an SMS 2.0 query using WQL is not trivial.
Figure 15-13. The WQL version of our sample query.
The query we just created will satisfy our immediate quest for information from the SMS database. However, it is static in the sense that it will always check the database for the same information—that is, all computers running Windows 95 that have at least 300 MB of free disk space.
A more useful query would be one that prompts us for value information as the query is being evaluated. For example, instead of hard-coding the value 300, it might be more useful to have the query prompt us for the Size value. This way, we can use the query repeatedly to find computers with different amounts of free space for different packages and purposes.
To change our query to a prompted query, we need to open it and modify it. You can modify any query by right-clicking on it in the SMS Administrator Console and choosing Properties from the context menu to display the Query Properties window. Click the Edit Query Statement button to return to the Query Statement Properties window, and select the Criteria tab, and then double-click on the element you want to modify to display the Criterion Properties window. In the example shown in Figure 15-14, we are modifying the Size value. The criterion type has been changed from Simple Value to Prompted Value.
Figure 15-14. A sample prompted query.
Compare this figure with Figure 15-9, and you'll see that the Value field has changed to indicate a prompted value. When this query is executed, it will first ask us to provide the value for Logical Disk - Free Space (Mbytes).
NOTE
The following statement is taken directly from the Microsoft Systems Management Server Version 2.0 Service Pack 1 Release Notes: "Running queries that include prompted value criteria that also use an alias for an attribute class is not supported in this release of SMS."
Now that we have seen how to create a query, it's time to explore how to run a query. All SMS queries are run through the SMS Administrator Console. The results of the queries will also be displayed in the SMS Administrator Console. To execute our sample query, follow these steps:
Figure 15-15. The Input Query Value dialog box.
Figure 15-16. The query results.
As with other SMS-managed objects such as collections, packages, and advertisements, only users that have access to the database objects will be able to run the query. The user must have rights to execute the SMS Administrator Console, rights to access the Queries folder, and rights to access data in the SMS database. This permission is assigned by applying object security through the SMS Administrator Console, or sometimes through the WMI itself. SMS security is discussed in more detail in Chapter 16.