SCCM Collection Relationships Using SQL Queries

 In System Center Configuration Manager (SCCM), collections are used to group systems or devices based on specific criteria for easier management. But how do these collections relate to each other? In this blog post, we’ll explore how to find relationships between collections, such as including, excluding, or limiting systems in source collections using SQL queries in SCCM.

Understanding Collection Relationships in SCCM

SCCM allows administrators to manage collections by creating dependencies between them. These dependencies determine how one collection impacts another. You may have scenarios where one collection is a subset of another (limited), one collection is included in another (include), or one collection excludes another collection (exclude).

SCCM maintains collection dependencies in the vSMS_CollectionDependencies table, which stores the relationship between a source collection and a dependent collection.

Key Columns Involved

• SourceCollectionID: The ID of the collection that is the source of the dependency.
• DependentCollectionID: The ID of the collection that is dependent on the source collection.
• RelationshipType: The type of relationship, where:
• 1 = Limited To
• 2 = Include
• 3 = Exclude

Using SQL to Query Collection Relationships

You can use SQL queries to extract details about collection relationships in SCCM. Below is a SQL query that will help you retrieve the relationships for a specific source collection

SELECT

    v_Collection.name,

    v_Collections.CollectionName AS [Source Collection Name],

    SourceCollectionID,

    CASE

        WHEN vSMS_CollectionDependencies.relationshiptype = 1 THEN 'Limited To ' + SourceCollectionID

        WHEN vSMS_CollectionDependencies.relationshiptype = 2 THEN 'Include ' + SourceCollectionID

        WHEN vSMS_CollectionDependencies.relationshiptype = 3 THEN 'Exclude ' + SourceCollectionID

    END AS "Type of Relationship"

FROM

    v_Collection

JOIN

    vSMS_CollectionDependencies ON vSMS_CollectionDependencies.DependentCollectionID = v_Collection.CollectionID

JOIN

    v_Collections ON v_Collections.SiteID = vSMS_CollectionDependencies.SourceCollectionID

WHERE

    vSMS_CollectionDependencies.SourceCollectionID LIKE 'CollectionID';

Conclusion

Using SQL queries to analyze collection relationships in SCCM can save time and improve your ability to manage your environment efficiently. The query shared in this blog will help you gain insights into how collections are interdependent and ensure that your configuration management policies are applied correctly.