SCCM SQL Query to list Desktop & Laptop Devices

As an SCCM administrator, you often need custom reports that go beyond the built‑in options. One common requirement is distinguishing between desktops, laptops, mini PCs, and Surface devices in your environment. With the right SQL query, you can pull this information directly from the Configuration Manager database and even use it to build SSRS reports. In this guide, we’ll share a ready‑to‑use SQL query that helps you quickly identify device types and client status, making hardware inventory and reporting more efficient

Use the SQL query below to retrieve details of all devices, including their type (Laptop, Desktop, Mini PC, and Surface), directly from the Configuration Manager database. This query can also be leveraged to build a custom SCCM SSRS report.

To run this SQL query, launch SQL Server Management Studio, open a new query, copy and paste the query below, and click on Run.

select r.ResourceID,r.Netbios_Name0 ,bios.SerialNumber0,comp.Model0,comp.Manufacturer0,enc.ChassisTypes0,
case when ch.ClientActiveStatus= '1' then 'Active' when ch.ClientActiveStatus='0' then 'Inactive' END as 'ClientStatus',
case
when enc.ChassisTypes0 in ( '8','10', '11', '12', '14', '18', '21' ) then 'Laptop'
when enc.ChassisTypes0 in( '3', '4', '5', '6', '7', '15', '16' ) then 'Desktop'
when enc.ChassisTypes0 in( '35' ) then 'Mini PC'
when (enc.ChassisTypes0=9 and comp.Model0 like 'Surface%') then 'Microsoft Surface'
end as 'Type'
from v_R_System r
left join v_GS_PC_BIOS bios on bios. ResourceID=r.ResourceID
left join v_CH_ClientSummary ch on ch.ResourceID=r.ResourceID
left join v_GS_SYSTEM_ENCLOSURE enc on enc.ResourceID=r.ResourceID
left join v_GS_COMPUTER_SYSTEM comp on comp.ResourceID=r.ResourceID
where r.Operating_System_Name_and0 like 'Microsoft Windows NT Workstation%'

Related Posts

• SCCM SQL Query to list Desktop & Laptop Devices
• SCCM SQL Query for Windows 10 / 11 Version Summary
• SQL Query to Get Maintenance Window for member of specific collection | SCCM
• Understanding SCCM SQL Views
• SCCM Collection Queries for System Resource (SMS_R_System) Attribute Class