I was looking at the console other day and found that, there were many collections created in the root folder (device collection) with 0 count. So i looked at the collection properties ,i found empty there (No direct or query based rule).

So i decided to write SQL query to identify the list of collections that have empty results with no query rules (Direct or query based) defined in it.

For this query ,i have used 2 SQL views (v_Collection and v_CollectionRuleQuery ) .

Following is the SQL Code to identify empty collections with no query rule defined ,You can delete these collections to simplify the list of collections displayed when deploying objects as part of maintenance tasks ,unless there is a reason to be in the console.

You can use the following code to create SSRS report as well.

select coll.CollectionID,coll.Name,
case when coll.CollectionType='1' then 'User' else 'Device' end as 'Collection Type'
from v_Collection coll
where coll.collectionid not in (select CRQ.collectionid from v_CollectionRuleQuery CRQ)
and coll.MemberCount=0
group by coll.CollectionID,coll.Name,coll.CollectionType