SQL Query to Get the current collection evaluation schedule
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
order by
CG.Schedule DESC
Powershell Script to update the evaluation schedule
# site code.
$sitecode = '123'
# name of server hosting the sms provider.
$provider = 'ServerName'
# create a recuring interval token with a cycle of x days.
# the start time will be randomised, but always on the hour.
function new-token($days = 1) {
$class = gwmi -list -name root\sms\site_$sitecode -class sms_st_recurinterval -comp $provider
$interval = $class.createinstance()
$interval.dayspan = $days
$interval.starttime = get-date (get-date '1/1/2016').addhours((get-random -max 24)) -format yyyyMMddHHmmss.000000+***
return $interval
}
# get the names of all collections enabled for incremental updates.
function get-incremental() {
$collections = @()
gwmi -name root\sms\site_$sitecode -class sms_collection -comp $provider | %{
$collection = [wmi]$_.__path
if ($collection.refreshtype -band 4 -and $collection.collectionid -notlike 'sms*') {
$collections += $collection.name
}
}
return $collections
}
# configure the refresh cycle for an array of collections.
# set $type to 2 for periodic refresh only, and 6 for incremental and periodic.
# set $days to the number days between each periodic refresh.
function set-schedule([array]$collections, $type, $days) {
$collections | %{
if (! ($collection = gwmi -name root\sms\site_$sitecode -class sms_collection -comp $provider -filter "name = '$_'")) { return }
$collection.refreshtype = $type
$collection.refreshschedule = new-token $days
#$collection.psbase()
$collection.put() | out-null
}
}
# disable incremental updates.
# i.e. enable periodic updates only, with a refresh cycle of 1 day.
function disable-incremental([array]$collections) {
set-schedule $collections 2 7
}
# enable incremental updates.
# i.e. enable incremental and periodic updates, with a refresh cycle of 7 days.
function enable-incremental([array]$collections) {
set-schedule $collections 6 7
}
#To retrieve the name of all collections enabled for incremental updates:
#get-incremental
#To disable incremental updates on all collections listed in a file named disable.txt, and enable periodic updates with a daily cycle:
disable-incremental (get-content "C:\Temp\Collection.txt")
#To enable incremental and periodic updates on all collections listed in a file named disable.txt, with a weekly periodic refresh cycle:
#enable-incremental (get-content enable.txt)
