MSEndpointMgr

Removing Annoying Include/Exclude Collection Rules Part 2

A  short while ago I posted about the annoying ‘Delete Collection’ error pop-up that appears when you attempt to delete a collection that is referenced through the environment. At the time I provided a SQL query that while useful for finding all of the relationships wasn’t useful for anything else. However, I alluded to having a PowerShell solution to this minor irritant and now after completely throwing away the scripted solution I originally wrote for my team I’m happy to announce I have published a PowerShell Module that finds those relationships and removes them.

The Problem:

Periodically due to the usage of ‘Include’ or ‘Exclude’ rules or even using a collection as a limiting collection (Currently out of scope) when you attempt to delete a collection you are greeted with the pop-up.

Now, the above example not so bad right? Just go check those collections out and fix, however in larger environments, it’s not uncommon to use include/exclude rules all over the place. Example Excluding Hyper-V Hosts from a great many collections.

The Solution:

If you haven’t I suggest you go read my original blog on using SQL to find the relationships. You can find it here: but I wanted to take this to the next level. So I created a new PowerShell Module that I’m going to work to continuously improve called SCCM-ProbRes. You can download it from the PSGallery here: and try and figure it out, or read along on how to use it to remove collection include rules that are preventing you from deleting a collection.

Installing the Module:

REQUIREMENTS:

SQLServer Module – You’ll need the SQL Server module from PSGallery

Configuration Manager Console (and PSCmdlets)

In order to leverage the SCCM-ProbRes module, you’ll need these two modules. I debated for a while on just automatically including the SQL Server within the manifest and decided against this in the event users already have the module. You can do this by just running:

Install-Module -name SQLServer

This will install the Needed SQLServer PowerShell Cmdlets

After that, I’m going to go ahead and import the SCCM Powershell Cmdlets as well. (This module supports two cmdlets.)

Import-Module (Join-Path $(Split-Path $env:SMS_ADMIN_UI_PATH) ConfigurationManager.psd1)

Get-CollectionRelationships & Remove-CollectionRules

Get-CollectionRelationships is the primary driver behind this and will be used to create an object that has the list of all of the include/exclude rules the collection we want to delete has. Get-CollectionRelationships has a few parameters.

-CollectionID” – This is the collection you want to delete but can’t because its in use as a rule in the environment or as a limiting collection. This parameter is mandatory.

-Server” – This is the server where your SCCM SQL instance resides. This parameter is not mandatory, however if you do not enter the server name it will assume SQL resides locally.

“-Database” – this is the database name for your CAS/Primary. This parameter is no mandatory, however if you do not enter the database name it will assume the name is standard CM_(SiteCode).

Remove-CollectionRules is the secondary cmdlet that will be doing the actual changes using the SCCM PowerShell cmdlets.

-CollectionRulesToEvaluate” – This is a powershell object that includes criteria that is provided from running Get-CollectionRelationships

-SiteServer” – this is the site server where the collections you need to make changes to reside. This has been tested in a CAS/Primary structure and you should specify the CAS unless, the collections being changed all reside on the same primary.

As an example, I have a collection “PR100017” that I would like to delete but can’t, because its in use as an include and exclude rule on two other collections. However, using “Get-CollectionRelationships -CollectionID PR100017 -verbose” I get a list of all the rules that are preventing me from deleting my collection.

I can now store this information in a variable and:

$Obj = Get-CollectionRelationships -CollectionID PR100017

Remove-CollectionRules -CollectionRulesToEvaluate $obj -SiteServer CAS

As you can see the include Exclude rules have been removed and the collection is now safe to delete. I will be working to update this module in the PSGallery as time goes on and if you have a specific request let me know and I’ll work to get it incorporated, the request does not have to be related to collection membership as I am looking forward to making this module try to resolve interesting challenges within SCCM.

Jordan Benzing

Jordan has been working in the Industry since 2009. Since starting he’s worked with Active Directory, Group Policy, SCCM, SCOM and PowerShell. Jordan most recently worked in the healthcare industry as an SCCM Infrastructure Team lead supporting over 150,000 endpoints. Jordan currently works as a Senior consultant for TrueSec Inc in the U.S. Most recently his focus has been in SQL Reporting for SCCM, creation of PowerShell scripts to automate tasks and PowerBI.

3 comments

  • Hi Jordan,

    While running Remove-CollectionRules -CollectionRulesToEvaluate $obj -SiteServer “<CASE SERVER NAME". I am also getting following error:

    Get-SqlDatabase : Failed to connect to server .
    At C:\temp\PSModule\SCCM-ProbRes\1.0.0.3\SCCM-ProbRes.psm1:58 char:12
    + $CMDB = Get-SqlDatabase -ServerInstance $ENV:COMPUTERNAME -Verbose:$false | w …
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : ObjectNotFound: (System.String[]:String[]) [Get-SqlDatabase], ConnectionFailureException
    + FullyQualifiedErrorId : ConnectionToServerFailed,Microsoft.SqlServer.Management.PowerShell.IaaS.GetSqlDatabaseCommand

    I believe it is looking for DB on same server

    • I’ll do a review on this later on tonight I’ve been meaning to do a code review and add this to the CMOperations Module anyways.

      Cheers!

      -J

  • Hi Jordan,

    Thank you the article. However, I am getting following error while running Get-collectionRelationships
    Invoke-Sqlcmd : The SELECT permission was denied on the object ‘vSMS_CollectionDependencies’, database ‘CM_2AA’, schema ‘dbo’.

Sponsors