Guide, Microsoft, Query, SCCM, SQL

SCCM – SQL Query for all Laptops

Last updated on September 1st, 2019 at 08:56 pm

Read Time:2 Minute, 17 Second

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 to create 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

This 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:

SYS.Name0 as 'Computer Name',
SYS.SystemRole0 as 'System Role',
OS.Caption0 as 'Operating System',
ENC.ChassisTypes0 as 'Chassis Type'
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
ChassisTypes0 in ('8','9','10','21')
ORDER BY ChassisTypes0

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


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.

Click to rate this post!
[Total: 0 Average: 0]
Avatar for Andrew Armstrong

About Post Author

Andrew Armstrong

Founder of TechyGeeksHome and Head Editor for over 15 years! IT expert in multiple areas for over 23 years. Sharing experience and knowledge whenever possible! Making IT Happen.

Leave us a message...

This site uses Akismet to reduce spam. Learn how your comment data is processed.