image I often make use of what I call “Roll-up Collections” in ConfigMgr. These are parent collections that contain sub-collections that specialize the intended membership of the parent collection. An example parent collection might be Texas with sub-collections for San Antonio, Austin, Dallas, Houston, Corpus Christi, etc. The parent collection’s membership, Texas in this case, is determined by the membership of the sub-collections; i.e., everything in the sub-collections should also be in the parent collection.

This is different than a trickle down collection where each sub-collection is limited to the membership of its parent collection and uses a query to “filter” the membership of that parent collection. The end result is similar however.

So when would you use a roll-up collection: typically if you populate the sub-collections using direct membership rules or there are more than a few sub-collections where combining the rules for use in the parent collection is non-trivial.

The following query, when added as a Query Rule to the parent collection, will accomplish the feat of a roll-up collection (where XYZ00123 is the Collection ID of the parent collection):


select SYS.ResourceID,sys.ResourceType, sys.Name,
sys.SMSUniqueIdentifier, sys.ResourceDomainORWorkgroup, sys.Client
from SMS_R_System as sys
where sys.ResourceId in
(select ResourceID  
from SMS_FullCollectionMembership fcm
inner join SMS_CollectToSubCollect csc 
on fcm.CollectionID = csc.subCollectionID 
where csc.parentCollectionID = ‘XYZ00123‘)