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
By Eswar Koneti
6 Comments
Hi Eswar,
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 ?
Regards,
Vicky
Hi,
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/
Thanks,
Eswar
Hello Eswar,
did you tried for Query which work in sccm 2012 for collection which dont have deployment and advertisement
Hi,
Did you see this post? http://eskonr.com/2016/12/sccm-configmgr-how-to-find-applications-with-no-deployments-as-part-of-maintenance-tasks/
Regards,
Eswar
Hello Eswar, this query is for Sccm 2007 and wont work well on Sccm 2012. Do you have query which will work for 2012??
Thanks,
Sandeep
Hi Sandeep,
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.
Regards,
Eswar