Create Operational SCCM collection using PowerShell script

Benoit LecoursPowershell, SCCM26 Comments

Using Powershell you can do many things in SCCM. Over the years, in many SCCM consulting projects that we got involved in, we get the same question: “Can you create SCCM collections for Servers, Laptops, Workstations, Windows 10…”. Back in 2015, I started to build an SCCM collection PowerShell script to create these operational collections that I create right after we do an SCCM installation. With time, I added more and more collections to the script. Today, the script contains more than 130 collections and has been downloaded more than 60 000 times making this my most downloaded contribution to the community.

This set of collections usually covers 95% of the initial client needs. All you need to do is to run the SCCM collection PowerShell script on your SCCM server and wait. After running, you’ll end up having 134 collections in an Operational folder.

The collections are set to refresh on a 7 days schedule. Once created, you can use these collections to have a quick overview of your devices. You can also use this collection to create deployment collections by using limiting collections on these ones.

The script uses the New-CMDeviceCollection and Add-CMDeviceCollectionQueryMembershipRule cmdlet.

SCCM collection powershell script
SCCM collection powershell script
SCCM collection powershell script

Full collections list

Here’s the full SCCM collection that the script Powershell will create.

  • All Clients
  • All Clients Active
  • All Client Inactive
  • All Clients Online
  • All Clients R2 CU0
  • All Clients R2 CU1
  • All Clients R2 CU2
  • All Clients R2 CU3
  • All Clients R2 CU4
  • All Clients R2 CU5
  • All Clients R2 SP1
  • All Clients R2 SP1 CU1
  • All Clients R2 SP1 CU2
  • All Clients R2 CU0
  • All Clients R2 CU1
  • All Clients R2 CU2
  • All Clients R2 CU3
  • All Clients R2 CU4
  • All Clients R2 CU5
  • All Clients R2 SP1
  • All Clients R2 SP1 CU1
  • All Clients R2 SP1 CU2
  • All Clients R2 SP1 CU3
  • All Clients Not Latest
  • All Client 1511
  • All Client 1602
  • All Client 1606
  • All Client 1610
  • All Client 1702
  • All Client 1706
  • All Client 1710
  • All Client 1802
  • All Client 1806
  • All Client 1810
  • All Client 1902
  • All Client 1906
  • All Client 1910
  • All Client 2002
  • All Client 2006
  • All Client 2010
  • All Client 2103
  • All Client 2107
  • All Clients Not Reporting HW Inv since 14 days
  • All Clients Not Reporting SW Inv since 30 days
  • All Clients X86
  • All Clients X64
  • All Laptops
  • All HP Laptops
  • All Lenovo Laptops
  • All Dell Laptops
  • All SCCM Distribution Points
  • All SCCM Site Servers
  • All SCCM Site Systems
  • All Servers
  • All Servers Physical
  • All Servers Virtual
  • All Servers Windows 2003 or 2003 R2
  • All Servers Windows 2008 or 2008 R2
  • All Servers Windows 2012 or 2012 R2
  • All Servers Windows 2016
  • All Servers Windows 2019
  • All Systems Created Since 24h
  • All Systems Disabled
  • All Systems Non Client
  • All Systems Obsolete
  • All Systems with SCCM Console
  • All Workstations
  • All Workstations Windows 7
  • All Workstations Windows 8
  • All Workstations Windows 8.1
  • All Workstations Windows 10
  • All Workstations Windows 11
  • All Workstations Windows XP
  • All Windows Update Agent Version Outdated
  • Mobile Devices – All Android
  • Mobile Devices – All Ipad
  • Mobile Devices – All Iphone
  • Mobile Devices – All Windows Phone 10
  • Mobile Devices – All Windows Phone 8
  • Mobile Devices – All Windows Phone 8.1
  • Mobile Devices – All Microsoft Surface
  • Mobile Devices – All Microsoft Surface 3
  • Mobile Devices – All Microsoft Surface 4
  • Mobile Device – All
  • Workstations | Windows 10 v1507
  • Workstations | Windows 10 v1511
  • Workstations | Windows 10 v1607
  • Workstations | Windows 10 v1703
  • Workstations | Windows 10 v1709
  • Workstations | Windows 10 v1803
  • Workstations | Windows 10 v1809
  • Workstations | Windows 10 v1903
  • Workstations | Windows 10 v1909
  • Workstations | Windows 10 Current Branch (CB)
  • Workstations | Windows 10 Current Branch for Business (CBB)
  • Workstations | Windows 10 Long Term Servicing Branch (LTSB)
  • Workstations | Windows 10 Support State – Current
  • Workstations | Windows 10 Support State – Expired Soon
  • Workstations | Windows 10 Support State – Expired
  • Others | Linux Devices
  • Others | MAC OSX Devices
  • Office 365 Build Version | 1705
  • Office 365 Build Version | 1709
  • Office 365 Build Version | 1802
  • Office 365 Build Version | 1803
  • Office 365 Build Version | 1808
  • Office 365 Build Version | 1902
  • Office 365 Build Version | 1908
  • Office 365 Build Version | 1912
  • Office 365 Build Version | 2001
  • Office 365 Build Version | 2002
  • Office 365 Build Version | 2003
  • Office 365 Build Version | 2004
  • Office 365 Build Version | 2005
  • Office 365 Build Version | 2006
  • Office 365 Build Version | 2007
  • Office 365 Build Version | 2008
  • Office 365 Build Version | 2009
  • Office 365 Build Version | 2010
  • Office 365 Build Version | 2011
  • Office 365 Build Version | 2012
  • Office 365 Build Version | 2101
  • Office 365 Build Version | 2102
  • Office 365 Build Version | 2103
  • Office 365 Build Version | 2104
  • Office 365 Build Version | 2105
  • Office 365 Build Version | 2106
  • Office 365 Build Version | 2107
  • Office 365 Build Version | 2108
  • Office 365 Build Version | 2109
  • Office 365 Build Version | 2110
  • Office 365 Channel | Monthly
  • Office 365 Channel | Monthly (Targeted)
  • Office 365 Channel | Semi-Annual
  • Office 365 Channel | Semi-Annual (Targeted)
  • All systems having a duplicate device record

SCCM collection powershell script Note

The script will detect if the collection has already been created. It will give a warning and create only new collections that have been added since the last time the script is run.

If you are comfortable with editing scripts, you can comment out any unwanted collections using (#) at each line of the section.

Ex:

#$Collections +=
#$DummyObject |
#Select-Object @{L=”Name”
#; E={“Clients Version | R2 CU5″}},@{L=”Query”
#; E={“select #SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifie#r,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System
#where SMS_R_System.ClientVersion = ‘5.00.7958.1604’”}},@{L=”LimitingCollection”
#; E={$LimitingCollection}},@{L=”Comment”
#; E={“All systems with SCCM client version R2 CU5 installed”}}

#New-CMDeviceCollection -Name $Collection1.Name -LimitingCollectionName $LimitingCollection -RefreshSchedule $Schedule -RefreshType 2

#Add-CMDeviceCollectionQueryMembershipRule -CollectionName $Collection1.Name -QueryExpression $Collection1.Query -RuleName $Collection1.Name

Download the SCCM collection PowerShell script

The script can be downloaded from my GitHub repo.

Extra hint: You can also verify if your collection has been created properly in your collections with our  Configuration Manager – Collections report. Simply sort the report by the Operational folder name

SCCM Collection query

ChangeLog

# Version : 3.5
# Created : 2014/07/17
# Modified : 
# 2014/08/14 - Added Collection 34,35,36
# 2014/09/23 - Changed collection 4 to CU3 instead of CU2
# 2015/01/30 - Improve Android collection
# 2015/02/03 - Changed collection 4 to CU4 instead of CU3
# 2015/05/06 - Changed collection 4 to CU5 instead of CU4
# 2015/05/06 - Changed collection 4 to SP1 instead of CU5
#            - Add collections 37 to 42
# 2015/08/04 - Add collection 43,44
#            - Changed collection 4 to SP1 CU1 instead of SP1
# 2015/08/06 - Change collection 22 query
# 2015/08/12 - Added Windows 10 - Collection 45
# 2015/11/10 - Changed collection 4 to SP1 CU2 instead of CU1, Add collection 46
# 2015/12/04 - Changed collection 4 to SCCM 1511 instead of CU2, Add collection 47
# 2016/02/16 - Add collection 48 and 49. Complete Revamp of Collections naming. Comment added on all collections
# 2016/03/03 - Add collection 51
# 2016/03/14 - Add collection 52
# 2016/03/15 - Added Error handling and better output
# 2016/08/08 - Add collection 53-56. Modification to collection 4,31,32,33
# 2016/09/14 - Add collection 57
# 2016/10/03 - Add collection 58 to 63
# 2016/10/14 - Add collection 64 to 67
# 2016/10/28 - Bug fixes and updated collection 50
# 2016/11/18 - Add collection 68
# 2017/02/03 - Corrected collection 39 and 68
# 2017/03/27 - Add collection 69,70,71
# 2017/08/25 - Add collection 72
# 2017/11/21 - Add collection 73
# 2018/02/12 - Add collection 74-76. Changed "=" instead of like for OS Build Collections
# 2018/03/27 - Add collection 77-81. Corrected Collection 75,76 to limit to Workstations only. Collection 73 updated to include 1710 Hotfix
# 2018/07/04 - Version 3.0
#            - Add Collection 82-87
#            - Optimized script to run with objects, extended options for replacing existing collections, and collection folder creation when not on site server.
# 2018/08/01 - Add Collection 88
# 2019/04/04 - Add Collection 89-91
# 2019/09/17 - Add Collection 92-94, Windows 2019, Updated Windows 2016
# 2020/01/09 - Add Collection 95-100
# 2021/11/22 - Add Collection 100-133

If you want to add a collection to the list, feel free to contact me using our social media or use the comment section. It will be our pleasure to add it to the next version.

26 Comments on “Create Operational SCCM collection using PowerShell script”

  1. Pingback: How to use SCCM dynamic queries in your deployment collections – IT Blog – Philip Loftin

  2. Script has already been run or a collection name already exist. Delete All Operational collection before re-executing the script !

    i cant fix this issue

  3. This substance is composed exceptionally well about explaining how to strengthen security and separate Intune with Configuration Manager infrastructure in SCCM console. Your utilization of organizing when mentioning your focuses makes your objective facts clear and straightforward. Much obliged to you.

  4. Hi, nice script if you need all Vendor | Modells PS.: first create the folder Modell in root …
    #—————————————————————————————————————
    [CmdletBinding(SupportsShouldProcess=$true)]
    param()
    Begin {
    #Load Configuration Manager PowerShell Module
    Import-module ($Env:SMS_ADMIN_UI_PATH.Substring(0,$Env:SMS_ADMIN_UI_PATH.Length-5) + ‘\ConfigurationManager.psd1’)

    #Get SiteCode
    $SiteCode = Get-PSDrive -PSProvider CMSITE

    #Write-host “SiteCode: $($SiteCode)”
    Set-location $SiteCode”:”

    # Determine SiteCode from WMI
    try {
    Write-Verbose “Determining SiteCode for Site Server: ‘$($SiteServer)'”
    $SiteCodeObjects = Get-WmiObject -Namespace “root\SMS” -Class SMS_ProviderLocation -ComputerName $SiteServer -ErrorAction Stop
    foreach ($SiteCodeObject in $SiteCodeObjects) {
    if ($SiteCodeObject.ProviderForLocalSite -eq $true) {
    $SiteCode = $SiteCodeObject.SiteCode
    Write-Debug “SiteCode: $($SiteCode)”
    }
    }
    }
    catch [Exception] {
    Throw “Unable to determine SiteCode”
    }

    #Refresh Schedule
    $Schedule = New-CMSchedule –RecurInterval Days –RecurCount 7

    #Create Defaut Folder
    $CollectionFolder = @{Name = “Modell”; ObjectType = 5000; ParentContainerNodeId = 0}
    Set-WmiInstance -Namespace “root\sms\site_$($SiteCode.Name)” -Class “SMS_ObjectContainerNode” -Arguments $CollectionFolder

    #Create Default limiting collections
    $LimitingCollection = “All Systems”

    #Create Defaut FolderPath
    $FolderPath = $SiteCode + “:\DeviceCollection\” + $CollectionFolder.Name

    # ArrayList to store the vendor | models in
    $ClientsArrayList = New-Object -TypeName System.Collections.ArrayList
    $CollectionModelsArrayList = New-Object -TypeName System.Collections.ArrayList

    # Enumerate through all models
    $Clietns = @()
    $Clietns = Get-WmiObject -Namespace “Root\SMS\Site_$($SiteCode)” -computer $SiteServer -Class SMS_G_System_COMPUTER_SYSTEM | Select-Object -Property Manufacturer, Model

    }
    Process {

    # Add model to ArrayList if not present
    if ($Clietns -ne $null) {
    foreach ($ArrayClient in $Clietns) {
    foreach ($Client in $ArrayClient) {
    if (!($ClientsArrayList -match $Client.Model)) {
    $PSO = [PSCustomObject]@{Name = $Client.Manufacturer+” | “+$Client.Model; 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_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceId = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM.Model = “+'”‘+$Client.Model+'”‘}
    $ClientsArrayList.Add($PSO) | Out-Null
    }
    }
    }
    }

    #Create Collections Model
    if ($ClientsArrayList.Count -ge 1) {
    foreach ($Collection in $ClientsArrayList) {
    #if ($Collection.Name -eq “Dell Inc. | PowerEdge 2950”) {
    New-CMDeviceCollection -Name $Collection.Name -Comment “Modell” -LimitingCollectionName $LimitingCollection -RefreshSchedule $Schedule -RefreshType 2 | Out-Null
    Add-CMDeviceCollectionQueryMembershipRule -CollectionName $Collection.Name -QueryExpression $Collection.Query -RuleName $Collection.Name
    Write-host *** Collection $Collection.Name created ***
    Move-CMObject -FolderPath $FolderPath -InputObject (Get-CMDeviceCollection -Name $Collection.Name)
    #}
    }
    }
    }
    End {
    #Write-Output $ModelsArrayList
    #Write-Output $CollectionModelsArrayList | Format-Table -Wrap
    #Write-Output $FolderPath
    }
    #—————————————————————————————————————

  5. Hello Benoit,

    If I have the names of hosts on which I need to install a package, how would I go about it using PS (Did not find any such option in GUI)? Just to be more clear on query, I’ve a list of Win10 hosts on which I need to deploy a package. These set of hosts are among a larger set of Win10 hosts and have difference only in terms of custom environment variables. I did not find a way to capture environment variables using custom query in GUI and hoping PS may have some answer to this.

    Regards,
    Shakti

  6. This script doesn’t work right with SCCM 1511. (console/site version of 5.00.8325.1000).

    It keeps failing saying
    “Script has already been run or a collection name already exist. Delete All Operational collection before re-executing the script!”

    The only collections that I have are
    All Systems
    All Unknown Computers
    All Desktop and Server Clients
    All Mobile Devices

    And none of those can be deleted.
    Any Suggestions?

    1. Found the faulting issue

      New-CMDeviceCollection -Name $Collection11.Name -Comment “All physical servers” -LimitingCollectionName $Collection10.Name -RefreshSchedule $Schedule -RefreshType 2 | Out-Null
      Add-CMDeviceCollectionQueryMembershipRule -CollectionName $Collection11.Name -QueryExpression $Collection11.Query -RuleName $Collection11.Name
      Write-host *** Collection $Collection11.Name created ***

      This just causes the process to not continue.

  7. Benoit, I downloaded the script from technet today. Used on a newly deployed lab, but got an error trying to create the collections. It appears that you are trying to create collection 53 and collection 68 with the same name but diff query. I changed my downloaded script on collection 68 to 1610 for the name.

  8. Noticed the collection for 1606 clients was not working and the criteria is looking for client version 5.00.8412.1006. That should be 1007, right?

  9. The “All Windows Update Agent Version Outdated – Win7 RTM and lower” collection is picking up all my Windows 10 workstations for some reason??

    1. This is weird… there’s a filter on the OS Version in the query. (SMS_G_System_OPERATING_SYSTEM.Version <= '6.1.7600'). Can you check the OS Version in the SCCM DB on some Windows 10 devices to see what is this value. Windows 10 version should be 10.%

  10. This is a great script, i’ve used it here and its covered almost everything. Just need to add in Windows 10 and maybe at the top a variable to allow changing the default refresh time from 7 days to something else

      1. It appears that some versions and editions of Windows 10, such as the Windows 10 Enterprise LTSB, do not follow the traditional naming convention. This affections Collections 8, 13, and 45.

        From my testing using “where OperatingSystemNameandVersion like ‘%Workstation 10.0%’ or OperatingSystemNameandVersion like ‘%Windows 10%'” should get all over the flavors.

        Thanks

        P.S. I haven’t tested Windows 10 mobile yet.

Leave a Reply