I was trying on the console different day and located that, there have been many collections created within the root folder (device collection) with 0 rely. So i regarded on the assortment properties ,i discovered empty there (No direct or question primarily based rule).
So i made a decision to put in writing SQL question to determine the listing of collections which have empty outcomes with no question guidelines (Direct or question based) outlined in it.
For this question ,i’ve used 2 SQL views (v_Collection and v_CollectionRuleQuery ) .
For full listing of SQL views that exist in SCCM Configmgr ,please refer https://gallery.technet.microsoft.com/SCCM-Configmgr-2012-R2-SQL-5fefdd3b .
Following is the SQL Code to determine empty collections with no question rule outlined ,would possibly provide assist to to determine such collections for clean-up until there might be cause to be there in SCCM console.
You can use the next code to create SSRS report as well.
case when coll.CollectionType=’1′ then ‘User’ else ‘Device’ finish as ‘Collection Type’
from v_Collection coll
where coll.collectionid not in (select CRQ.collectionid from v_CollectionRuleQuery CRQ)
group by coll.CollectionID,coll.Name,coll.CollectionType
Hope it helps!
Please check this great service at: http://www.test-net.org/services/port-check/ or visit FREE SERVICES menu