Set of Operational SCCM Collections

@Benoit Lecours - I've used your script as a base layer to make 16 device collections for Restart Pending clients. 

I have the script hosted on Github if you are happy for me to share the link here or I can forward it to you privately for inclusion within your already established collection. 

I'm happy either way.

Please, could you update the script to add 1809/1903 collections and update the expired/expiring collections accordingly? I've changed my expired/expiring collections to reference the feature updates accordingly to reduce the need to re-evaluate things

Hi guys and Benoit, somehow after I ran the ps1, it´s stop and do not continue.

Never finish to add the collections, always freeze.

Any idea?

Thanks

Just updating the rules is pretty straight forward.

Comment out this line:
#Get-CMDeviceCollection -Name $Collection.Name | Remove-CMDeviceCollection -Force

And then update the start of the main loop:
ForEach ($Collection In $($Collections | Sort-Object LimitingCollection))
{

Try 
{
        $Collection.Name
        $coll = Get-CMDeviceCollection -Name $Collection.Name
        if (!$coll) {
            New-CMDeviceCollection -Name $Collection.Name -Comment $Collection.Comment -LimitingCollectionName $Collection.LimitingCollection -RefreshSchedule $Schedule -RefreshType 2 | Out-Null
        } else {
            #Remove existing rules
            Get-CMDeviceCollectionQueryMembershipRule -CollectionId $coll.CollectionID |  %{ 
                Remove-CMDeviceCollectionQueryMembershipRule -CollectionId $coll.CollectionID -RuleName $_.RuleName -Force
            } 

            Set-CMDeviceCollection -CollectionId $coll.CollectionID -Comment $Collection.Comment -LimitingCollectionName $Collection.LimitingCollection -RefreshSchedule $Schedule -RefreshType 2 | Out-Null
        }
        Add-CMDeviceCollectionQueryMembershipRule -CollectionName $Collection.Name -QueryExpression $Collection.Query -RuleName $Collection.Name
        Write-host *** Collection $Collection.Name created ***
    }

Catch {

You will still need to accept overwriting the collections for them to be updated.

I've added a modified version of this script to my github account https://github.com/arricc/SccmScripts/blob/master/Operational%20Collections.ps1

Hi, for internal use i have rewritten the script to fit some automation purposes and use it for a complete creation and deletion of query collections. 
I have added more exception handling (if current user has permissions on defined sccm server, if sccm server is available, if folder structure is correct, cleanup tasks, etc), a parameter for SMS Site Server and Site Code, ....

I have also added a few more features. You can delete all/single collections or use an interactive deletion guide (based on powershell read-host), skip already created collections, set limiting collections, set direct membership, include/exclude collections and query membership rules and more stuff. 

I could publish the script for public, but I would add some changes in advance. If there is a demand, I can publish the script on Github afterwards.

You can define every collection inside an PS custom object. Just Copy one block, and edit it for your needs.

Example:

This Collection will show you all Devices with an installed SCCM Client but not with the latest version installed. It Includes all Devices with an installed SCCM Client (IncludeCollection) and excludes all devices which have the latest Client installed (ExcludeCollection). 

    $Collection4 = @{
        Name               = "Clients version | Not Latest";
        Comment            = "All devices without latest SCCM client";
        LimitingCollection = $Collection1.Name;
        Schedule           = $script:Schedule1;
        IncludeCollections = "Clients | All";
        ExcludeCollections = "Clients Version | 1810"
    }
    $Collections += $Collection4


Would you have any need for that?

Hi 
Do you have a query script for Windows Server 2019, 10.0.17763.1
I haven't got it works,

------------------------------------------
Thanks in advance 
Sokoban

Hi, 
you could use the following queries for Server 2016 & 2019. I am using the OperatingSystemNameandVersion + the Build Number to distinguish between the two server versions. We are using LTSB for Windows Server OS, so adjust the Build Numbers for your needs.

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

For 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, SMS_R_System.OperatingSystemNameandVersion, SMS_G_System_OPERATING_SYSTEM.BuildNumber from  SMS_R_System inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceId = SMS_R_System.ResourceId where SMS_R_System.OperatingSystemNameandVersion like '%Server 10%' and SMS_G_System_OPERATING_SYSTEM.BuildNumber = '17763'"

Server 2019 Systems are reporting under Server 2016 collection. 

Hi, 
you could use the following queries for Server 2016 & 2019. I am using the OperatingSystemNameandVersion + the Build Number to distinguish between the two server versions. We are using LTSB for Windows Server OS, so adjust the Build Numbers for your needs.

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

For 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, SMS_R_System.OperatingSystemNameandVersion, SMS_G_System_OPERATING_SYSTEM.BuildNumber from  SMS_R_System inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceId = SMS_R_System.ResourceId where SMS_R_System.OperatingSystemNameandVersion like '%Server 10%' and SMS_G_System_OPERATING_SYSTEM.BuildNumber = '17763'"

This used to be such a useful script, I wish you would offer download links to the old reliable versions.

Firstly, the get site code lines fail, we have multiple site codes. you are better off just prompting for the site code, this makes it much more flexible.

Secondly, there are no logs being created - this is extremely poor, there are no excuses for no logs.

Thirdly as many have pointed out, if they are already using the previous collections as includes/excludes/limiting collections, they can just delete them and recreate them can they? Not having the option to skip deleting them makes this script valueless to many people who used to use it.

Finally, the on screen errors are utterly pointless, what am I supposed to do with these? How can a typical user begin to look into the problem without contacting you for assistance, because you have suppressed any useful message from PowerShell...

Please Benoit, if this script is to be of value to a wide community, can you architect it to from a higher level view so its of use to the many and not the few?


There was an error creating the: Clients Version | Not Latest (1806) collection.
-----------------
-----------------
There was an error moving the: Clients Version | Not Latest (1806) collection to EMEA.
-----------------
-----------------
There was an error creating the: Hardware Inventory | Clients Not Reporting since 14 Days collection.
-----------------
-----------------
There was an error moving the: Hardware Inventory | Clients Not Reporting since 14 Days collection to EMEA.
-----------------
-----------------
There was an error creating the: System Health | Clients Active collection.
-----------------
-----------------
There was an error moving the: System Health | Clients Active collection to EMEA.
-----------------
-----------------
There was an error creating the: System Health | Clients Inactive collection.
-----------------
-----------------

I also need the script to just skip already created collections and only to create those that do not exist. I have used these collections for different deployments and also limiting and removing devices from another collections.

Agreeing with Bhushan and Krayzie Se7en.  Please add in a function that skips already created collections. We are really grateful for the work you've done and use these collections as limits for other collections etc.