SCCM Queries for Server 2016, 2012, 2008 and Windows 10

SCCM Queries for Server 2016 and Windows 10

For Windows 2016 Servers

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%"

For Windows 2012 R2 Servers

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 "%6.3%"

For Windows 2008 R2 Servers

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 "%6.1%"

Query to Extract All Servers

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.Name like "US%"
and (SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT Server%"
or
SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT Advanced Server%"
or SMS_R_System.OperatingSystemNameandVersion like "%Server%")

For Windows 10 Workstation

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%"