SCCM Collection Schedule Queries.
There is no real readable output of the SCCM Collection schedule for us to understand. What is produced is 16 character string. If you want to really find out what this translated to then you have to look into the SDK for translation. Also, there really are not any good existing reports/queries out there to cover this information via SQL. This is the query I came up with for my customers environment when trying to evaluate their SCCM Collection evaluation problems. Also I will do another blogpost with the sql queries to identify a number of other items to include orphaned collections, SCCM Collection update types, etc… There is a screenshot in the bottom of this post to give a preview of the information.
Let’s show an example.
There schedule produced “29B66B4000100200” is not readable. You have to look up this information in the SDK to start to get an understanding.
The First “7” digits translate to the effective schedule date for example: Effective 6/22/2015 1:10pm
The last “9” digits translate to the actual schedule that is performed for example: Every 2 hours
The code:
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 ‘%000102000’ THEN ‘Every 1 minute’
WHEN CG.Schedule like ‘%00010A000’ THEN ‘Every 5 mins’
WHEN CG.Schedule like ‘%000114000’ THEN ‘Every 10 mins’
WHEN CG.Schedule like ‘%00011E000’ THEN ‘Every 15 mins’
WHEN CG.Schedule like ‘%000128000’ THEN ‘Every 20 mins’
WHEN CG.Schedule like ‘%000132000’ THEN ‘Every 25 mins’
WHEN CG.Schedule like ‘%00013C000’ THEN ‘Every 30 mins’
WHEN CG.Schedule like ‘%000150000’ THEN ‘Every 40 mins’
WHEN CG.Schedule like ‘%00015A000’ THEN ‘Every 45 mins’
WHEN CG.Schedule like ‘%000100100’ THEN ‘Every 1 hour’
WHEN CG.Schedule like ‘%000100200’ THEN ‘Every 2 hours’
WHEN CG.Schedule like ‘%000100300’ THEN ‘Every 3 hours’
WHEN CG.Schedule like ‘%000100400’ THEN ‘Every 4 hours’
WHEN CG.Schedule like ‘%000100500’ THEN ‘Every 5 hours’
WHEN CG.Schedule like ‘%000100600’ THEN ‘Every 6 hours’
WHEN CG.Schedule like ‘%000100700’ THEN ‘Every 7 hours’
WHEN CG.Schedule like ‘%000100B00’ THEN ‘Every 11 Hours’
WHEN CG.Schedule like ‘%000100C00’ THEN ‘Every 12 Hours’
WHEN CG.Schedule like ‘%000101000’ THEN ‘Every 16 Hours’
WHEN CG.Schedule like ‘%000100008’ THEN ‘Every 1 days’
WHEN CG.Schedule like ‘%000100010’ THEN ‘Every 2 days’
WHEN CG.Schedule like ‘%000100028’ THEN ‘Every 5 days’
WHEN CG.Schedule like ‘%000100038’ THEN ‘Every 7 Days’
WHEN CG.Schedule like ‘%000192000’ THEN ‘1 week’
WHEN CG.Schedule like ‘%000080000’ 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
from
dbo.collections_g CG
left join v_collections VC on VC.SiteID = CG.SiteID
–Where CG.CollectionName like ‘%minutes’
order by
CG.Schedule DESC
The results of the query:
NOTE: I did not put a case when statement for every possible outcome, only the ones that exist in my customers environment. If you want to look up what schedule is used for a specific SCCM Collection then modify the where statement to site that specific SCCM Collection name.
Here is the SSRS report: https://sccmf12twice.com/portfolio-item/troubleshooting-collection-evaluation/
I plan on in the near future releasing a revised version where you can see update times over the last 7 days included.
thanks a ton!
It is very useful and it would be great if you share the SSRS report.
Hi That was extraordinarily helpful. I added a few more and sorted how I needed with unknown schedules first then min-hour-day…
Select CG.CollectionName, CG.CollectionID,
CASE CG.CollectionType
WHEN 0 THEN ‘Other’
WHEN 1 THEN ‘User’
WHEN 2 THEN ‘Device’
ELSE ‘Unknown’ END AS CollectionType,
case
WHEN CG.Schedule like ‘%000102000’ THEN ‘ Mins 01’
WHEN CG.Schedule like ‘%000104000’ THEN ‘ Mins 02’
WHEN CG.Schedule like ‘%00010A000’ THEN ‘ Mins 05’
WHEN CG.Schedule like ‘%000114000’ THEN ‘ Mins 10’
WHEN CG.Schedule like ‘%00011E000’ THEN ‘ Mins 15’
WHEN CG.Schedule like ‘%000128000’ THEN ‘ Mins 20’
WHEN CG.Schedule like ‘%000132000’ THEN ‘ Mins 25’
WHEN CG.Schedule like ‘%00013C000’ THEN ‘ Mins 30’
WHEN CG.Schedule like ‘%000150000’ THEN ‘ Mins 40’
WHEN CG.Schedule like ‘%00015A000’ THEN ‘ Mins 45’
WHEN CG.Schedule like ‘%000100100’ THEN ‘ Hours 01’
WHEN CG.Schedule like ‘%000100200’ THEN ‘ Hours 02’
WHEN CG.Schedule like ‘%000100300’ THEN ‘ Hours 03’
WHEN CG.Schedule like ‘%000100400’ THEN ‘ Hours 04’
WHEN CG.Schedule like ‘%000100500’ THEN ‘ Hours 05’
WHEN CG.Schedule like ‘%000100600’ THEN ‘ Hours 06’
WHEN CG.Schedule like ‘%000100700’ THEN ‘ Hours 07’
WHEN CG.Schedule like ‘%000100B00’ THEN ‘ Hours 11’
WHEN CG.Schedule like ‘%000100C00’ THEN ‘ Hours 12’
WHEN CG.Schedule like ‘%008100C00’ THEN ‘ Hours 12’
WHEN CG.Schedule like ‘%000101000’ THEN ‘ Hours 16’
WHEN CG.Schedule like ‘%000100008’ THEN ‘ Days 01’
WHEN CG.Schedule like ‘%000100010’ THEN ‘ Days 02’
WHEN CG.Schedule like ‘%000100018’ THEN ‘ Days 03’
WHEN CG.Schedule like ‘%000100020’ THEN ‘ Days 04’
WHEN CG.Schedule like ‘%000100028’ THEN ‘ Days 05’
WHEN CG.Schedule like ‘%000100038’ THEN ‘ Days 07’
WHEN CG.Schedule like ‘%000192000’ THEN ‘ Weeks 01’
WHEN CG.Schedule like ‘%0001B2000’ THEN ‘ Weeks 01 Tues’
WHEN CG.Schedule like ‘%000080000’ THEN ‘Once’
WHEN CG.SChedule = ” THEN ‘Manual’
ELSE ‘ ‘+CG.SChedule END AS [**SCHEDULE**],
Case CG.RefreshType
when 1 then ‘Manual’
when 2 then ‘Scheduled’
when 4 then ‘Incremental’
when 6 then ‘Scheduled and Incremental’
else ‘Unknown’ end as RefreshType,
cg.MemberCount, Cast(cg.LastChangeTime as DATE) as LastChangeTime
from V_collections CG
order by [**Schedule**], RefreshType Desc