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

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

2 Responses to "SCCM Configmgr identify count of Direct membership rules ,collection Schedule Refresh Types"

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