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/