Posted on by mteegarden

The one ConfigMgr query to rule them all

Remember that movie with the one ring that the Ewoks had while trying to convince Dr. Theopolis that Spock was still alive and aboard the TARDIS? I can’t remember what kind of Chewbacca ended up rescuing him. Don’t you dare ruin it for me!

Having a go to query to use as a base for your ConfigMgr reports is pretty handy, and that is what this post will cover

 

ConfigMgr Query

 

As you know, ConfigMgr collects a ton of information from WMI and places that information into the ConfigMgr SQL database.

Below is a link to a base ConfigMgr query that I like to use for most of my custom reports.

The query returns the following:

  • ResourceID of the ConfigMgr client machine
  • Machine Name
  • Domain
  • AD Site
  • Organizational Unit
  • Operating System
  • Operating System Name
  • Operating System Service Pack
    • This would be for pre-Windows 10 machines
  • Operating System Version
  • Windows 10 Release Number
  • Operating System Architecture
  • System Manufacturer
  • System Model Number
  • Serial Number
  • Full BIOS Version
  • SMBIOS version
  • BIOS Release Date
  • Total Memory
  • Processor
  • Disk Space
  • Free Disk Space
  • Last Logged On User
  • Top User
    • User that logged onto the machine the most
  • Main User
    • Primary User of the device
    • NOTE: This WILL create duplicate results. More than one user can be associated with a machine
  • SCCM Client
  • SCCM Client Version
  • Last Hardware Scan date
  • Last Software Scan date
  • Last WSUS Scan date
  • WSUS Scan Result
  • WSUS Error ID
  • WSUS Error Code
  • WSUS Hex Error Code

 

https://github.com/TheEmptyGarden/ConfigMgr/blob/master/ConfigMgr-MachineDetail.sql

NOTE: In order to run this query you must replact CM_TP1 with the your ConfigMgr databse name.

 

Wait! The query is returning errors.

 

If you get errors in the query, it is because the views I am using are not in your ConfigMgr database. You will need to extend hardware inventory accordingly.

If the errors are coming from the v_R_System view, then you will need to add active directory attributes to System Discovery

What to do with this query

 

You could just save the .SQL file and use it when needed.

Modify the query to get rid of what you don’t want and add what you do.

You could save it as a view in SQL. However, creating object in the ConfigMgr database is not supported.

You could create another database called CMCustomView and create the view there.

 

How to create a view

 

Open the query in SSMS. Type Create View v_[name] before the select statement. I just always use the company name.

Example: Create View v_EmptyGarden_ConfigMgrMachineDetail as [then the entire select statement]

Then you will need to provide rights for the ConfigMgr reporting point to read the view.

use [CM_TP1]

GO

GRANT
SELECT
ON [dbo].[v_EmptyGarden_ConfigMgrMachineDetail ] TO [smsschm_users]

GO

 

Again, it is not supported to create, modify, or delete objects in the ConfigMgr database.

 

How to create a database for the custom view

 

In SSMS right click databases and create a new one called ‘CM_CustomViews’

Open the query in the above link and create a view in the CM_CustomViews DB

In AD you will want to create a group for reporting users. Example: ConfigMgr_ReportViewers

Give that group rights in the ConfigMgr security section to ‘Run Reports’

I have exported a ConfigMgr security role profile called “Report Viewer” that provides rights to ‘run report’ on all object in ConfigMgr.

https://github.com/TheEmptyGarden/ConfigMgr/blob/master/Report%20Viewer.xml

 

Give that group read rights to the new database you just created.

Open SSMS and select from the newly created view

 

Create a report based on the query

 

Open SSDT. Select FILE > NEW > PROJECT

Select Business Intelligence > Reporting Services > Report Server Project Wizard.

Give your solution a name and a location.

 

The next window that opens after selecting OK will be a Data Source configuration.

Give the datasource a name. Point it to the ConfigMgr DB server and the new DB that you just created from above.

Make the data source shared as well. This will make it easier to continue to build more custom reports in the future.

 

 

Enter the query you want to use for the report

 

Select next twice. Enter a report name and select finish

At this point your report will look something like this:

 

From here you can modify the report to meet your design needs.

I have modified mine to look like this:

Deploy the report

 

In order to deploy the report a connection to the ConfigMgr reporting point must be configured.

Right click the project and select properties

 

Replace the TargetServerURL with the URL of the ConfigMgr Report Server

Select OK

Right click the solution and select deploy

 

Navigate to the ConfigMgr reporting site and notice that there is a new folder that matches the name of the solution.

 

NOTE: You may have to provide permissions to the new custom datasource.

Click on the new folder and run the new report.

 

 

I have copied my custom report solution here: https://github.com/TheEmptyGarden/ConfigMgr/blob/master/TP1%20Custom%20Reports.zip