The Configuration Manager 2007 schema views provide information about the schema that can be used when creating reports, as well as the discovery schema views, inventory schema views, and a desired configuration management schema view.

View Schema Views

The Configuration Manager 2007 view schema views can be joined together and used to retrieve specific data. They provide information about all of the views in a Configuration Manager 2007 site that are in the Configuration Manager view schema family. The Configuration Manager 2007 view schema views are described in the following table.

View Schema View Description

v_SchemaViews

Lists all the Configuration Manager SQL views and SQL view types in the view schema family.

The view can be joined to the v_ReportViewSchema view by using the ViewName column.

v_ReportViewSchema

Lists all the Configuration Manager SQL views in the view schema family and the column names for each view.

The view can be joined to the v_ReportViewSchema view by using the ViewName column.

The following query uses the v_SchemaViews view to retrieve a list of all the view schema family views and their associated view category:

SELECT Type, ViewName

FROM v_SchemaViews

ORDER BY Type, ViewName

Each of the Configuration Manager views has multiple columns, and determining which of these columns to use when building queries for the desired data is sometimes difficult. The following query joins the v_SchemaViews and v_ReportViewSchema views to list all of the views in the Configuration Manager view schema family, each of the columns within each view, and the view category:

SELECT RVS.ViewName, RVS.ViewColumnName, SV.Type

FROM v_SchemaViews as SV INNER JOIN v_ReportViewSchema as RVS

  ON SV.ViewName = RVS.ViewName

ORDER BY SV.Type, RVS.ViewName, RVS.ViewColumnName

The output from this query and the information throughout this document provide information to help you use the correct view and view column to build queries for effective reporting.

Discovery Schema Views

The discovery schema views provide information about all resources in a Configuration Manager 2007 site and are described in the following table. The two resource schema information views are v_ResourceMap and v_ResourceAttributeMap. The v_ResourceMap view contains a list of all the resource types for discovered data. By default, Configuration Manager has the Unknown System, User Group, User, and System resource types, each of which has its own resource type number and individual view. The view can be joined to other views by using the ResourceType column. The following table represents the default data contained in the v_ResourceMap view.

ResourceType DisplayName ResourceClassName

2

Unknown System

v_R_UnknownSystem

3

User Group

v_R_UserGroup

4

User

v_R_User

5

System

v_R_System

The v_ResourceAttributeMap view contains all of the attributes that will be discovered for each of the resource types, such as NetBIOS name, operating system, user name, user group name, domain name, and so forth. The v_ResourceAttributeMap view can be joined to other views by using the ResourceType column. For more information about the discovery views, see Discovery Views.

Hardware Inventory Schema Views

The hardware inventory schema is important to understand when creating queries for Configuration Manager reports that contain hardware inventory information. Most of the client data within Configuration Manager is contained in one of the two hardware inventory schema views: v_GroupMap and v_GroupAttributeMap. The v_GroupMap view contains a list of all the hardware inventory groups and the associated view for each of the groups. The v_GroupAttributeMap view contains all of the attributes that are inventoried for each of the groups. Both views can be joined together by using the GroupID column and joined to the v_ResourceMap discovery schema view by using the ResourceType column.

Because hardware inventory can be configured through enabling or disabling classes in the SMS_DEF.MOF, one Configuration Manager site's SQL database might have different hardware inventory views and schema when compared to another site. The following query joins the v_GroupMap and v_GroupAttributeMap views to generate the hardware inventory view schema, based on the specific SMS_DEF.MOF file settings for the site:

SELECT DISTINCT GM.DisplayName, GM.InvClassName,

  GM.InvHistoryClassName, GAM.AttributeName,

  GAM.ColumnName, GM.MIFClass

FROM v_GroupMap GM INNER JOIN v_GroupAttributeMap GAM

  ON GM.GroupID = GAM.GroupID

For more information about the hardware inventory views, see Hardware Inventory Views.

Software Inventory View Schema

There is not a specific software inventory schema view, but the following query joins the v_GS_SoftwareProduct and v_FullCollectionMembership software inventory views to generate the software inventory view schema by product name for the All Systems collection:

SELECT MIN(PRD.ProductID) AS ProductID, PRD.ProductName,

PRD.ProductVersion, COUNT(DISTINCT PRD.ResourceID) AS 'Count'

FROM v_GS_SoftwareProduct PRD INNER JOIN v_FullCollectionMembership FCM

ON PRD.ResourceID = FCM.ResourceID

WHERE FCM.CollectionID = 'SMS00001'

GROUP BY PRD.ProductName, PRD.ProductVersion

ORDER BY PRD.ProductName

For more information about the software inventory views, see Software Inventory Views.

Desired Configuration Management Schema View

There is one desired configuration management schema view, v_CIRelationTypeMapping, that lists the configuration item elements, such as Baselines and Software Updates, the relation type value, and description for the relation type. The view can be joined to other desired configuration management views by using the RelationType column. For more information about the desired configuration management views, see Desired Configuration Management Views.

See Also