SCCM Device collections

by lionardo

Windows 7 workstations query

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 
where 
SMS_R_System.OperatingSystemNameandVersion like "%Workstation 6.1%"
or 
SMS_R_System.OperatingSystemNameandVersion like "%Windows 7%"

Windows 10 workstations query

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 
where 
SMS_R_System.OperatingSystemNameandVersion like "%Workstation%" 
and 
SMS_R_System.OperatingSystemNameandVersion like "%10.0%"

Windows 2016 server query

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 
where 
SMS_R_System.OperatingSystemNameandVersion like "%Server%" 
and 
SMS_R_System.OperatingSystemNameandVersion like "%10.0%"

Thanks to: https://i-py.com/2016/sccm-server-2016/