none
Membership rules for Collection RRS feed

  • Question

  • Hi

    We have many collections and I want to do some cleaning sccm.
    I would like to know if there is a way to see if the collection I want to delete is part of a rule of exclusion or inclusion in the membership rule of a collection.

    thanks

    Tuesday, December 3, 2019 8:01 PM

Answers

  • See https://jordantheitguy.com/2017/10/01/sccm-collection-relationships/ for a nice query to do this.

    Jason | https://home.configmgrftw.com | @jasonsandys

    • Marked as answer by Benoit Cleary Wednesday, December 4, 2019 1:17 PM
    Tuesday, December 3, 2019 8:47 PM
  • Hi,

    The query from Jason's link is very good, I ran it in my lab, it works well. However, when you copy the query to your query editor, the single quotes are not formatted correctly, I have modified it and pasted it below, hope it helps.
    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'

    Regards,
    Allen

    Please remember to mark the replies as answers if they help.

    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    • Marked as answer by Benoit Cleary Wednesday, December 4, 2019 1:17 PM
    Wednesday, December 4, 2019 7:17 AM

All replies

  • See https://jordantheitguy.com/2017/10/01/sccm-collection-relationships/ for a nice query to do this.

    Jason | https://home.configmgrftw.com | @jasonsandys

    • Marked as answer by Benoit Cleary Wednesday, December 4, 2019 1:17 PM
    Tuesday, December 3, 2019 8:47 PM
  • Hi,

    The query from Jason's link is very good, I ran it in my lab, it works well. However, when you copy the query to your query editor, the single quotes are not formatted correctly, I have modified it and pasted it below, hope it helps.
    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'

    Regards,
    Allen

    Please remember to mark the replies as answers if they help.

    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    • Marked as answer by Benoit Cleary Wednesday, December 4, 2019 1:17 PM
    Wednesday, December 4, 2019 7:17 AM
  • thanks exactly what i need
    Wednesday, December 4, 2019 1:17 PM
  • Hi,

    Thank you very much for the update and we're glad the problem is solved now. If you have any questions in future, we warmly welcome you to post in this forum again.

    Have a nice day!

    Regards,
    Allen

    Please remember to mark the replies as answers if they help.

    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Friday, December 6, 2019 7:47 AM