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
2 Comments
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
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