System Center Configuration Manager 2007 : Creating Queries (part 2) - ConfigMgr Query Builder

11/21/2011 9:13:49 AM

ConfigMgr Query Builder

Creating a query in ConfigMgr is fairly simple. The easiest way to create and run a query is by using the New Query Wizard, which is built in to the ConfigMgr console. Perform the following steps:

1.	To launch the wizard, right-click the Queries node and then choose New -> Query. Figure 2 displays the New Query Wizard’s General page. This example shows the name Systems with ConfigMgr SP1 Client entered, along with a description for the query. The default object type of System Resource is selected from the dropdown Object Type list box. Figure 2. New Query Wizard’s General page for the Systems with ConfigMgr SP1 Client query
2.	The General page includes the Import Query Statement button, which allows you to browse the existing queries and select one to use as a starting point for your new query. The Collection Limiting section of the page lets you select objects only from a collection you specify or a collection supplied by the user at runtime. The Systems with ConfigMgr SP1 Client query will be a new query statement created from scratch, and will not limit the results to a specific collection. To create the query statement, click the Edit Query Statement button. The New Query Wizard now displays the Query Statement Properties dialog box, displayed in Figure 3. Figure 3. The Systems with ConfigMgr SP1 Client Query Statement Properties dialog box
3.	You can click the New button to display the Result Properties dialog box, which allows you to select the resource type and attributes that the query will retrieve. On the Result Properties dialog box, you can click the Select button to display the Select Attribute dialog box. Figure 4 displays the Result Properties dialog box, with the Select Attribute dialog box in the foreground. Figure 4. Choosing an attribute to display in the query results Figure 4 also shows the System Resource attribute class and Name attribute selected. Optionally, you could supply an alias to use as the column header for the Name column. After clicking OK to return to the Result Properties dialog box, add three additional system resource attributes to the query results: IPAddresses OperatingSystemNameandVersion OperatingSystemServicePack
4.	After you determine that the query will display system resources and have selected those attributes you want to see, the next step in designing the query is to specify criteria for the systems you want to display. On the Query Statement Properties dialog box’s Criteria tab, click the New button to display the Criterion Properties dialog box. On the Criterion Properties dialog box, choose Simple Value from the Criterion Type list box and then click the Select button to select the attribute to use in the criterion. Figure 5 displays the Criterion Properties dialog box with the Select Attribute dialog box in the foreground. The attribute class used here is SMS Advanced Client State, and the specific attribute is Version. Figure 5. Choosing an attribute to use in a selection criterion for query results
5.	After choosing the criterion attribute and clicking OK to return to the Criterion Properties dialog box, you can choose the operator and value for the selection criterion. Figure 6 shows the full criterion used: SMS Advanced Client State – Version is equal to 4.0.6221.1000. This is the client version for Configuration Manager 2007 Service Pack (SP) 1. Figure 6. The completed Criterion Properties dialog box
6.	To complete the wizard, click OK to accept the criterion properties and OK again to complete the query properties. Click Next to return to the New Query Wizard’s General page. You will now have an opportunity to modify security on your query.

Reports Versus Queries in ConfigMgr 2007

You may be asking yourself whether you should use a report or a query. As usual, it depends. The main reason to use one technology over the other is the intended audience. For data used only by the ConfigMgr administrator, a query may be sufficient. However, for data that must be available to a very large audience, a web report will probably be preferred.

If you plan to build a query-based collections, you may want to consider creating a ConfigMgr query first. By creating a query, you can see the data on which you based your criteria in the WQL statement. As an example, you need to create a collection of systems that have a specific piece of software installed. When you create a query-based collection based on an installed file, the collection members will appear in the collection, but not the specific criteria built in the WQL statement. If you create that same WQL statement in a ConfigMgr query, you will be able to see the value of your criteria, which enables you to verify visually that your query is returning exactly what you intended. After verifying the data, insert the WQL into a query-based rule for the desired collection.

Table 2 lists some points to consider when deciding between queries and reports.

Table 2. Comparing Usage of Queries to Reports
Capability	Query	Report
Access to data	Requires access to the ConfigMgr console or the use of script	Access data from any web browser.
Export data	Ability to copy data from the ConfigMgr console and paste it into Excel	Ability to copy/paste data, as well as export to comma-separated value (.csv) file. Using SQL Reporting Services (SRS), you can also subscribe to reports, so they are sent directly to your email.
Building queries	Provides an intuitive query builder that automatically creates the necessary joins between classes of data	Provides a basic query builder, and requires you to define the proper joins, which can be complicated. You can use SQL Management Studio to build the queries, but are still required to define the joins.