Understanding SCCM SQL Views

The Configuration Manager 2012 / Current Branch come with many built in reports which are sufficient for day to day requirements. However, you still have to deal with SQL query to pull custom details directly from SCCM database based on requirement. As an SCCM administrator you must have good understanding of SQL query in order order to pull these information from SCCM database directly. One of the key requirement here is to have understanding of SCCM DB schema. Once you know which information are stored in which SQL view, you can easily write a SQL query to pull out information.

Know SCCM SQL Views:

The SCCM SQL View v_SchemaViews contain the details of all views. You can use below SQL query to get a list of all views.

Select * from v_SchemaViews

Since, SCCM creates one separate view for each collection, the list will be quite long. You can filter the result by excluding Collection views from query.

Select * from v_SchemaViews where Type <> ‘Collection’

Details of SCCM Views and Their Naming Conventions:

These views can be grouped into following.

Resource Views:

Resource views represent discovery data, which include any resources that were discovers on the network. These views name start with V_R.

The four main discovery views are , v_R_System for system resources, v_R_User for user resources, v_R_UserGroup for user group resources, and v_R_UnknownSystem for unknown systems.

Resource Array Views:

Resource Array views contain data where there can be more than one value for a resource, such as IP Address or User Organizational Unit (OU) name. The name of most of these views start with V_RA.

Examples:

V_RA_System_IPAddresess : List the IP addresses of discovered system resource

V_RA_System_SystemGroupName : List all system resources which are in an associated Active Directory group.

You can find more details about Discovery Views (Resource & Resource Array) here.

Inventory Views:

Inventory views contain current inventory data for hardware, software ,clients, files, products, and so forth in the Configuration Manager hierarchy. These views are named as V_GS.

Select * from v_SchemaViews where type = ‘inventory’

Inventory History Views:

Inventory History views contain inventory history data for hardware, software ,clients, files, products, and so forth in the Configuration Manager hierarchy. These views are named as V_HS_.

Other Views

V_Collection : The V_Collection SQL view display the properties of site’s collections.

v_Package : The v_Package SQL view display the properties of site’s packages.

v_Advertisements: The v_Advertisements SQL view display the properties of Advertisements.

v_Reports: Display the properties of Reports

You can find complete details of all views in Configuration Manager 2012 here.

Subscribe to Techuisitive Newsletter

Be the first to know about our new blog posts. Get our newsletters directly in your inbox and stay up to date about Modern Desktop Management technologies & news.