I just came across the need to query SCCM for collections that are empty as digging through queries in the console I could not find a suitable solution. Luck would have it that a quick Google search away was the following:
Here’s the actual query statement
C.Collectionid not in (Select Collectionid from dbo.v_FullCollectionMembership)
Pretty basic however if your even remotely familiar with SQL you can add additional critera to weed out collections you know will be empty and only look at the ones you truly care about.
Example: In our SCCM we have multiple layers of collections with the parent collections being empty (with no “update this collection” schedule of course!)
Lets pick on Adobe. For Acrobat Reader we have a parent collection called Adobe, a sub collection called Adobe_AcrobatReader and the finally a 3rd level collection called Adobe_AcrobatReader_9.3_R01-Install.
The query above returns all 3 collections when really all I care about is the 3rd level collection. The other two collections are empty by design.
Quite simply I add another criteria to my query to only pull back the “Install” collections like so:
C.Collectionid not in (Select Collectionid from dbo.v_FullCollectionMembership) and c.name like '%-Install'
Easy peasy lemon squeezy. 🙂