Creating and Editing Query Statements

  • Article

The processes for creating or editing a query statement are the same. Before you begin creating or editing query statements, read the "Understanding SMS Queries" section earlier in this chapter.

You can create and edit query statements by:

  • Using the Query Statements Properties dialog box in Query Design view and using the command buttons and properties on the General, Criteria, and Joins tabs.

  • Using the Query Statements Properties dialog box in Query Language view and typing a WQL query statement into the Query Statement text box.

This section describes how to create and edit query statements by using the Query Statements Properties dialog box in Query Design view.

Important

  • Use the Query Language view only if you have a good working knowledge of WQL. If you enter a query that is not valid (for example, one that is not syntactically correct), you will get an error message. If the query statement that you edit uses features of WQL that are not supported in the Query Design view, you cannot return to the Query Design view. However, you can still save and run the query.

For information about using WQL, see the SMS SDK and the Windows Management Instrumentation SDK, which are available from the MSDN Web site at https://msdn.microsoft.com.

On This Page

Creating an Example Query
Creating Queries Against Multiple SMS Object Types

Creating an Example Query

This section describes, in a series of procedures, the steps that are necessary to create an example query statement. The example query returns all clients running Windows 2000 Professional with Pentium III processors and with more than 1.5 GB of free disk space.

To do this, you must create a query to search the System Resource object type, and also create two criteria for the query that narrow the search. The first criteria limits the query results to clients with Pentium III processors, as designated by their description of %Pentium III%. The second criteria limits the query results to clients that satisfy the first condition and have more than 1.5 GB of free disk space. You further narrow the results of the query by limiting it to the collection that contains all clients running Windows 2000 Professional.

To create a query statement

  1. Navigate to Queries in the SMS Administrator console.

  2. Right-click Queries, point to New, and then click Query.

    The Query Properties dialog box opens. For new queries, the System Resource object type is selected by default.

  3. Click Edit Query Statement.

The Query Statement Properties dialog box opens.

Configuring properties on the General tab

You use the General tab of the Query Statement Properties dialog box to specify which attributes you want to display and to specify how to display the data that the query returns when it is run. If you want all attributes for the specified object type to display, leave the Results area blank.

To specify attributes to be displayed

  1. In the Results area, click New.

  2. In the Results Properties dialog box, click Select.

    The Select Attribute dialog box opens.

  3. Select the Processor attribute class from the Attribute class list.

  4. Select the Name attribute class from the Attribute list and click OK.

  5. If you want to sort the query results by using this attribute, in the Sort list, select Ascending or Descending.

Note

  • Sorting and grouping of array attributes are not supported. If you select any of the following array attributes, then the results data cannot be sorted based on those attributes:

    • System Resource: Agent Name, Agent Site, Agent Time, IP Addresses, IP Subnets, IPX Addresses, IPX Network Numbers, MAC Addresses, Resource Names, SMS Assigned Sites, SMS Installed Sites, System Roles

    • User Resource: Agent Name, Agent Site, Agent Time, SMS Assigned Sites

    • Package: Icon

    • Program: Icon

Configuring properties on the Criteria tab

You use the Criteria tab of the Query Statement Properties dialog box to specify the criteria by which the query selects records to display. Criteria are based on attributes of the object type, a relational operator, and a value.

The criteria for the example query statement described earlier, which returns all clients with Pentium III processors and with more than 1.5 GB of free disk space, is shown below as it appears on the Criteria tab in the Query Design view:

Processor.Name is like "%Pentium III%"and
LogicalDisk.FreeSpace (MBytes) is greater than 1500

To create the criteria for the example query, perform the steps in the following procedures.

To select criterion type

  1. In the Query Statement Properties dialog box, click the Criteria tab, and then click New. The Criterion Properties dialog box opens.

  2. In the Criterion type list, click a criterion type. For the example query, click Simple value.

    The criterion type tells the processor what to expect for a criterion. For more information, see the "SMS Criterion Types and Values" section earlier in this chapter.

To select attribute class and attribute

  1. In the Criterion Properties dialog box, click Select.

  2. In the Select Attribute dialog box, click an attribute class in the Attribute class list. For the example query, click Processor.

  3. Click an attribute in the Attribute list. For the example query, click Name.

  4. Click OK to close the Select Attribute dialog box.

To select a relational operator

  1. In the Criterion Properties dialog box, click an operator in the Operator list. For the example query, click is like.

  2. Click OK to close the Criterion Properties dialog box.

Note

  • There are four data types for SMS queries: numerical, date/time, string, and parameterized. Each data type has its own list of relational operators. Only the list of operators that applies to the selected attribute's data type is displayed. For more information, see the "SMS Relational Operators" section earlier in this chapter.

To select a value to compare with the attribute

  1. In the Value box, enter a value for the query to compare with the attribute that you have selected. For the example query, type %Pentium III%.

    -Or-

    Click Values to select from a list of available values.

    If a list of values exists for the attribute you chose, that list appears in the Values dialog box.

  2. Click OK to close the Criterion Properties dialog box.

Note

  • The SMS Provider can run out of memory while caching a large result set. To avoid this, and to maintain performance, the Query Builder limits the number of values displayed in the Values dialog box to the first 2000. You can override this by changing registry settings. For more information, see article number 269201 in the Microsoft Knowledge Base at https://support.microsoft.com.

For more information about attribute classes, attributes, and values, see the "SMS Criterion Types and Values" section earlier in this chapter.

Create additional criteria

By completing the previous steps you have created the following expression, shown as it appears on the Criteria tab in the Query Design view:

Processor.Name is like "%Pentium III%"

Often, your query requires more than one criterion. In the previous example, the query returns all clients that have Pentium III processors. To modify the search to include those Pentium III processors that have 1.5 GB of free disk space, you must add another criterion. You can add as many criteria as you want, and each one further limits (AND, NOT) or expands (OR) the query. In the example, create a second criterion with the following properties, repeating the instructions in the previous steps if necessary:

  • Criterion type of Simple Value

  • Attribute class of Logical Disk

  • Attribute of Free Space

  • Operator of is greater than

  • Value of 1500

The second criterion appears below the first criterion as follows:

Processor.Name is like "%Pentium III%"and
LogicalDisk.FreeSpace (MBytes) is greater than 1500

Choose the logical operator

By default, the AND operator connects the two criterion. In the Query Statement Properties dialog box, click the And Or button to replace the AND with OR. Select one of the expressions and click the Not button to insert NOT before the expression. For the example, leave the default AND as the logical operator.

Choose parentheses

In the example, there are no parts of the criteria expression that require grouping. Grouping with parentheses is used to clarify the meaning of expressions and to cause the expression or expressions within the parentheses to be evaluated first. If your query statement requires parentheses, highlight the expression or expressions that you want to place within the parentheses and click the Parentheses button.

By following these steps, you have created the following expression, shown as it appears on the Criteria tab in the Query Design view:

Processor.Name is like "%Pentium III%"and
Logical Disk.FreeSpace (MBytes) is greater than 1500

To view the full query in the Query Language view, click Show Query Language in the Query Statement Properties dialog box.

To configure the query to return only clients running Windows 2000 Professional with Pentium III processors and that have greater than 1.5 GB of free disk space, you must limit the query to the All Windows 2000 Professional Systems collection.

To limit the query to a collection

  1. Click OK to close the Query Statement Properties dialog box and return to the Query Properties dialog box.

  2. On the General tab, in the Collection Limiting area, click Limit to a collection.

  3. Click Browse, and in the Browse Collection dialog box, click the All Windows 2000 Professional Systems collection.

Note

  • When you limit a query to a collection, the query is limited only to the collection you specify and is not limited by any subcollections of the specified collection.

For more information about limiting collections, see the SMS Help.

Creating Queries Against Multiple SMS Object Types

When you create a query by using the SMS Query Builder, you are limited to using the attributes of only one SMS object type at a time. You can use the <unspecified> object type to query against more than one SMS object type at a time.

When you use the <unspecified> object type, you can only create a query by using WQL in the Query Language view. You can use this to create free-form WQL queries to run against more than one SMS class. You must have a good understanding of WQL to use this feature.

To create a WQL query against multiple SMS object types

  1. In the SMS Administrator console, navigate to Queries.

  2. Right-click Queries, point to New, and then click Query.

    The Query Properties dialog box opens.

  3. In the Object Type list, click <unspecified>, and then click Edit Query Statement.

    The Query Statement Properties dialog box opens in the Query Language view.

  4. In the Query statement box, type a valid WQL query statement.

The following is an example of a WQL query that queries both the System Resource and the User Resource SMS object types:

SELECT R.Name, U.UniqueUserName FROM SMS_R_System R, SMS_R_User U WHERE 
R.LastLogonUserName=U.UserName
For More Information

Did you find this information useful? Please send your suggestions and comments about the documentation to .smsdocs@microsoft.com.