SCCM report list collections with no deployments

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

6 Responses to "SCCM report list collections with no deployments"

  1. 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

    Reply
    1. 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

      Reply

Leave a Reply