locked
SCCM Query for list of Collection with Direct membership RRS feed

  • Question

  • Hi All,

    I wanted to have a query which should list out all collection configured with Direct Membership rule and would like to see following columns

    Column Name,Collection ID,# of Members, update type, Update schedule, updateschedule type, Update internal

    Thanks in Advance

    Wednesday, March 30, 2016 12:15 PM

Answers

  • Direct membership rule information is stored in view: v_CollectionRuleDirect

    Try this SQL Query:

    select crd.CollectionID,COUNT(crd.RuleName) [Direct rules],

    Case when coll.RefreshType = 1 then 'Manual'

    when coll.RefreshType = 2 then 'Scheduled'

    when coll.RefreshType = 4 then 'Incremental'

    when coll.RefreshType = 6 then 'Scheduled and Incremental'

    else 'Unknown' end as RefreshType

    from v_CollectionRuleDirect  CRD

    inner join v_Collections Coll on CRD.collectionID=coll.siteid

    Group by crd.CollectionID,coll.RefreshType

    order by crd.CollectionID

    To customize more ,you can download the SQL views for Configmgr 2012 Documentation from here https://gallery.technet.microsoft.com/SCCM-Configmgr-2012-R2-SQL-5fefdd3b


    Eswar Koneti | Configmgr Blog: www.eskonr.com | Linkedin: Eswar Koneti | Twitter: eskonr


    Thursday, March 31, 2016 8:32 AM

All replies

  • Direct membership rule information is stored in view: v_CollectionRuleDirect

    Try this SQL Query:

    select crd.CollectionID,COUNT(crd.RuleName) [Direct rules],

    Case when coll.RefreshType = 1 then 'Manual'

    when coll.RefreshType = 2 then 'Scheduled'

    when coll.RefreshType = 4 then 'Incremental'

    when coll.RefreshType = 6 then 'Scheduled and Incremental'

    else 'Unknown' end as RefreshType

    from v_CollectionRuleDirect  CRD

    inner join v_Collections Coll on CRD.collectionID=coll.siteid

    Group by crd.CollectionID,coll.RefreshType

    order by crd.CollectionID

    To customize more ,you can download the SQL views for Configmgr 2012 Documentation from here https://gallery.technet.microsoft.com/SCCM-Configmgr-2012-R2-SQL-5fefdd3b


    Eswar Koneti | Configmgr Blog: www.eskonr.com | Linkedin: Eswar Koneti | Twitter: eskonr


    Thursday, March 31, 2016 8:32 AM
  • Dear Sir,

        There's no need to list the update type, update schedule, etc about direct rule because the direct rule collections will never update automatically since they been created. 

    Best regards,

    Jimmy


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.


    Thursday, March 31, 2016 9:01 AM
  • See if our report can help you :

    https://www.systemcenterdudes.com/sccm-2012-collections-report/


    Benoit Lecours | Blog: System Center Dudes

    Thursday, March 31, 2016 11:25 AM