How to Export SQL Views in SCCM: A Comprehensive Guide

by

in

A detailed tutorial on how to query and export SQL views that can be used to create reports in Configuration Manager.

How to Export SQL Views in SCCM

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 ReadMultiple ways to export SCCM Device Collection

Table of Contents

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

Query SQL Views in SCCM
Query SQL Views in SCCM

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.

Query and Export SQL Views in SCCM
Query and Export SQL Views in SCCM

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.

Query and Export SQL Views in SCCM
Query and Export SQL Views in SCCM

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
Query v_SchemaViews in Configuration Manager
Query v_SchemaViews in Configuration Manager

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.

Join the v_SchemaViews and v_ReportViewSchema views
Join the v_SchemaViews and v_ReportViewSchema views

Schema Information views in Configuration Manager

The following table lists all the schema information views.

https://7c23219c5676f8e1b7981dbaefb1036c.safeframe.googlesyndication.com/safeframe/1-0-40/html/container.html
Schema ViewDescription
v_SchemaViewsShows all the views in the view schema family
v_ReportViewSchemaShows list of classes, properties of each class
v_ResourceMapDisplays all the resource type views.
v_ResourceAttributeMapLists attributes for each resource type
v_GroupMapShows all the inventory groups for each inventory architecture
v_GroupAttributeMapLists 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.

https://7c23219c5676f8e1b7981dbaefb1036c.safeframe.googlesyndication.com/safeframe/1-0-40/html/container.html

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
https://7c23219c5676f8e1b7981dbaefb1036c.safeframe.googlesyndication.com/safeframe/1-0-40/html/container.html
Hardware Inventory Schema Views
Hardware Inventory Schema Views

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
Software Inventory Schema Views
Software Inventory Schema Views

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.

Sign Up For Newsletter

Join our newsletter to stay updated and receive all the top articles published on the site! Get the latest articles delivered straight to your inbox.

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *