Using SMS 2003 SQL Views to Create Custom Reports
Microsoft Systems Management Server (SMS) 2003 Reporting uses Microsoft SQL Server views to provide access to data from the tables in the SMS site SQL database and to offer an efficient reporting option. The SMS site SQL database contains a large collection of information about the network, computers, users, user groups, and many other components of the computing environment. This database also contains objects that represent SMS items such as advertisements, packages, queries, reports, and status messages.
SMS administrators need to understand the different categories of the views, how they are related to Windows Management Instrumentation (WMI), and how they can be used to create effective reports.
This document assumes that you have a basic understanding of SMS 2003 and SQL, that you have an active SMS 2003 infrastructure in place, and that you have a basic understanding of SMS 2003 reports. For more information about creating reports in SMS 2003, see Chapter 11: Creating Reports in the Systems Management Server 2003 Operations Guide.
This document provides an overview of the WMI and SMS SQL view schemas, an overview of typical SQL statements that are used when creating reports, an overview of the built-in reports and associated components for creating reports, a demonstration of how the views can be joined to other views to retrieve the desired data, and detailed information about the SMS views and view categories.
Provides an overview of SQL Server views and the types of views created and used by SMS.
Provides an overview of reports in SMS 2003 and information about creating custom reports.
Appendix A: SMS 2003 SQL Server View Reference
Provides information about each 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 information about creating custom SMS reports and the construct of simple and complex predefined reports.