SMS 2003 Reports

Reporting in Microsoft Systems Management Server (SMS) 2003 is integrated into the SMS Administrator console. A number of predefined reports that you can use to gather important information from your site database are provided in the product. For many administrators, these reports provide sufficient information to administer their computer infrastructure and SMS system. If you find that your information needs to extend beyond the predefined reports, you can create your own reports or copy and modify predefined reports.

For more information about creating reports in SMS 2003, see Chapter 11: Creating Reports in the Systems Management Server 2003 Operations Guide.

SMS 2003 Report Types

SMS 2003 has four main report types: predefined, custom, supplemental, and dashboards.

  • Predefined reports: A variety of reports are provided with SMS 2003 to help you quickly obtain information that is useful to the administration of your SMS operations. The predefined reports include information about hardware, software, software distribution, software metering, software updates, network, operating system, SMS site, status messages, and so forth.

  • Custom reports: Reports that you create either by copying and modifying predefined reports or by creating new reports. To create a new report, you must specify a SQL statement that determines which records are returned when the report is run.

  • Supplemental reports: Reports created outside of SMS 2003, which you can place in a designated folder on a reporting point to extend your reporting capabilities. These reports will primarily be Active Server Pages (ASP) pages. However, it can be any file that you can display by using Microsoft Internet Explorer 5.0 or later. Because supplemental reports are not secured SMS objects, any user can view them unless you secure them by using Microsoft Internet Information Services (IIS) security.

  • Dashboards: Sets of reports that are displayed in a grid within a single window of Report Viewer. You can use dashboards to quickly obtain information about a variety of topics.

SMS 2003 reports include the ability to prompt for information when the report is run, as well as linking one report to another, which passes information from the first report to the second report and bypasses the prompt of that second report.

Report Prompts

A prompt is a report property that you can configure when you create or modify a report. When a user runs the report, a prompt requests the user to enter a value for a required parameter prior to running the report. A report can contain more than one prompt.

You can use prompts to limit or target the data that a report retrieves. For example, you create a report that retrieves hardware inventory data for a given computer and prompts the user for a computer name. Report Viewer then passes the user-specified value to a variable that is defined in the SQL statement for the report. Provided that you have properly configured the SQL statement, the report returns hardware inventory data only for the specified computer.

You can use a link in a source report to provide users with ready access to additional data, such as more detailed information about each of the items in the source report. For example, you might link a report that lists all site codes to another report that lists all recent error messages for a given site code. The source report passes a specific site code to the target report based on which line item in the source report the user chose to obtain more information. A report can be configured with only one link, and that link can connect to only one target report.

SQL Statement

The principal element of a report is a SQL statement that defines which data the report gathers and returns as the result set or multiple result sets. The SQL statement in a report does not run directly against your SMS site database tables. Instead, the SQL statement runs against a set of views, which point to records in your SMS site database tables. Each time you run a report, the information returned consists of current data.

Creating Custom Reports

Microsoft Systems Management Server (SMS) 2003 provides a number of predefined reports that you can use to gather important information from your site database. For many administrators, these reports provide sufficient information to administer their computer infrastructure and SMS environment. However, if the predefined reports do not provide the information that you are looking for, you can create a new custom report, clone a predefined report that provides some of the desired data and then modify it to fit your needs, or modify a predefined report.

The SQL statements in custom SMS reports are created in the Report SQL Statement dialog box in the SMS 2003 Administrator console, which can be difficult and requires SQL experience, or by using the DTS Query Designer or SQL Query Analyzer query design tools that are built into SQL Server. For more advanced queries or for users who are less experienced in writing query statements, the query design tools provide a simple interface for writing SQL query statements. For more information about the query design tools, see Query Design Tools.

The following sections can be used as reference information to create effective SMS 2003 custom reports that provide the desired data.

Appendix A: SMS 2003 SQL Server View Reference

Provides information about each of the SMS SQL Server views, what type of data each view contains, and how they can be joined to other views to retrieve desired data.

Appendix B: SMS SQL View Sample Queries

Provides sample queries that demonstrate how SMS SQL Server views can be joined to other views when creating reports.

Appendix C: SMS Provider WMI Schema Reference

Provides an overview of the SMS WMI Provider schema and information about the WMI namespaces and classes used by SMS.

Appendix D: SQL Server Reference

Provides information about common SQL query statements and the query design tools available in SQL Server.

Appendix E: SMS 2003 Custom Report Reference

Provides real-world scenarios for creating, cloning and modifying SMS reports, and information about the construct of simple and complex predefined reports.