Close Menu
    Facebook X (Twitter) Instagram
    Sunday, June 22
    X (Twitter) LinkedIn
    All about Endpoint Management
    • Home
    All about Endpoint Management
    Home»CM2012»SCCM Configmgr identify count of Direct membership rules ,collection Schedule Refresh Types

    SCCM Configmgr identify count of Direct membership rules ,collection Schedule Refresh Types

    Eswar KonetiBy Eswar KonetiApril 01, 12:10 am2 Mins Read CM2012 4,169 Views
    Share
    Facebook Twitter LinkedIn Reddit

    Collections in Configmgr play a crucial role .If you want to do anything(can be software deployment,OSD ,Client agent settings,Software updates,compliance etc) in configmgr against clients,you a collection .

    Being Configmgr administrator, it is always important to look at collection performance ,if they are scheduled well and do some maintenance like identifying collections that take longer time to update (collection evaluation viewer tool from Configmgr 2012 toolkit) or identifying the collections that update too frequently than expected etc.

    This blog post will assist you to identify collections with count of direct membership rules and type of schedule and other important collection Queries which can be represented in Nice SSRS Report.

    The below SQL Code is for Collections with count of Direct Membership rule and what type of Collection Schedule configured.

    Usually for Collections with Direct membership rule, you really no need to configure any Schedule at all as they are one time created and do not require any update.

    so ,you can simply run this SQL Code ,if the count of Direct membership rule is bigger and if any Schedule configured to get the RID of it.

    Direct membership rule info stored in v_CollectionRuleDirect View ,For more information about SQL Views in Configmgr, refer this Excel spreadsheet

    Schedule can be of anything listed below:

    Scheduled
    Incremental
    Scheduled and Incremental

    SQL Code:

    select coll.CollectionName,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,coll.CollectionName
    order by crd.CollectionID

    SQL Code for All Collections with its Refresh Type:

    Select (Case when RefreshType = 1 then 'Manual'
    when RefreshType = 2 then 'Scheduled'
    when RefreshType = 4 then 'Incremental'
    when RefreshType = 6 then 'Scheduled and Incremental'
    else 'Unknown' end) as RefreshType, count(SiteID) as Collections
    from v_Collections
    group by RefreshType

    image

    Collections configmgr Count of collections Directmembership Rules Incremental Refresh Type SCCM Schedule Types SQL code
    Share. Twitter LinkedIn Email Facebook Reddit

    Related Posts

    Optimize Your Intune Workflow with a Powerful Browser Extension

    March 22, 10:39 am

    Migrate Microsoft 365 Updates from SCCM/MECM to Intune for Co-Managed Devices

    February 11, 9:50 pm

    Investigating Co-Management Issues with Windows Endpoints in SCCM/Intune

    October 26, 10:45 pm

    2 Comments

    1. shokey66 on July 26, 2018 10:45 AM

      would you have a query for: list all the collection that have refresh schedule checked and what is that schedule (5,10,15min, 1 hr , week, month,etc) and how many collection are limited to it
      how to translate this hex?
      https://social.technet.microsoft.com/Forums/en-US/769e8201-18cb-4110-b990-190cbaf996bb/query-to-find-collection-update-schedules?forum=configmanagergeneral

      Reply
      • Eswar Koneti on July 26, 2018 8:30 PM

        You can try the following SQL query to get the collection membership schedule and its properties:

        Select
        CG.CollectionName,
        CG.SITEID AS [Collection ID],
        CASE VC.CollectionType
        WHEN 0 THEN ‘Other’
        WHEN 1 THEN ‘User’
        WHEN 2 THEN ‘Device’
        ELSE ‘Unknown’ END AS CollectionType,
        CG.schedule, case
        WHEN CG.Schedule like ‘0102000’ THEN ‘Every 1 minute’
        WHEN CG.Schedule like ‘010A000’ THEN ‘Every 5 mins’
        WHEN CG.Schedule like ‘0114000’ THEN ‘Every 10 mins’
        WHEN CG.Schedule like ‘011E000’ THEN ‘Every 15 mins’
        WHEN CG.Schedule like ‘0128000’ THEN ‘Every 20 mins’
        WHEN CG.Schedule like ‘0132000’ THEN ‘Every 25 mins’
        WHEN CG.Schedule like ‘013C000’ THEN ‘Every 30 mins’
        WHEN CG.Schedule like ‘0150000’ THEN ‘Every 40 mins’
        WHEN CG.Schedule like ‘015A000’ THEN ‘Every 45 mins’
        WHEN CG.Schedule like ‘0100100’ THEN ‘Every 1 hour’
        WHEN CG.Schedule like ‘0100200’ THEN ‘Every 2 hours’
        WHEN CG.Schedule like ‘0100300’ THEN ‘Every 3 hours’
        WHEN CG.Schedule like ‘0100400’ THEN ‘Every 4 hours’
        WHEN CG.Schedule like ‘0100500’ THEN ‘Every 5 hours’
        WHEN CG.Schedule like ‘0100600’ THEN ‘Every 6 hours’
        WHEN CG.Schedule like ‘0100700’ THEN ‘Every 7 hours’
        WHEN CG.Schedule like ‘0100B00’ THEN ‘Every 11 Hours’
        WHEN CG.Schedule like ‘0100C00’ THEN ‘Every 12 Hours’
        WHEN CG.Schedule like ‘0101000’ THEN ‘Every 16 Hours’
        WHEN CG.Schedule like ‘0100008’ THEN ‘Every 1 days’
        WHEN CG.Schedule like ‘0100010’ THEN ‘Every 2 days’
        WHEN CG.Schedule like ‘0100028’ THEN ‘Every 5 days’
        WHEN CG.Schedule like ‘0100038’ THEN ‘Every 7 Days’
        WHEN CG.Schedule like ‘0192000’ THEN ‘1 week’
        WHEN CG.Schedule like ‘0080000’ THEN ‘Update Once’
        WHEN CG.SChedule = ” THEN ‘Manual’
        END AS [Update Schedule],
        Case VC.RefreshType
        when 1 then ‘Manual’
        when 2 then ‘Scheduled’
        when 4 then ‘Incremental’
        when 6 then ‘Scheduled and Incremental’
        else ‘Unknown’
        end as RefreshType,
        VC.MemberCount,
        (SELECT COUNT(CollectionID) FROM v_CollectionRuleQuery CRQ WHERE CRQ.CollectionID = VC.SiteID) AS ‘RuleQueryCount’,
        (SELECT COUNT(CollectionID) FROM v_CollectionRuleDirect CRD WHERE CRD.CollectionID = VC.SiteID) AS ‘RuleDirectCount’
        from
        dbo.collections_g CG
        left join v_collections VC on VC.SiteID = CG.SiteID
        Where
        –cg.schedule like ‘0100200’
        order by
        CG.Schedule DESC

        Via https://sccmf12twice.com/2018/03/collection-schedule-query/

        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.