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
- Create SCCM device collection for all workstations
- Create Device collection for all Windows 10 workstations
- Create Device Collection for all Windows 10 Workstations 20H2
- Create Device Collection for all Windows 10 Workstations 21H1
- Create Device Collection for all Windows 10 Workstations 21H2
- Create Device collection for other Windows 10 Build versions
- Create Device collection to exclude specific Build version of Windows 10
- Create Device collection for all Windows Server
- Conclusion
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.
On Membership Rules page, Click Add 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.
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%'
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.
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