SCCM Query for Empty Collections

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:
http://smsug.ca/blogs/garth_jones/archive/2010/07/01/find-the-collections-which-does-nt-have-members.aspx

Here’s the actual query statement
select
C.Collectionid,
C.Name
from
dbo.v_Collection C
Where
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:

select
C.Collectionid,
C.Name
from
dbo.v_Collection C
Where
C.Collectionid not in (Select Collectionid from dbo.v_FullCollectionMembership) and c.name like '%-Install'

Easy peasy lemon squeezy. 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s