In this tutorial, you’ll learn how to query and export SQL views in SCCM to an Excel file (.csv).
According to Microsoft, a SQL Server view is a virtual table whose contents are based on the result of a SQL query. A view consists of a set of named columns and rows of data.
If you are asked to create a custom report in SCCM, an understanding of the SQL Server views helps. Once you are aware of the information that is stored in a SQL view, you can write queries to efficiently retrieve the information from the database.
Also Read: Multiple ways to export SCCM Device Collection
Benefits of using the SQL views
Querying the views directly can be significantly faster than using WMI and WQL, which receive a query request and then query the SQL Server database for the information. By using SQL Server views directly, you eliminate the intermediate step and gain a faster path to the data.
To effectively create reports with the required output, it’s essential to know what data each of the Configuration Manager SQLÂ Server views contains and how the views are related to each other.
We have seen several versions of ConfigMgr, and Microsoft usually includes new SQL views with most releases. The current branch documentation includes information about the new SQL views added to a specific version of Configuration Manager.
See Also: How to Backup SCCM Server: A Complete Guide
Export SQL Views in SCCM
I frequently receive emails asking, “Where can I find a schema of the various tables/views from the SCCM SQL DB to facilitate my SQL queries?” Well, you can use a simple SQL query to retrieve the SQL views from the SCCM DB and export them to a .csv file.
Use the below steps to query and export all the SQL views from the SCCM database:
- Launch the SQL Server Management Studio.
- Connect to the database engine with your credentials.
- Right-click SCCM Database and select New Query.
- In the query window, enter the below query and click Execute.
Select distinct table_name,COLUMN_NAME From Information_Schema.columns
where table_name LIKE ('v_%')
Order by table_name
Running the above query lists all the SQL views from the SCCM DB. In the output, you see the table name that starts with v_ which is nothing but the SQL views and the column name.
To export all the SQL views, right-click on the output and select Save results as. On the Save grid results window, choose the folder where you want to save the file. The SQL DB views file is exported and saved to a .csv file.
The SQL views .csv file can be opened in an Excel application. In the below screenshot, we see the list of all the exported SQL views from the Configuration Manager database.
SQL View Categories in SCCM
Configuration Manager includes the following SQL view categories:
- Application Management Views in Configuration Manager
- Client Deployment Views in Configuration Manager
- Client Status Views in Configuration Manager
- Collection Views in Configuration Manager
- Compliance Settings Views in Configuration Manager
- Content Management Views in Configuration Manager
- Discovery Views in Configuration Manager
- Endpoint Protection Views in Configuration Manager
- Inventory Views in Configuration Manager
- Migration Views in Configuration Manager
- Mobile Device Management Views in Configuration Manager
- Network Access Protection Views in Configuration Manager
- Operating System Deployment Views in Configuration Manager
- Out of Band Management Views in Configuration Manager
- Power Management Views in Configuration Manager
- Query Views in Configuration Manager
- Reporting Views in Configuration Manager
- Schema Views in Configuration Manager
- Security Views in Configuration Manager
- Site Administration Views in Configuration Manager
- Software Metering Views in Configuration Manager
- Software Updates Views in Configuration Manager
- Status and Alert Views in Configuration Manager
- Wake On LAN Views in Configuration Manager
View Schema Views in Configuration Manager
The v_SchemaViews lists all the SQL views and SQL view types in the view schema family. You can use the following query that uses the v_SchemaViews view to retrieve a list of all the view schema family views and their associated view categories.
SELECT Type, ViewName
FROM v_SchemaViews
ORDER BY Type, ViewName
The v_ReportViewSchema lists all the Configuration Manager SQL views in the view schema family and the column names for each view.
What you can do further is join the v_SchemaViews and v_ReportViewSchema views to group all the views in the Configuration Manager view schema family.
Schema Information views in Configuration Manager
The following table lists all the schema information views.
| Schema View | Description |
|---|---|
| v_SchemaViews | Shows all the views in the view schema family |
| v_ReportViewSchema | Shows list of classes, properties of each class |
| v_ResourceMap | Displays all the resource type views. |
| v_ResourceAttributeMap | Lists attributes for each resource type |
| v_GroupMap | Shows all the inventory groups for each inventory architecture |
| v_GroupAttributeMap | Lists attributes for each inventory group |
Hardware Inventory Schema Views
The hardware inventory views contain information about the computer hardware scanned on Configuration Manager client computers.
If you have enabled hardware inventory in SCCM, a set of views is created by default. Additional views are added to the DB after creating classes by using the hardware inventory classes dialog box, accessible from client settings.
Run the below SQL query to generate the hardware inventory view schema based on the specific 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
To export the hardware inventory schema views to a .csv file, right-click the query output and choose Save Results as. Pick a location, specify a file name, and select save.
Software Inventory Schema Views
The below SQL query by Microsoft lets you generate the software inventory view schema by product name for the specific device collection in SCCM. Before running the query, you must specify the collection ID to view the software inventory schema views.
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 = 'SMSDM003'
GROUP BY PRD.ProductName, PRD.ProductVersion
ORDER BY PRD.ProductName
To export the software inventory schema views to a .csv file, right-click the query output and choose Save Results as. Pick a location, specify a file name, and save it.
There are many other SQL server views in Configuration Manager, and we have covered only a few examples in this guide. We recommend going through the following article to find out the list of all the SQL views included in the ConfigMgr: https://learn.microsoft.com/en-us/mem/configmgr/develop/core/understand/sqlviews/sql-server-views-configuration-manager.
