SCCM SQL Query Show Direct Members

If you want to create an overview of all direct members that have been manually added to all collections, you can create a Report containing the SQL query below:

SCCM 2007

Use the SQL statement below:

SELECT coll.Name, crd.CollectionID, crd.rulenameFROM v_CollectionRuleDirect crdINNER JOIN v_Collection coll on crd.collectionID=coll.collectionID

SCCM CB (Current Branch)

Use the SQL statement below:

SELECT coll.Name, coll.CollectionID, fcfm.NameFROM SMS_Collection coll, SMS_FullCollectionMembership fcfmWHERE coll.CollectionID = fcfm.CollectionID AND fcfm.IsDirect = 1