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 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.
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 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_.
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.