SMS 2003 SQL Server View Schema
To create effective reports, accurate queries based on the appropriate Microsoft Systems Management Server (SMS) views need to be used to retrieve the desired data and display the expected output. Knowing the SMS 2003 Microsoft SQL Server view schema is an important first step in learning how to create these reports.
The SMS SQL Server view schema maps almost exactly to the SMS Provider WMI schema, which is used when building WQL-based queries and collections in the SMS Administrator console. However, querying the views directly can be much faster than using WMI and WQL, which receive a query request and in turn query SQL for the information. By using views directly, you eliminate the intermediate step and gain a faster path to the data. For more information about the SMS Provider WMI schema, see Appendix C: SMS Provider WMI Schema Reference.
The view and view column names used by SMS are designed to be as close to the SMS Provider WMI schema as possible. However, because the view and view column names must be valid SQL identifiers, there are some discrepancies between WMI and SQL names. In most cases, the following general rules can be applied to convert a WMI class name to its corresponding SMS view:
For the start of the view name, v_ replaces SMS_.
If a view name is longer than 30 characters, it is truncated.
WMI property names are the same in the views for non-inventory or discovery classes.
For example, if you wanted to convert the WMI class SMS_Advertisement to the associated SQL view, you would remove the **SMS_**and replace it with v_, resulting in the appropriate view name of v_Advertisement.
The following list provides the SMS view categories and a brief description for each:
Discovery views (resource data) consist of system resource objects (systems, users, and user groups), which include any resources that were discovered on the network. The type of information that SMS gathers depends on the type of resource that is discovered. For example, some resources, such as printers, might not have the operating system name and version property.
Inventory views contain hardware and software inventory information about the clients in the SMS hierarchy. SMS collects inventory data when you enable the Hardware Inventory Client Agent or the Software Inventory Client Agent.
Status views contain information about status messages. For example, component name, message ID, module name, message type, severity, time, site code, and computer name. Status messages are generated by SMS components and represent the flow of activity within an SMS site and hierarchy. Status messages provide information that you can use to assess the health of your SMS system.
Collection views contain data about each resource that is a member of the collection. Each collection in the SMS Administrator console is represented by its own view. In addition, several other views contain general information about the collections in an SMS site hierarchy.
Query view contains information about all of the queries in the SMS hierarchy. There is only one query view in SMS, named v_Query. The query ID, query name, comment, target class name, and the collection ID to which the query is limited, if applicable, are all listed.
Report views contain information such as report names, report IDs, dashboard ID, which reports are part of a dashboard, which column to use when navigating to linked reports, the SQL query for the report, and so forth. There is no official report view classification, but because several views contain information directly related to SMS reporting, they are grouped together and referred to as the report views.
Security views contain information about the permissions that are granted to users and user groups to perform operations on secured SMS object classes and instances, such as collections, packages, and reports.
Site views contain information such as site code, SMS version, SMS provider location, site server name, site system names, site boundary information, and so forth. There is no official site view classification, but because several views contain information directly related to sites in the SMS hierarchy, they are grouped together and referred to as the site views.
Software distribution views contain information such as package ID, advertisement ID, collection ID, time package was presented, time that the advertisement expires, distribution point locations, and so forth. There is no official software distribution view classification, but because several views contain information directly related to the distribution process, they are grouped together in this section and referred to as the software distribution views.
Software metering views contain information such as the software metering rules that are created in the SMS hierarchy, which files to meter, the products in which the files belong, the users that have used the metered files, and so forth. There is no official software metering view classification, but because several views contain information directly related to software metering in SMS, they are grouped together and referred to as the software metering views.
Software update views contain information such as the resource ID for all systems where software updates are applicable, software update ID, article numbers associated with the software update, path to more information for the software update, inventory scan tool name, and so forth. There is no official software update view classification, but because several views contain information directly related to software updates, they are grouped together and referred to as the software update views.