Close Menu
    Facebook X (Twitter) Instagram
    Tuesday, May 20
    X (Twitter) LinkedIn
    All about Endpoint Management
    • Home
    All about Endpoint Management
    Home»SCCM 2007»SCCM report list collections with no deployments

    SCCM report list collections with no deployments

    Eswar KonetiBy Eswar KonetiDecember 05, 12:04 pm1 Min Read SCCM 2007 3,869 Views
    Share
    Facebook Twitter LinkedIn Reddit
    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

    Advertisement Collections Configmgr 2007 configmgr report Microsoft SCCM report SCCM 2007 sccm report sccm report collection which does not have any advertisement SCCM Reports SQL System center configuration Manager
    Share. Twitter LinkedIn Email Facebook Reddit

    Related Posts

    Monitoring Endpoint Security Applications with SCCM ConfigMgr SQL

    October 11, 8:48 pm

    SCCM Right Click Tool Managed workloads of co-managed device

    September 25, 6:08 pm

    Efficiently Identify Co-Managed Devices: Techniques and Tools

    January 17, 8:37 pm

    6 Comments

    1. Vignesh on February 3, 2023 5:21 PM

      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
      • Eswar Koneti on March 12, 2023 2:31 AM

        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

        Reply
    2. Amol Thakur on January 12, 2018 12:33 AM

      Hello Eswar,

      did you tried for Query which work in sccm 2012 for collection which dont have deployment and advertisement

      Reply
      • Eswar Koneti on January 20, 2018 2:52 PM

        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

        Reply
    3. San on July 19, 2017 7:15 PM

      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

      Reply
      • Eswar Koneti on July 19, 2017 9:43 PM

        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 ReplyCancel reply

    This site uses Akismet to reduce spam. Learn how your comment data is processed.

    Sign Up

    Get email notifications for new posts.

    Author

    I’m Eswar Koneti ,a tech enthusiast, security advocate, and your guide to Microsoft Intune and Modern Device Management. My goal? To turn complex tech into actionable insights for a streamlined management experience. Let’s navigate this journey together!

    Support

    Awards

    Archives

    © Copyright 2009-2024 Eswar Koneti, All rights reserved.

    Type above and press Enter to search. Press Esc to cancel.