SCCM Collection Relationships

The Problem:

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:

DeleteCollectionError

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.

The Resolution:

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:

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 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.

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:

SQL-Collection-Dependency-Results

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.

  1. Hi, is it possible to do this the other way around? I would like to be able to see the collections that are includes in a certain collection.

    Like

    Reply

    1. Yes you absolutely can do this! The easiest way to do it would be to add a where condition to the end of the SQL statement.

      If you put the collection you are looking for in the where statement it will show you every relationship that collection has to other collections.

      Like

      Reply

  2. mmm Could you show me where I should do this there is a where already?

    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 =@Collection

    I created a parameter for collectionID.)(

    Like

    Reply

  3. Is it also possible to check the collection name and not the collection ID? So instead of this: join vSMS_CollectionDependencies on vSMS_CollectionDependencies.DependentCollectionID = v_Collection.CollectionID
    where vSMS_CollectionDependencies.SourceCollectionID =@Collection

    Than based on collectionname not ID?

    Like

    Reply

  4. Well I found I got the info I need It was allready in your query but is there a way to get the collection name of sourcecollection, instead of the id???

    Like

    Reply

  5. I still can’t get it to work the way I want. I have got a collection, that is not included or has got releationships with other collections I just want to get the info: show me the collections that are included into this collection. So the other way arround.

    Like

    Reply

    1. Ah I see what you’re asking you want to reverse the relationship matching try this instead:

      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.DependentCollectionID = ‘YourCollectionIDHere’

      Like

      Reply

  6. Yes, now I am getting the collections ID of the collections that are included Thanks! But my final question is: I dont want to show the collectionID’s but the collection names how can I show this?

    Like

    Reply

  7. The output I want is a table in a report that shows the sourcecollections ID’s for now but i want it to show the collection names.

    Like

    Reply

    1. Right the column on the far right side should have the collection name and then the collection ID in parenthesis.

      All of the information is there in that SQL query just a matter of manipulating the data around.

      Like

      Reply

  8. I only get the collection name of the colletion source ID like in your screenshot, not the collection name of the source collections ID. Can I send you my output of the sql report?

    I want to get the collection name of source collection not collection dependency. My source collection is 1 collection I want the data of the included collections (source collection),

    How can I change this so that source collections are shown the collection names not from collection ID?

    Collection Source Name – Collection ID – Source Collection – Type of Relationship – Collection Source Name
    D-DC-AM-PRD-Req-Stata 001003CC 001003F6 Include D-DC-AM-PRD-Req-Stata (001003F6) D-DC-AM-PRD-Req-Stata
    D-DC-AM-PRD-Req-Stata 001003CC 00100415 Include D-DC-AM-PRD-Req-Stata (00100415) D-DC-AM-PRD-Req-Stata
    D-DC-AM-PRD-Req-Stata 001003CC 00100416 Include D-DC-AM-PRD-Req-Stata (00100416) D-DC-AM-PRD-Req-Stata
    D-DC-AM-PRD-Req-Stata 001003CC 00100418 Include D-DC-AM-PRD-Req-Stata (00100418) D-DC-AM-PRD-Req-Stata
    D-DC-AM-PRD-Req-Stata 001003CC 00100419 Include D-DC-AM-PRD-Req-Stata (00100419) D-DC-AM-PRD-Req-Stata
    D-DC-AM-PRD-Req-Stata 001003CC SMS00001 Limited To D-DC-AM-PRD-Req-Stata (SMS00001) D-DC-AM-PRD-Req-Stata

    Like

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: