The below SQL query can be used to pull the details of all devices along with Device type (Laptop, Desktop , Mini PC & Surface devices) directly from Configuration Manager database. This query can be used to create SCCM SSRS report as well.
To run this SQL query, launch SQL Server Management Studio, open a new query and copy and paste below query 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',
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%'
- 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
Subscribe to Techuisitive Newsletter
Be the first to know about our new blog posts. Get our newsletters directly in your inbox and stay up to date about Modern Desktop Management technologies & news.