A Microsoft SQL Server view is a virtual table whose contents are defined by a query. A view consists of a set of named columns and rows of data. However, a view does not exist as a stored set of data values in a database. The rows and columns of data come from tables or other SQL views referenced in the query that defines the view and are produced dynamically when the query is run. The query that defines the view can be from one or more tables or from other views in one or more databases. Distributed queries (queries that access data from multiple data sources) can also be used to define views that pull data from multiple heterogeneous sources (data stored in multiple formats), such as data stored in a SQL Server database, a text file, or a Microsoft Excel spreadsheet.

During setup, Configuration Manager 2007 creates the following SQL view types:

For a dynamic schema, Setup creates a number of SQL Server stored procedures that create the views. These stored procedures are rerun by Configuration Manager to refresh the views when the schema of underlying tables changes. Collection evaluation, discovery, and inventory data are examples of data for which new tables or new properties in existing tables might appear during the operation of a Configuration Manager site.

Configuration Manager 2007 SQL View Schema

To create effective reports, accurate SQL statements based on the appropriate Configuration Manager views need to be used to retrieve the desired data and display the expected output. Knowing the Configuration Manager Microsoft SQL Server view schema is an important first step in learning how to create these reports.

Much of the Configuration Manager SQL view schema maps to the SMS Provider WMI schema, which is used when building WQL-based queries and collections in the Configuration Manager console. However, querying the views directly can be much faster than using WMI and WQL, which receive a query request and in turn query the SQL database for the information. By using SQL 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 SMS Provider WMI Schema Reference.

Configuration Manager SQL View Categories

To effectively create reports with the desired Configuration Manager output, it is essential to know what data each of the Configuration Manager 2007 SQL Server views contains and how the views are related to each other. The following topics in this section provide detailed information about each of the view categories, what kind of data each of the views contains, and what columns can be used to JOIN views in SQL statements.

Schema Views

Schema views provide information about all of the views in a Configuration Manager 2007 site that are in the Configuration Manager view schema family, information about all resources in a Configuration Manager site, and information about the hardware inventory that is retrieved.
Site Views

Site views contain information specific to the Configuration Manager site, such as site code, site version, provider location, site server name, site system names, site boundary information, and so forth.
Status Views

Status views contain information about status and state messages, as well as status summarizers. Status messages report information about Configuration Manager 2007 component behavior and data flow and are categorized by severity and type. State messages are sent by Configuration Manager 2007 clients to site systems based on important changes of state. Status summarizers produce summaries of the status or state messages and provide a snapshot of status and health of site systems, components, software updates compliance, and so on.
Client Deployment Views

The client deployment views contain information about the state of the deployment of Configuration Manager 2007 client computers and devices.
Client Health Views

Client health views contain information about the health of Configuration Manager 2007 client computers, such as when the client last scanned for hardware and software inventory, the last policy request, the result of the last ping request, and so on.
Note
The client health views were introduced in Configuration Manager 2007 Service Pack 1.
Collection Views

Collection views contain information about each resource that is a member of the collection. Each collection in the Configuration Manager console is represented by its own view. In addition, several other views contain general information about the collections in a Configuration Manager site hierarchy.
Software Distribution Views

Software distribution views contain information about the software distributions for the Configuration Manager site, as well as the software distribution components, advertisements, packages, and programs.
Software Updates Views

Software updates views contain information about the software updates metadata, update lists, software update bundles, and so on.
Operating System Deployment Views

Operating system deployment views contain information about boot image packages, computer association state migrations, operating system image packages, task sequences, driver packages, and so on.
Asset Intelligence Views

Asset intelligence views contain inventory information collected through the asset intelligence component, which was introduced in Configuration Manager 2007 Service Pack 1.
Software Metering Views

Software metering views contain information about the software metering rules that are created in the Configuration Manager hierarchy, which files to meter, the products in which the files belong, the users that have used the metered files, and so forth.
Reporting Views

Report views contain information about the reports and dashboards in a site, 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 statement for the report, and so forth.
Desired Configuration Management Views

Desired configuration management views contain information about configuration items, bundled configuration items, configuration item content, resources that have been assigned a configuration item, localized configuration item properties, and so on.
Query Views

The query view contains information about all of the queries in the Configuration Manager hierarchy. There is only one query view in Configuration Manager, named v_Query.
Mobile Device Management Views

Mobile device management views contain information about the mobile device configuration items and configuration packages.
Network Access Protection Views

Network Access Protection (NAP) views contain information about clients with the NAP client installed, client restriction history, the NAP error codes, and summary information about the NAP systems and NAP restrictions.
Discovery Views

Discovery views (resource data) contain information about system resource objects (unknown systems, systems, users, and user groups) that were discovered on the network by using one or more discovery methods.
Inventory Views

Inventory views contain hardware and software inventory information collected from the clients in the Configuration Manager 2007 hierarchy. Configuration Manager collects hardware inventory when the Hardware Inventory Client Agent is enabled and software inventory when the Software Inventory Client Agent is enabled.
Wake On LAN Views

Wake On LAN views contain information about the objects that have Wake On LAN enabled, as well as the clients that are Wake On LAN enabled, and clients that have been targeted with an advertisement or deployment with Wake On LAN enabled.
Security Views

Security views contain information about the permissions that are granted to users and user groups to perform operations on secured Configuration Manager object classes and instances, such as collections, deployments, packages, and reports.

See Also