Set of Operational SCCM Collections

Hello,
Great list. But after creating the set i see something strange happening.
The collection Servers | Windows 2019 does not get populated. I have installed 3 days ago 2 windows server 2019 servers. They appear in the devices list.
Hope you can help.
Regards, Ernst

Any reason why the script stops at *** Collection Clients Version 1810 moved to Operational Folder ***

I've ran it 3 times and it keeps stopping at the same spot.  I let it sit overnight.

Thanks!

Mr. Lecours,

As we have now migrated to the above mentioned OS for our Hyper-V hosts, could you share with us how we might query for a Serve 2019 "datacenter core" collection?

Thanks for the consideration.

Charles

@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'"