Previous Section
 < Day Day Up > 
Next Section


Chapter 16: Queries and Reports

In the first three parts of this book we've covered the primary functions of Microsoft Systems Management Server (SMS) 2003. We've explored the inventory collection process, package distribution and management, software metering, and remote client management. Along the way, a lot of information has made its way into the SMS database, and you've seen periodic references to using a query or a report to extract information from the SMS database-for example, you might use a query as a membership rule to populate a collection or to view status messages generated by various client agents, or you can use a report to view collected software metering data. This part of the book focuses on database maintenance tasks, including extracting and analyzing data, setting security, and recovering data.

Queries are an efficient and relatively easy way to retrieve information from the SMS site database. SMS 2003 also makes a new SMS Report Viewer available as a reporting tool accessible through the SMS Administrator Console. We'll explore both methods of accessing SMS database information in this chapter.

Queries

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're 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're querying for computers with processors greater than 700 MHz. In this case, computer is an object, processor is an attribute of the object, greater than is the relational operator, and 700 MHz is the value.

You can use SMS queries 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.

You can generate SMS queries a couple of 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, WMI 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 can't use regular SQL queries or commands to extract data from the SMS database. Instead, you're specifying WMI object classes and attributes that the query uses to access and search the SMS database. For example, most of the queries that you create and use for collection membership will likely be based on the SMS_R_System discovery class, which contains discovery record properties such as IP Address, OperatingSystemNameandVersion, and Name, and on the SMS_G_System set of inventory classes, such as SMS_G_System_Processor, which includes processor data such as Name and ResourceId, and SMS_G_System_x86_PC_Memory, which includes memory data such as TotalPhysicalMemory.

More Info 

For more information about SMS object classes and properties, see the Microsoft Systems Management Server 2003 Software Development Kit (SDK), available through the Microsoft SMS Web site (http://www.microsoft.com/smserver) and through the Microsoft Developer Network (MSDN) program (http://msdn.microsoft.com).

Consequently, it's not recommended that you access the SMS database using regular SQL queries. What you need is a tool that can connect to WMI to access the SMS Provider and collect the information you require. You can use any third-party utility that's WMI Open Database Connectivity (ODBC)-compliant for this purpose. For example, you could use a reporting tool such as Microsoft Excel 2002 or Microsoft Access 2002 with the WMI ODBC drivers to report on SMS data. For more information about how to access a SQL database using a third-party tool, refer to the documentation for that tool.

SMS 2003 loads 21 predefined queries, as shown in Figure 16.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.

Click To expand
Figure 16.1: Predefined queries in SMS 2003.

Query Elements

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 16.2. As mentioned, you begin your query definition by selecting a WMI object class to query on. However, when you use the SMS Query Builder, these objects use friendly names that make it easier to select the correct object and attribute. We'll gear our discussion toward these friendly object names.

Click To expand
Figure 16.2: The relationship between objects, their attribute classes, and the attributes of each class.

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, among other elements. 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.

Table 16.1 lists the more frequently used object types, some of their attribute classes, and a short list of attributes.

Table 16.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 Metering Rule

Software Metering Rule

Enabled, File Name, File Version, Language, Rule Name, Site Code

Software Product Compliance

Software Product Compliance

Category, Product Company, Product Name, Product Version, Type

System Resource

Add Or Remove Programs

Display Name, Product ID, Publisher, Version

 

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, Microsoft Windows NT Domain

User Resource

User Resource

Full User Name, Resource ID, SMS Assigned Sites, User Name, Windows NT Domain

The criterion type defines what you're comparing the attribute with. The six criterion types are listed in Table 16.2.

Table 16.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 that you specify

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 16.3 outlines the subtle differences between these operators.

Table 16.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 16.4.

Table 16.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're 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 operator. 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 doesn't satisfy the statement preceded by the NOT. For instance, in our sample query we might have wanted to exclude all the computers running a version of Windows earlier than Windows 2000 for upgrade purposes.

Creating a Query

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 2000 that have at least 2 GB of free disk space (perhaps so that we can install Microsoft Office 2003 or upgrade to a newer version of Windows).

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:

  1. In the SMS Administrator Console, navigate to the Queries folder and expand it to view the existing queries.

  2. Right-click the Queries folder, choose New from the context menu, and then choose Query to display the Query Properties dialog box.

  3. In the General tab, enter a name for your query. This name can be up to 127 characters, so it can be quite descriptive (as shown in Figure 16.3). You can also select an existing query to copy and modify by clicking Import Query Statement.

    Click To expand
    Figure 16.3: The Query Properties dialog box, showing a descriptive query name.

  4. Enter a more detailed description of the query in the Comment text box if desired.

  5. In the Collection Limiting section, you can narrow the query's scope by selecting Limit To Collection and then typing in or browsing for the collection name. You can also make the query more interactive and therefore more useful by selecting Prompt For Collection, in which case you'll need to supply the collection name whenever the query is run. If you leave the default Not Collection Limited option selected, the query will be run against the entire database, assuming that the administrator executing the query has access to the entire database.

    Note 

    As described in Chapter 10, 'Remote Control of Client Systems,' and Chapter 17, 'Security,' you can create SMS security rights so that administrators have access to various objects in the database, including specific collections. If an administrator can't access a collection, the query won't run.

  6. Select the object type you want to run the query on, and then click Edit Query Statement to display the Query Statement Properties dialog box, shown in Figure 16.4.

    Click To expand
    Figure 16.4: The Query Statement Properties dialog box.

  7. In the General tab, you'll define the query results window-that is, the data (the attributes) displayed in the SMS Administrator Console when the query is run. To add a class and an attribute, click the New button (the yellow star) to display the Result Properties dialog box, shown in Figure 16.5.

    Click To expand
    Figure 16.5: The Result Properties dialog box.

  8. Click Select to display the Select Attribute dialog box, shown in Figure 16.6, where you define an attribute class and an attribute.

    Click To expand
    Figure 16.6: The Select Attribute dialog box.

  9. Enter or select an alias if desired. This must be a valid SQL alias. (Refer to your SQL documentation for more information about aliases.) Click OK to save your selections and return to the Result Properties dialog box.

  10. Select a sort order if desired, and then click OK to return to the Query Statement Properties dialog box.

  11. Repeat steps 7 through 10 to add as many attributes as you want displayed when the query is run. Remember, the query results displayed are based on your query criteria.

  12. Select the Criteria tab. In this tab you will actually define your query statement. Click the New button to display the Criterion Properties dialog box, shown in Figure 16.7. Here you will define the specific query elements.

    Click To expand
    Figure 16.7: The Criterion Properties dialog box.

  13. Select a criterion type from the drop-down list. To select an attribute class and an attribute to fill the Where text box, click Select to display the Select Attribute dialog box and choose the appropriate entries from the drop-down lists. In this example, because we're looking for computers with at least 2 GB of free space (2000 MB), our Attribute Class setting will be Logical Disk and the Attribute setting will be Free Space (MBytes).

  14. Click OK to return to the Criterion Properties dialog box and then select an appropriate operator from the drop-down list.

  15. Enter a value. If you click Values, SMS will display the Values dialog box, shown in Figure 16.8, which lists all the Free Space values currently recorded in the SMS database. You can select one of these values or enter the appropriate value (2000, in this case) in the Value text box and then click OK. Notice that the value will then be added automatically to the Value text box in the Criterion Properties dialog box.

    Click To expand
    Figure 16.8: The Values dialog box.

  16. The completed Criterion Properties dialog box is shown in Figure 16.9. Click OK to save your settings and return to the Criteria tab.

    Click To expand
    Figure 16.9: The completed Criterion Properties dialog box.

  17. Repeat steps 12 through 16 to add query statements and use the logical operator buttons listed in the Criteria tab to connect these query statements. The New button, used to add a query statement, creates an AND connection by default. Selecting the AND operator and clicking the &| button will change the AND to an OR, and clicking the ! button will change the AND to a NOT. The two Parentheses buttons are for grouping (or ungrouping) two or more selected statements.

  18. Group your statements together using parentheses to define the order of evaluation. For example, Figure 16.10 shows what the query statement would look like if we had not restricted the query to the All Windows 2000 Server Computers collection.

    Click To expand
    Figure 16.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 2000 Server 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 2000 Server version 5.0.2195 instead of clients that are running Windows 2000 Server and clients that have any operating system whose version is 5.0.2195 (however likely or unlikely that might be).

Combining Attributes

The Joins tab of the Query Statement Properties dialog box, shown in Figure 16.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 might need to create joins between different attribute classes manually.

Click To expand
Figure 16.11: The Joins tab.

To create your own joins to different attribute classes, follow these steps:

  1. Select the Joins tab and click the New button to display the Attribute Class Join Properties dialog box, shown in Figure 16.12.

    Click To expand
    Figure 16.12: The Attribute Class Join Properties dialog box.

  2. In the Type drop-down list, select the join type. Four types of attribute class joins exist in SMS:

    • Inner-Displays only matching results

    • Left-Displays all results for the base attribute and matching results for the join attribute

    • Right-Displays all results for the join attribute and matching results for the base attribute

    • Full-Displays all results for both the base and the join attributes

  3. To select an attribute class and attribute for the Join Attribute text box, click Select to display the Select Attribute dialog box, where you can select appropriate entries from the drop-down lists. The attribute you specified will be connected to the base attribute and becomes a child of the base attribute.

  4. Choose an appropriate relational operator from the Operator drop- down list.

  5. To fill in the Base Attribute text box, click Select to display the Select Attribute dialog box and choose the appropriate base attribute. The base attribute class is an existing attribute class on which you based the query. Notice that you can't change the base attribute class; you can change only the base attribute.

  6. Click OK to close and save your query configuration.

  7. Click OK again to save the query.

    More Info 

    Working with joins requires a better-than-good understanding of SMS attribute classes and attributes. For a complete discussion of WQL, refer to the Microsoft Systems Management Server 2003 Software Development Kit, as mentioned earlier in this chapter.

Viewing the Query Language

Figure 16.13 shows our sample query using WQL. You can display the WQL version of any query by clicking Show Query Language in the General, Criteria, or Join tab of the Query Statement Properties dialog box. As you can see, writing an SMS 2003 query using WQL is not trivial.

Click To expand
Figure 16.13: The WQL version of our sample query.

Prompted Queries

The query we just created will satisfy our immediate quest for information from the SMS database. However, it's static in the sense that it will always check the database for the same information-that is, all computers running Windows 2000 Server that have at least 2 GB 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 2000, 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 it in the SMS Administrator Console and choosing Properties from the context menu to display the Query Properties dialog box. Click Edit Query Statement to return to the Query Statement Properties dialog box, select the Criteria tab, and then double-click the element you want to modify to display the Criterion Properties dialog box. In the example shown in Figure 16.14, we're modifying the Size value. The criterion type has been changed from Simple Value to Prompted Value.

Click To expand
Figure 16.14: An example of a prompted query.

Compare this figure with Figure 16.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).

Executing Queries

Now that we've 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:

  1. In the SMS Administrator Console, navigate to the Queries folder.

  2. Right-click the query you want to run and choose Run Query from the context menu.

  3. If the query contains any prompts, the Input Query Value dialog box appears, as shown in Figure 16.15. Enter the appropriate value and click OK.

    Click To expand
    Figure 16.15: The Input Query Value dialog box.

  4. You can view the results of the query in the result pane of the SMS Administrator Console interface, shown in Figure 16.16. You'll need to scroll to the right to see all the result fields you chose to display.

    Click To expand
    Figure 16.16: The query results.

As with other SMS-managed objects, such as collections, packages, and advertisements, only users who 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 17.



Previous Section
 < Day Day Up > 
Next Section