In this post I will be showing you how to create various SCCM windows device collection. I will show various queries to be used for Windows 10 workstations, Windows 7, Windows servers etc.

I am here sharing multiple queries used for Workstations and Servers, and also specific version of Workstation or Server. I hope you will find this post useful

How to create Query Based Configuration Manager Device collection

To create device based collection based upon specific Operating System type, we need to create Query Rule base collection. Using Query based rule, we can specify various conditions to be used for Workstation based query or Server based query.

Create SCCM device collection for all workstations

Launch Configuration Manager Admin Console¸ navigate to \Assets and Compliance\Overview\Device Collections, right click Device Collections > Create Device Collection

On Create Device Collection Wizard page, provide name and choose Limiting collection as “All Systems”, click Next.

Create Device Collection Wizard

On Membership Rules page, Click Add Rule > Query Rule.

Query Rule

Provide the name of the query, lets say “Workstations”, while Resource class “System Resource” selected click on Edit Query Statement and then click on Show Query Language.

Show Query Language

We will be under Query Language page where we can type the WQL query to fetch the records from SCCM Database. Default query shown will always be “Select * from SMS_R_System”

Let’s change it, and use following query for All workstations

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 OperatingSystemNameandVersion like '%workstation%'
SCCM Query all workstations

Note: Configuration Manager comes with the Live Preview button shown  with green icon to run the query live, this comes very handy to see and verify the results.

Click OK twice to return to Membership Rules page, click Next and complete the wizard.

Membership Rules

Create Device collection for all Windows 10 workstations

To create SCCM Windows 10 Device collection, we will be using the same query and making a small change ‘%workstation%10%’

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 OperatingSystemNameandVersion like '%workstation%10%'

Create Device Collection for all Windows 10 Workstations 20H2

Now we are talking about specific version of Windows ie. 20H2, hence we need more information about the build version / Build number of OS. Hence, we modify the query and will use following:

To create Windows 10 20H2 device collection, use following 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 OperatingSystemNameandVersion like '%workstation%10%' and build = '10.0.19042'

Create Device Collection for all Windows 10 Workstations 21H1

To create Windows 10 21H1 device collection, use following 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 OperatingSystemNameandVersion like '%workstation%10%' and build = '10.0.19043'

Create Device Collection for all Windows 10 Workstations 21H2

To create Windows 10 21H2 device collection, use following 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 OperatingSystemNameandVersion like '%workstation%10%' and build = '10.0.19044'

Create Device collection for other Windows 10 Build versions

Using this technique, we can create collection of any specific version of Windows 10, such as:

Windows 10 Build versions starting with 10.0:

For Collection of Windows 10 1909 – Replace build with 10.0.18363
For Collection of Windows 10 1903 – Replace build with 10.0.18362
For Collection of Windows 10 2004 – Replace build with 10.0.19041
For Collection of Windows 10 20H2 – Replace build with 10.0.19042
For Collection of Windows 10 21H1 – Replace build with 10.0.19043
For Collection of Windows 10 21H2 – Replace build with 10.0.19044

Create Device collection to exclude specific Build version of Windows 10

Let’s say you want to exclude specific version of Windows 10 version, for the purpose of In-place upgrade / feature update requirement. This query could be quite handy to be used (example: excluding Windows 10 1507 & 1607 build versions):

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 OperatingSystemNameandVersion like '%workstation%10%' and (build !='10.0.10240' and build !=’10.0.14393’)

Note:!=’ means Not Equal to. WQL query is slightly different than SQL query hence not like doesn’t work in WQL.

Create Device collection for all Windows Server

To create Windows Server collection, the same technique we are going to use here, OperatingSystemNameandVersion will be here Server. Use following query (WQL query for all windows server):

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 OperatingSystemNameandVersion like '%Server%'

Create Device collection for Windows Server 2016

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 OperatingSystemNameandVersion like '%Server%' and build = '10.0.17763'

Create Device collection for Windows Server 2019

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 OperatingSystemNameandVersion like '%Server%' and build = '10.0.17763'

Create Device collection for Windows Server 2022

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 OperatingSystemNameandVersion like '%Server%' and build = '10.0.20348'

Conclusion


For any other Windows Server query, you just need to get the build number details. This is a handy Wikipedia page for Windows Server version history

For Windows Workstations, you can use Windows 10 version history