Back in SCCM 2012R2, this wonderful feature was added where you could leverage these new fancy rules called ‘Include’ and ‘Exclude’ collections. OK, so it is wonderful, at least until you try to delete a collection that has been leveraged as an include or exclude collection rule. If it is you get this big friendly error message:
And then it gives you a list of collections that are all related and their name. Annoying, and doubly so when you find out you need to manually go fix all of those relationships. Especially as the error message doesn’t provide how to find them.
Rest easy because SQL and Powershell together can save the day. I’m going to provide the more ‘simple’ answer first and then later will release the slightly more complex resolution. Don’t worry it’s only complex because it tries to incorporate some automation.
First, you’ll need to crack open SQL and connect to your SCCM Database. Open up Query editor and throw this bad boy in there:
So what you’ll want to do at the end of this, where it says ‘YOUR COLLECTION ID HERE’ is to put your collection ID there. Now, I’m going to explain what this query does and how it works. If you don’t care and just want to move on to something useful, scroll down until you encounter a picture.
select distinct v_Collection.Name as 'Collection Dependency Name', v_Collection.CollectionID, vSMS_CollectionDependencies.SourceCollectionID as 'SourceCollection', Case When vSMS_CollectionDependencies.relationshiptype = 1 then 'Limited To ' + v_Collection.name + ' (' + vSMS_CollectionDependencies.SourceCollectionID + ')' when vSMS_CollectionDependencies.relationshiptype = 2 then 'Include ' + v_Collection.name + ' (' + vSMS_CollectionDependencies.SourceCollectionID + ')' when vSMS_CollectionDependencies.relationshiptype = 3 then 'Exclude ' + v_Collection.name + ' (' + vSMS_CollectionDependencies.SourceCollectionID + ')' end as 'Type of Relationship' from v_Collection join vSMS_CollectionDependencies on vSMS_CollectionDependencies.DependentCollectionID = v_Collection.CollectionID where vSMS_CollectionDependencies.SourceCollectionID = 'YOUR COLLECTION ID HERE
So, V_Collection is pretty obvious, this is information about every collection that exists within your environment. My production environment has over 1K collections and has no issue running this query. Second, V_SMSCollectionDependencies this view shows all dependencies collections have between each other. This is the same information that gets used actually during delta collection evaluation (You know, when a collection updates and causes a ripple of change).
This query gathers and associates collection information, and then joins it to all of the dependency relationships and then filters it back to only include information on the collection you are trying to delete. Simple stuff. I also went ahead and converted the dependency types in there.
1 = Limited to
2 = Include Collection Membership Rule
3 = Exclude Collection Membership Rule
If you do that you’ll get an output that looks like this:
OK Great so its some data but what the !@#@#$ does it mean. Simple, this shows the relationship in my lab environment (If you don’t have one BUILD ONE) between the ‘All Servers’ Collection and all other collections and explains the relationship. Example the ‘NAT – Member Servers’ is related to ‘All Servers’ in two ways. First, the collections limiting collection is the ‘All Servers’ collection. Second, the ‘All Servers’ collection is included in the ‘NAT – Member Servers’ collection.
This will allow you to quickly find all of the relationships the collection you would like to delete has. To expedite this process I’ve put together a PowerShell script that attempts to do all of the mentioned steps AND remove the collection rules for you. I’ll put together a second post on this as its actively making changes not just showing you how to do something.