Configuration Manager 2007 SQL Server Views
Applies To: System Center Configuration Manager 2007, System Center Configuration Manager 2007 R2, System Center Configuration Manager 2007 R3, System Center Configuration Manager 2007 SP1, System Center Configuration Manager 2007 SP2
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:
- Views against static (unchanging) tables.
- Views that use data from tables with a dynamic (changing) schema.
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.