Microsoft System Center Configuration Manager 2007 uses Windows Management Instrumentation (WMI) to manage its objects. Any managed object, such as a disk drive or a collection of computers, can be represented by an instance of a Configuration Manager class. Configuration Manager also includes classes that represent features, such as software distribution or software updates. Collectively, these Configuration Manager classes comprise the SMS Provider WMI schema.
Configuration Manager 2007 uses a SQL Server database to store managed object data. Both SQL Server and WMI can be used to view Configuration Manager managed data. A new query created in the Queries or Collections node of the Configuration Manager console uses a WMI Query Language (WQL) query to request the Configuration Manager object data from the SMS Provider WMI Schema, which in turn retrieves the data from the site database. When creating a custom report in Configuration Manager, report SQL statements retrieve the Configuration Manager object data from SQL views in the site database, which in turn retrieve the data from one or more SQL views or tables.
SQL View and SMS Provider WMI Schema Relationship
Many of the SQL view and view column names used by Configuration Manager 2007 are designed to be as close to the SMS Provider WMI schema as possible. Other SQL views retrieve data from other views or from multiple tables or views, and there is no direct mapping to the SMS Provider WMI schema. Also, because the SQL view and view column names must be valid SQL identifiers, there are some discrepancies between WMI and SQL names when there is a mapping. In most cases, the following general rules can be applied to convert a WMI class name to its corresponding SQL 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 noninventory
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 SQL view is equivalent to the associated WMI class, and a row within the SQL view is the equivalent of a WMI instance. The following example queries show the relationship between the v_Advertisement SQL view and the associated SMS_Advertisement WMI class.
SQL View Query
SELECT AdvertisementID, PackageID, CollectionID,
SourceSite
FROM v_Advertisement
This query returns the following rows.
| AdvertisementID | PackageID | CollectionID | SourceSite |
|---|---|---|---|
|
MCM20000 |
MCM00003 |
SMS00001 |
MCM |
|
MCM20001 |
MCM00002 |
SMS00004 |
MCM |
|
MCM20002 |
MCM00006 |
SMS00001 |
MCM |
WQL Query
SELECT AdvertisementID, PackageID, CollectionID,
SourceSite
FROM SMS_Advertisement
This query returns the following rows.
| AdvertisementID | PackageID | CollectionID | SourceSite |
|---|---|---|---|
|
MCM20000 |
MCM00003 |
SMS00001 |
MCM |
|
MCM20001 |
MCM00002 |
SMS00004 |
MCM |
|
MCM20002 |
MCM00006 |
SMS00001 |
MCM |
Configuration Manager SQL View Design
When there is no direct mapping for a SQL view and the SMS Provider WMI schema class and you want to determine where the data in the SQL view comes from, you can look at the SQL view design. This helps determine whether a SQL view is retrieving data from a single SQL table, from another SQL view, or from more than one table or view. When the SQL view retrieves data from more than one table or view, the SQL view will most likely map to more than one class in the SMS Provider WMI schema. Use the following procedure to display the SQL view design.
 Caution |
|---|
| Modifying the design of a Configuration Manager SQL view can result in product and reporting errors. |
-
Start Microsoft SQL Server Management Studio on the server that hosts the Configuration Manager site database.
-
Navigate to Computer Name \ Databases \ Configuration Manager database name \ Views.
-
Right-click the SQL view in which you want to see the design, and click Design. The SQL pane displays the SQL statement. Look at the table or view name just after the FROM clause to figure out where the view is retrieving its data. When the view retrieves data from more than one source, the table or views will use JOINS.