How To Create A SCCM Device Collection Based on OS

Jason Barrett Jason Barrett | 25 Mar 2024 | Device Collection

In this article I am going to show you how to create a SCCM device collection based on an Operating System.

These collections can then be used as a limiting device collection, Reports, Compliance settings, Software Updates and much more.

Below I will be using the query rule to populate collections, to jump directly to the queries I use for the collections click here

Contents show

How To Create A SCCM Device Collection Based on OS

To create this device collection we are going to use a collection query rule to populate the collection.

To do this follow these steps

  1. Open the configuration manager console
  2. Go to \Assets and Compliance\Overview\Device Collections
  3. Click on create device collection in the toolbar
    create device collection
  4. Give the device collection a name, below I have given “Windows 2019 Machines”
  5. Click browse
  6. Select All systems
  7. Click ok
  8. Click Next
    give device collection a name
  9. Click Add Rule then Query Rule
    add query rule
  10. On the query rule properties screen give the query a name, I gave Windows 2019 Machines
  11. Then click on edit query statement
    query rule properties page
  12. At the query statement properties page select the criteria tab
  13. Click on Show Query Language
    query statement properties
  14. Now copy and paste the query you want to use for your operating system. Note you can copy and paste the queries from this article I wrote
    windows 2019 collection query
  15. Click ok
  16. Click edit query statement again
  17. Click the criteria tab
  18. You can see the query in the criteria window. Below you can see system role = Server and Version = 10.0.17763
    query
  19. Click ok
  20. Click ok
  21. Click next
  22. Click next
  23. Click close
  24. Below you can see the device collection has now been created and it has two members in it
    windows 2019 server device collection

SCCM Collection Queries By Operating System

Listed below are the sccm collection queries I use for the listed operating systems.

Windows 10

Windows 10 Machines By Caption

Show all Windows 10 Machines

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 inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceId = SMS_R_System.ResourceId where SMS_G_System_OPERATING_SYSTEM.Caption = “Microsoft Windows 10%”

Show all Windows 10 Enterprise Machines

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 inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceId = SMS_R_System.ResourceId where SMS_G_System_OPERATING_SYSTEM.Caption = “Microsoft Windows 10 Enterprise”

Show all Windows 10 Pro Machines

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 inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceId = SMS_R_System.ResourceId where SMS_G_System_OPERATING_SYSTEM.Caption = “Microsoft Windows 10 Pro”

Show all Windows 10 Home

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 inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceId = SMS_R_System.ResourceId where SMS_G_System_OPERATING_SYSTEM.Caption = “Microsoft Windows 10 Home”

Windows 10 Machines By Build Number

Replace 19042 with the required build number, For a full list of build numbers take a look here

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 inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceId = SMS_R_System.ResourceId where SMS_G_System_OPERATING_SYSTEM.BuildNumber = “19042”

Windows 11

Windows 11 Machines By Caption

Show all Windows 11 Machines

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 inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceId = SMS_R_System.ResourceId where SMS_G_System_OPERATING_SYSTEM.Caption = “Microsoft Windows 11%”

Show all Windows 11 Enterprise Machines

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 inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceId = SMS_R_System.ResourceId where SMS_G_System_OPERATING_SYSTEM.Caption = “Microsoft Windows 11 Enterprise”

Show all Windows 11 Pro Machines

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 inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceId = SMS_R_System.ResourceId where SMS_G_System_OPERATING_SYSTEM.Caption = “Microsoft Windows 11 Pro”

Show all Windows 11 Home

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 inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceId = SMS_R_System.ResourceId where SMS_G_System_OPERATING_SYSTEM.Caption = “Microsoft Windows 11 Home”

Windows 11 Machines By Build Number

Replace 22000 with the required build number, For a full list of build numbers take a look here

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 inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceId = SMS_R_System.ResourceId where SMS_G_System_OPERATING_SYSTEM.BuildNumber = “22000”

Windows Server 2012

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 6.2%’ or OperatingSystemNameandVersion like ‘%Server 6.3%’

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 inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceId = SMS_R_System.ResourceId where OperatingSystemNameandVersion like ‘%Server 10%’ and SMS_G_System_OPERATING_SYSTEM.BuildNumber = ‘14393’

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 inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceId = SMS_R_System.ResourceId where OperatingSystemNameandVersion like ‘%Server 10%’ and SMS_G_System_OPERATING_SYSTEM.BuildNumber = ‘17763’

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 inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceId = SMS_R_System.ResourceId where OperatingSystemNameandVersion like ‘%Server 10%’ and SMS_G_System_OPERATING_SYSTEM.BuildNumber = ‘20348’

 

 

 

Leave a Comment