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
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
- 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
- Disk Space
- Free Disk Space
- Last Logged On User
- User that logged onto the machine the most
- 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
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.
ON [dbo].[v_EmptyGarden_ConfigMgrMachineDetail ] TO [smsschm_users]
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.
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
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