Lately we have gotten some inquiries asking us to share some “must have” SCCM collections.
We will mostly focus on Device Collections, Listed below are some of our common queries, please let us know if you need anything specific.
Laptop Chassis
Useful to target laptops and notebooks. We usually use this query for deploying specific software, settings, drivers, and baselines.
select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_SYSTEM_ENCLOSURE on SMS_G_System_SYSTEM_ENCLOSURE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes = "8" or SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes = "9" or SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes = "10" or SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes = "11" or SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes = "13" or SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes = "14"
Workstations with ConfigMgr client and a heartbeat
We usually use these kind of collections for reports. A collection which filters out all unmanaged clients and disconnected computers (queries systems with a heartbeat update the last 15 days).
select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_CH_ClientSummary on SMS_G_System_CH_ClientSummary.ResourceId = SMS_R_System.ResourceId where SMS_R_System.Name in (select Name from SMS_R_System where ((DATEDIFF(day, SMS_R_SYSTEM.AgentTime, getdate()) <=15) and AgentName = "SMS_AD_SYSTEM_DISCOVERY_AGENT")) and SMS_R_System.OperatingSystemNameandVersion like "%Workstation%" and SMS_R_System.Name in (select Name from SMS_R_System where ((DATEDIFF(day, SMS_R_SYSTEM.AgentTime, getdate()) <=15) and AgentName = "Heartbeat Discovery")) and SMS_R_System.Active = "1"
This list will be updated continuously.
