The following SQL query can be used to query the list of collections that doesn't have any deployments. you can review the number of collections with no deployments and you can further extend the query to exclude certain folders where you use these exception collections for troubleshooting or other purpose. select Col.Name, col.CollectionID, case when col.CollectionType='2' then 'Device Based' when col.CollectionType='1' then 'Used Based' Else 'Others' end as 'Collection Type', col.MemberCount, coll.ObjectPath from dbo.v_Collection Col inner join v_Collections coll on coll.SiteID=col.CollectionID Where Col.CollectionID not in (select CollectionID from dbo.v_Advertisement) order by Col.Name
Thanks for the Query. It is easy to fetch all the No Advertisement collections list with the Member count in SCCM DB.
Along with this can we get the Object path for those collections ?
The blogpost is updated with the SQL query. you can refer this blog post https://eskonr.com/2022/12/sccm-report-list-collections-with-no-deployments/
did you tried for Query which work in sccm 2012 for collection which dont have deployment and advertisement
Did you see this post? http://eskonr.com/2016/12/sccm-configmgr-how-to-find-applications-with-no-deployments-as-part-of-maintenance-tasks/
Hello Eswar, this query is for Sccm 2007 and wont work well on Sccm 2012. Do you have query which will work for 2012??
Yes that one is for SCCM 2007 and in SCCM 2012 ,you have applications added which do not have any advertisements instead they call deployments.
You can tweak the query posted in this blog post http://eskonr.com/2016/12/sccm-configmgr-how-to-find-applications-with-no-deployments-as-part-of-maintenance-tasks/ though it is to identify applications that do not have any deployments. When i get time ,i will look at this requirement.