SCCM SQL Query to list Desktop & Laptop Devices

SCCM SQL queries are great way to pull out information from SCCM database directly. As a SCCM administrator, you may encounter a situation where custom data is required and you are unable to get those information from built-in reports or WQL queries. The SQL query can be used to pull out any information from SCCM and an admin familiar with SQL can even create complex queries.

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',
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