Using the Queries and Reports SMS Features

SMS 2.0 Queries Overview

You can use a SMS query to search for information in the SMS site database. In fact, all data stored in the site database can be queried. This includes data relating to computer resources, sites, software distribution, and users and user groups. A query consists of one expression or multiple expressions conveyed using the WBEM Query Language (WQL). When the query is run against the SMS site database, the SMS Provider retrieves all data in the SMS site database tables that match the criteria defined in the expression. The results of the query are written to the SMS Administrator console.

There are a number of functions that you can use SMS queries for:

Find data in the SMS site database.
Find computers that meet specific criteria before sending packages.
Define collection memberships.

To create and run a query, the SMS Query Builder located within the SMS Administrator console is utilized. SMS 2.0 provides quite a number of predefined queries. You can though create and run your own SMS queries.

You can define a simple query by specifying the following:

The SMS object to search.
One or multiple attributes of the object.
An operator
A value

A SMS query is made up of a number of elements:

Attributes: The SMS site database contains numerous of attributes.
Attribute class: The attributes contained in the SMS site database are categorized by attribute class. A specific attribute class contains a list of attributes.
Object type: The Attribute classes belong to a category, called an object type. The attribute classes describe the object type. The predefined object types are:
- System Resource
- Site
- Package
- Advertisement
- Program
- User Resource
- User Group Resource

While the System Resource object type contains multiple attribute classes, the remainder of the object types only contain one attribute class.

Criterion type; defines what the attribute is being compared with.
Specify the relational operator and value to search for.

The different SMS object types and a few of their associated attribute classes and attributes are listed below:

System Resource object type:
- Attribute Class:
  - Collected File
- Attributes:
  - Collection Date
  - File Name
  - File Path
- Attribute Class:
  - Logical Disk
- Attributes:
  - File System
  - Free Space
  - Volume Name
- Attribute Class:
  - Memory
- Attributes:
  - Total Page file Space
  - Total Physical Memory
  - Total Virtual Memory
- Attribute Class:
  - Network Adapter
- Attributes:
  - Adapter Type
  - MAC Address
  - Manufacturer
- Attribute Class:
  - Operating System
- Attributes:
  - Build Number
  - Manufacturer
  - Version
- Attribute Class:
  - Processor
- Attributes:
  - Family
  - Manufacturer
  - Max Clock Speed
- Attribute Class:
  - System Resource
- Attributes:
  - IP Addresses
  - NetBIOS Name
  - Operating System Name And Version
Site object type:
- Attribute Class:
  - Site
- Attributes:
  - Build Number
  - Install Directory
  - Server Name
  - Site Code
  - Site Name
Package object type:
- Attribute Class:
  - Package
- Attributes:
  - Description
  - Manufacturer
  - Name
  - Package ID
  - Priority
Advertisement object type:
- Attribute Class:
  - Advertisement
- Attributes:
  - Advertisement ID
  - Advertisement Name
  - Collection ID
  - Package ID
  - Program Name
Program object type:
- Attribute Class:
  - Program
- Attributes:
  - Command Line
  - Comment
  - Disk Space Required
  - Package ID
  - Working Directory
User Resource object type:
- Attribute Class:
  - User Resource
- Attributes:
  - Full User Name
  - Resource ID
  - SMS Assigned Sites
  - User Name
  - Windows NT Domain
User Group Resource:
- Attribute Class:
  - User Group Resource
- Attributes:
  - Name
  - Resource ID
  - SMS Assigned Sites
  - User Group Name
  - Windows NT Domain
Software Product Compliance object type:
- Attribute Class:
  - Software Product Compliance
- Attributes:
  - Category
  - Product Company
  - Product Name
  - Product Version
  - Type

The different criterion types which you can specify are listed here:

Null Value; used to specify that the attribute value can be null or cannot be null.
Simple Value; used to specify a constant value against which the attribute is compared.
Prompted Value; used to specify that a value has to be prompted for before the query is run. This is the value to which the attribute is compared.
Attribute Reference; used to compare the query attribute to a different attribute which you specify.
Subselected Values; used to compare the query attribute to the results of a query.
List Of Values; used to specify a list of constant values against which the attribute is compared.

How to create a query

You use the SMS Query Builder located within the SMS Administrator console to create your own queries. The Query Builder can be accessed from the Queries node.

Open the SMS Administrator console.
Expand the Queries node to display existing queries.
Right-click the Queries node and select New and then Query from the shortcut menu.
The Query Properties dialog box opens.
On the General tab, in the Name textbox, provide a name for the query which you are creating.
If you want to copy or modify an existing query, click the Browse button to select the query.
In the Comment box, provide a meaningful description.
In the Collection Limiting section area of the General tab, you can select the following options:
- Not Collection Limited option; this is the default option which results in the query being run against the entire SMS site database.
- Limit To Collection option; and then specify the collection name.
- Prompt For Collection option; results in the prompting of the collection name when the query runs.
In the Object type box, provide the object type for the new query.
Click the Edit Query Statement button.
The Query Statement Properties dialog box opens.
On the General tab, you need to define what the query results window will display in the SMS Administrator console during the running of the query.
Click the New button to define an attribute class and an attribute.
The Result Properties dialog box opens.
Click the Select button.
The Select Attribute dialog box opens.
In the Attribute class drop-down list box, specify the attribute class.
You can select an alias.
In the Attribute drop-down list box, specify the attribute.
Click OK to close the Select Attribute dialog box.
When the Result Properties dialog box is displayed, you can define a sort order.
Click OK in the Result Properties dialog box. Use the process just illustrated to specify all the attributes you want displayed when the query runs.
The Query Statement Properties dialog box is displayed again.
Click the Criteria tab.
Click the New button to define the query criteria.
The Criterion Properties dialog box opens.
In the criterion type drop-down list, specify what the attribute will be compared to.
Click the Select button.
The Select Attribute dialog box opens.
Select the attribute class and attributes, and then click OK.
When the Criterion Properties dialog box is displayed, in the Operator drop-down list, chose the operator.
Click the Value button.
The Values dialog box opens, displaying all possible values.
Select the appropriate value and then click OK. This value will be shown in the Value textbox on the Criterion Properties dialog box.
Click OK in the Criterion Properties dialog box.
The Query Statement Properties dialog box is displayed again.
Click the Joins tab to display the database joins which are necessary for a query that contains attributes from different attributes classes.
Click OK to close the Query Statement Properties dialog box.
Click OK to the Query Properties dialog box.

How to create joins to different attribute classes

SMS automatically creates the links between different attribute classes as attributes from the different attribute classes that are selected. There may though be occasions where you have to manually create these links.

To create joins to different attribute classes;

Open the SMS Administrator console.
Expand the Queries node to display existing queries.
Open the Query Statement Properties dialog box for the query.
Click the Joins tab.
Click the New button.
The Attribute Class Join Properties dialog box opens.
In the Type drop-down select the join type:

- Inner; displays only the matching results.
- Left; displays all results for the base attribute and any matching results for the join attribute.
- Left; displays all results for the join attribute and any matching results for the base attribute.
- Full; displays all results for join attribute and for the base attribute.

Click the Select button located beneath the Join Attribute textbox to specify an attribute class and attribute.
In the Select Attribute dialog box, specify the attribute class and attribute. This will be displayed in the Join Attribute text box.
Click OK to close the Select Attribute dialog box.
In the Operator drop-down list, select the relational operator.
Click the Select button located beneath the Base Attribute textbox to specify the base attribute.
When the Select Attribute dialog box opens, select the base attribute. This will be displayed in the Base Attribute textbox.
Click OK to close the Select Attribute dialog box.
Click OK to close the Attribute Class Join Properties dialog box.
Click OK to close the Query Statement Properties dialog box.

How to edit existing queries

Existing queries are edited on the Criteria tab of the Query Statement Properties dialog box. The toolbar located on the Criteria tab can be used to perform a number of tasks to edit an existing query:

Add new criteria
Change the properties of a specific criterion.
Delete a specific criterion.
Set parentheses around a specified list of criteria.
Remove parentheses from a selected list of criteria.
Toggle the AND and OR operator to connect criteria.
Toggle the NOT operator on or off.

How to run a SMS query

After you have created an SMS query, you need to access the Queries node in the SMS Administrator console to run the query. All query results are displayed in the details pane of the SMS Administrator console.

To run a query;

Open the SMS Administrator console.
Expand the Queries node to display existing queries.
Right-click the specific query which you want to run and then select Run Query from the shortcut menu.
The Input Query Value dialog box is displayed for a query that contains prompts. Provide the necessary value and then click OK.
After the query executed, the query results are displayed in the details pane of the SMS Administrator console.

SMS 2.0 Reports Overview

SMS 2.0 includes Crystal Info 6.0, which is the report building tool available with SMS. Crystal Info can be used to create a number of reports which are based on data stored within the SMS site database. Once Crystal Info is installed, you can use the default reports provided, or you can design your own reports. You can also schedule reports to run against the data stored in the SMS site database.

Crystal Info is integrated with the SMS Administrator console. However, you have to install Crystal Info either when performing an Express setup installation or a Custom setup installation; or after you have installed SMS.

When you install Crystal Info, three Crystal Info services are installed on the SMS primary site server, or on another Windows computer residing in the SMS site.

The three Crystal Info services installed when you install Crystal Info are:

Info Agent: This is the main SMS reporting service that performs a number of functions:
- Coordinates the activities of the other Crystal Info services (Info APS service, Info Sentinel service)
- Processes the activities of the other Crystal Info services
- Processes user requests
- Processes files associated with reports
- Deals with permissions needed to run reports
- Manages configuration information
- Manages scheduling information.
- Manages SMS reporting auditing
Info APS: Responsible for passing requests to view a report to the Info Agent service.
Info Sentinel: Facilitates communication between the Info Agent service and the user running the report.

The different report types are listed here:

Cross-tab
Drill-down
Form
Form letter
Mail label
Online Analytical Processing (OLAP)
Subreport
Standard chart

The default reports that are created when you install Crystal Info are located beneath the Tools node in the SMS Administrator console.

The default reports are categorized as follows:

Configuration
Hardware Inventory
Product Compliance
Queries
Status

How to generate a report using Crystal info

Open the SMS Administrator console.
Expand the Tools node.
Expand the Reports node.
Right-click the report that you want to run and select All Tasks and then Schedule Report from the shortcut menu.
The Schedule Report Properties dialog box is displayed.
If you want to modify how the report is displayed, click the Modify button on the General tab of the Schedule Report Properties dialog box.
When the Info Report Designer dialog box opens, proceed to modify the appearance of the displayed report, and then click OK.
In the Schedule Report Properties dialog box, click the Refresh button on the General tab to update the report filename in the Description text box.
Click the Schedule tab.
In the Time area of the Schedule tab, choose between the following options to specify when the report should run:

- Not Scheduled
- Right Now
- At Time option, and then specify the time.

In the Recurrence drop-down list box, specify the recurrence pattern.
Click the Accounts tab.
In the Logon User Name textbox, the name of the user currently logged on is displayed. Provide the associated password if this account has the necessary permissions to run the report. If the account does not have sufficient permissions to run the report, then provide the account that does have the necessary permissions and specify the password.
Click the Selection Formula tab.
Click the Edit Selection Formula button if you need to edit the query statement used to create the data for the report.
Click the Prompts tab.
This is where you set which prompted values the user has to provide before the report can be generated.
Click OK.

How to create a report using Crystal info

Open the SMS Administrator console.
Expand the Tools node.
Expand the Reports node.
Right-click any report under the Reports node and select All Tasks and then Design New Report from the shortcut menu.
The Info Report Designer is initiated.
On the Info Report Designer welcome dialog box, click New Report, to start creating your new report.
The Report Gallery dialog box opens.
Specify the type of report which you want to create.
The Create Report Expert dialog box opens. This is where you create the display of the report.
On the Data tab of the Create Report Expert dialog box, click the SQL/ODBC button to define the data source.
When the Log On Server dialog box opens, specify the data source, and then click OK.
After you have specified the data source, the Configure Connection dialog box opens automatically.
In the Login properties area, in the User name textbox, provide the user account details that have the necessary access to the data.
In the Password textbox, provide the password associated with the user account specified.
In the Server name textbox, provide the name of the SMS database server name, and then click Connect.
After a connection is successfully established, the Namespace Selection box will show a tree structure that starts with the entry Root. Click OK.
The Choose SQL Table dialog box opens next.
Add each table that must be used for the report, and click the Done button once you have added all the necessary reports.
The Create Report Expert dialog box is displayed.
On the Links tab, choose the link for your report and delete the links which you do not need.
Click the Options button after you have selected the appropriate link.
The Link Options dialog box opens.
Specify the join which should be created between the tables and then click OK.
When the Create Report Expert dialog box is displayed, click the Fields tab.
Specify which fields should be displayed when the report is generated.
Use the remaining tabs of the Create Report Expert dialog box to define any additional settings for the report. The tabs are listed here:

- Sort tab
- Total tab
- TopN tab
- Drill tab
- Graph tab
- Select tab
- Style tab.

After adding all necessary settings for the report, click the Preview Report button located at the bottom of the Create Report Expert dialog box to view the display of the report.
If you need to modify the display, click the Info Report Design menu, click Format, and then click Report Style Expert.
To save the report template which you have created, click the Preview Report button, and then click File and select the Save option from the Info Report Design menu.

How to edit an existing report

Open the SMS Administrator console.
Expand the Tools node.
Expand the Reports node.
Right-click the report that you want to edit and click All Tasks and then Modify Report from the shortcut menu.
The Info Report Designer tool starts with the report that you have selected loaded.
Before you can make modifications to the report, you have to connect to the WBEM data source server.
After the connection to the WBEM data source server has been established, you can proceed with making the required changes.
When you are ready to save the report, first click the File menu and then click Options.
The Options Properties dialog box is displayed.
Click the Reporting tab.
Check that the Save Data With Report checkbox is not selected. Click OK.
In the SMS Administrator Console, right-click the report that you are working with, and select All Tasks and then Schedule Report from the shortcut menu.
The Schedule Report Properties is displayed.
Click the Refresh button on the General tab.
Run the report to create a new report that includes the modifications you have made.

Organizing Reports in Report Folders

How to create report folders to organize reports

Open the SMS Administrator console.
Expand the Tools node.
Expand the Reports node.
Right-click the Reports node and select New and then select Folder from the shortcut menu.
The Seagate Crystal Info dialog box opens.
Provide a name for the new report folder and then click OK.
The new report folder is displayed beneath the Reports node.

How to add a report to a report folder

Open the SMS Administrator console.
Expand the Tools node.
Expand the Reports node.
Right-click the report folder to which you want to add a report and select New and then select Report Object from the shortcut menu.
The New Report Object Properties dialog box opens.
In the Report File textbox, provide the path to the specific report that you want to add to the report folder.
In the Title textbox, provide a title.
In the Comment textbox, provide a description.
Click OK to add the report to the report folder.