Estimated reading time: 3 minutes
If you are using Configuration Manager, you will be aware that you can create a number of custom reports using the SCCM SQL data and SSRS to produce the reports.
The best way to start creating a custom report is to ensure that you get your SQL query correct first. We have written hundreds of various queries over the years for our own use and by request through our website. This guide will show you how to pull data from the Configuration Manager database and give you the following information:
- Machine Name
- System Role
- Operating System
- Chassis Type
These is just a limited few options, although by using our query you can change these to a * and then pick and choose what you want from the output, but for this guide, these are what we will use.
The SQL Query
By running our first query, it will bring back a large amount of data (depending on your environment size of course) – it will basically show the options for every machine in your Configuration Manager database.
To do this, run the following query:
SELECT
SYS.Name0 as 'Computer Name',
SYS.SystemRole0 as 'System Role',
OS.Caption0 as 'Operating System',
ENC.ChassisTypes0 as 'Chassis Type'
FROM
v_GS_System SYS join v_GS_Operating_System OS on SYS.ResourceID = OS.ResourceID
join v_GS_SYSTEM_ENCLOSURE ENC on ENC.ResourceID = SYS.ResourceID
WHERE
ChassisTypes0 in ('8','9','10','21')
ORDER BY ChassisTypes0Chassis Type is just a number, what does it mean?
When you run this query, it will bring back the results and under the ChassisTypes0 column it will show either 8, 9, 10 or 21. These are some of the values that SCCM uses to show what type of machine it is. So, in this query, the values are for laptops.
For a full breakdown of all the codes that SCCM uses, just click here.
What if I want to amend the query?
For more information on how to change and use SQL queries for Configuration Manager, you can take a look at our in-depth guide by clicking here.
I need help with setting up the SSRS report.
If you need any help in using these queries to create a SSRS report, we have written a step-by-step guide on how to do that to, just click here to take a look.
More Queries
Our full range of SQL and WQL Collection queries are available here.
Feedback
If you have any questions or feedback about this post, or if you would like us to create any queries for you, please go ahead and leave us a message below in the comments section and we will get back to you as quick as we can.
Share this content:
