SCCM collections

Nov 23, 2023 · 6 min read · POWERSHELL SCCM ·

Overview

Intro

These are various queries to populate the collections in SCCM, which will be updated in the future.

Additionally, at the bottom, I have included a script for the automatic addition of the collections shown in this post

Collections

Collection Boundary Groups Based

This query adds the devices that are part of a specific Boundary Group to the collection. It's necessary to modify the query by inserting the name of your own group.

1select 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.ResourceId in  (select resourceid from SMS_CollectionMemberClientBaselineStatus  where SMS_CollectionMemberClientBaselineStatus.boundarygroups = 'YOUR_BOUNDARY_GROUP_HERE')  and SMS_R_System.Name not in ('Unknown') and SMS_R_System.Client = '1'

Windows OS version Collections

Collection Windows 10 20H2 Based

This query adds devices with Windows 10 20H2 installed to the collection.

1select 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.Build = '10.0.19042'

Collection Windows 10 21H1 Based

This query adds devices with Windows 10 21H1 installed to the collection.

1select 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.Build = '10.0.19043'

Collection Windows 10 21H2 Based

This query adds devices with Windows 10 21H2 installed to the collection:

1select 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.Build = '10.0.19044'

Collection Windows 10 22H2 Based

This query adds devices with Windows 10 22H2 installed to the collection:

1select 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.Build = '10.0.19045'

Collection Windows Server 2012 Based

This query adds devices with Windows Server 2012 installed to the collection:

1select 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 "Microsoft Windows NT Server 6.2%"  OR SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT Advanced Server 6.2%"

Collection Windows Server 2012 R2 Based

This query adds devices with Windows Server 2012 R2 installed to the collection:

1select 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 "Microsoft Windows NT Server 6.3%"  OR SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT Advanced Server 6.3%"

Collection Windows Server 2016 Standard Based

This query adds devices with Windows Server 2016 Standard installed to the collection:

1select 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.Version = "10.0.14393" and SMS_G_System_OPERATING_SYSTEM.Caption = "Microsoft Windows Server 2016 Standard"

Collection Windows Server 2016 Datacenter Based

This query adds devices with Windows Server 2016 Datacenter installed to the collection:

1select 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.Version = "10.0.14393" and SMS_G_System_OPERATING_SYSTEM.Caption = "Microsoft Windows Server 2016 Datacenter"

Collection Windows Server 2019 Standard Based

This query adds devices with Windows Server 2019 Standard installed to the collection:

1select 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.Version = "10.0.17763" and SMS_G_System_OPERATING_SYSTEM.Caption = "Microsoft Windows Server 2019 Standard"

Collection Windows Server 2019 Datacenter Based

This query adds devices with Windows Server 2019 Datacenter installed to the collection:

1select 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.Version = "10.0.17763" and SMS_G_System_OPERATING_SYSTEM.Caption = "Microsoft Windows Server 2019 Datacenter"

Collection Windows Server 2022 Standard Based

This query adds devices with Windows Server 2022 Standard installed to the collection:

1select 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.Version = "10.0.20348" and SMS_G_System_OPERATING_SYSTEM.Caption = "Microsoft Windows Server 2022 Standard"

Collection Windows Server 2022 Datacenter Based

This query adds devices with Windows Server 2022 Datacenter installed to the collection:

1select 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.Version = "10.0.20348" and SMS_G_System_OPERATING_SYSTEM.Caption = "Microsoft Windows Server 2022 Datacenter"

Script for Automatic Collection Creation

I have created a small script for the automatic creation of collections that I use most frequently when working on a new SCCM server. This saves me a lot of time compared to manually creating each one.

Here is the code for the script:

 1
 2  # Definition of collections and their queries
 3
 4$collections = @(
 5    @{
 6        Name = "Windows Server 2019 Standard Based"
 7        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 inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_OPERATING_SYSTEM.Version = '10.0.17763' and SMS_G_System_OPERATING_SYSTEM.Caption = 'Microsoft Windows Server 2019 Standard'"
 8    },
 9    @{
10        Name = "Windows Server 2019 Datacenter Based"
11        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 inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_OPERATING_SYSTEM.Version = '10.0.17763' and SMS_G_System_OPERATING_SYSTEM.Caption = 'Microsoft Windows Server 2019 Datacenter'"
12    },
13    @{
14        Name = "Windows 10 20H2 Based"
15        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.Build = '10.0.19042'"
16    },
17    @{
18        Name = "Windows 10 21H1 Based"
19        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.Build = '10.0.19043'"
20    },
21    @{
22        Name = "Windows 10 21H2 Based"
23        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.Build = '10.0.19044'"
24    },
25    @{
26        Name = "Windows 10 22H2 Based"
27        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.Build = '10.0.19045'"
28    },
29    @{
30        Name = "Windows Server 2012 Based"
31        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 "Microsoft Windows NT Server 6.2%"  OR SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT Advanced Server 6.2%""
32    },
33    @{
34        Name = "Windows Server 2012 R2 Based"
35        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 "Microsoft Windows NT Server 6.3%"  OR SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT Advanced Server 6.3%""
36    },
37    @{
38        Name = "Windows Server 2016 Standard Based"
39        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 inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_OPERATING_SYSTEM.Version = "10.0.14393" and SMS_G_System_OPERATING_SYSTEM.Caption = "Microsoft Windows Server 2016 Standard""
40    },
41    @{
42        Name = "Windows Server 2016 Datacenter Based"
43        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 inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_OPERATING_SYSTEM.Version = "10.0.14393" and SMS_G_System_OPERATING_SYSTEM.Caption = "Microsoft Windows Server 2016 Datacenter""
44    },
45    @{
46        Name = "Windows Server 2019 Standard Based"
47        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 inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_OPERATING_SYSTEM.Version = "10.0.17763" and SMS_G_System_OPERATING_SYSTEM.Caption = "Microsoft Windows Server 2019 Standard""
48    },
49    @{
50        Name = "Windows Server 2019 Datacenter Based"
51        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 inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_OPERATING_SYSTEM.Version = "10.0.17763" and SMS_G_System_OPERATING_SYSTEM.Caption = "Microsoft Windows Server 2019 Datacenter""
52    },
53    @{
54        Name = "Windows Server 2022 Standard Based"
55        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 inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_OPERATING_SYSTEM.Version = "10.0.20348" and SMS_G_System_OPERATING_SYSTEM.Caption = "Microsoft Windows Server 2022 Standard""
56    },
57    @{
58        Name = "Windows Server 2022 Datacenter Based"
59        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 inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_OPERATING_SYSTEM.Version = "10.0.20348" and SMS_G_System_OPERATING_SYSTEM.Caption = "Microsoft Windows Server 2022 Datacenter""
60    }
61    
62)
63
64# Creation of collections
65foreach ($collection in $collections) {
66    $collectionName = $collection.Name
67    $collectionQuery = $collection.Query
68
69    # Create the collection
70    $newCollection = New-CMDeviceCollection -Name $collectionName -LimitingCollection "All Systems"
71
72    # Add the query rule
73    Add-CMDeviceCollectionQueryMembershipRule -CollectionId $newCollection.CollectionID -QueryExpression $collectionQuery -RuleName "$collectionName Rule"
74    
75    Write-Host "Collection created: $collectionName"
76}