Set of Operational SCCM Collections

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.

I'm suggesting this be moved to github?  it would make it easier for folks to add some adjustments.

Can you please add an if function like below so we won't have to delete and recreate these collections all over again ? (Delete and recreate causes to change the collection ID which later causes dependent collection to fail at times. Hence the request.) 


if ((Get-CMCollection -Name $Collection1.Name).count -eq 1) {

Write-host *** Collection $Collection1.Name already exists ***

}

else 
{
New-CMDeviceCollection -Name $Collection1.Name -Comment "All devices detected by SCCM" -LimitingCollectionName $LimitingCollection -RefreshSchedule $Schedule -RefreshType 2 | Out-Null
Add-CMDeviceCollectionQueryMembershipRule -CollectionName $Collection1.Name -QueryExpression $Collection1.Query -RuleName $Collection1.Name
Write-host *** Collection $Collection1.Name created ***
}

for each collection and then remove the if ($error -eq 1) part with warning to delete the existing collections. 

Basically we are really grateful of the work you've done and use these collections as limits for other collections etc. and when you update the script, this may help us add only the new ones instead of recreating all of them.  

Hope you'd like the idea.

Thanks 
Bhushan

Same as KacCouilard, how can we get around this?

I even deleted all collections with | in the name, no joy.

Hi Benoit, 
I last used your script very successful in July of 2017.  Today when I run 2.10.ps1
I get a prompt to delete All Operational collection, pressing enter to continue quits the script and no new collections are installed.  I thought your script would continue past same names? 
"Script has already been run or a collection name already exist.  Delete All Operational collection before re-executing the script!  Press enter to continue..." 
What to do? Thank you. 

Working on a new version to handle that. For now, just delete the previously created collection manually and re-run the script.