none
Query to find Collection Update Schedules RRS feed

  • Question

  • Our colleval log was going crazy a couple of days ago and bring the SQL db to its knees.  We found out there were almost 1000(!!) collections where a couple of teams had turned on Incremental Updates.  We ran a nice query and then PS script to wipe out all the incremental updates and then rebuild the few that needed it. 

    That problem solved (down to about 50 incrementally updated collections), we're also using this political opportunity to clean up some of the collection update times.  I noticed a decent number of collections which are updated hourly and very few that have that business need.

    noticed this old answer but it's for 2007 and the tables appear to have changed names.  I don't see a CollectionRuleDirect any more (but there is a Collection_Rules):http://social.technet.microsoft.com/Forums/en-US/1944c282-209f-43d1-b88b-08719cd90579/how-to-query-collection-update-schedules?forum=configmgrgeneral

    Is there a query I cound run in 2012 to track down to show hourly/daily/weekly updates for collections? 

    Friday, February 14, 2014 7:35 PM

Answers

  • Hi,

    The Wmi instances do not have the refreshSchedule Property defined so that the script you are using cannot return the value.

    I queried directly from the database and found the value in database is not human readable. If you realy want to query the value out, you need to test multiple times, then make a translation for the value to get the readable output.

    e.g.

    Select collectionname, schedule  from dbo.collections_g 

    order by collectionname

    5C4B9AC000102000  custom occur every 1 minute

    5C4B9AC000104000  custom occur every 2 minute

    5C4B9AC000106000  custom occur every  3 minute    (From the last fourth position, add 2 per minuter starting from 2)

    5C4B9AC000176000  custom occur every  59 minute

    5C4B9AC000100100  custom occur every  1 Hours

    5C4B9AC000100200  custom occur every  2 Hours

    5C4B9AC000101700  custom occur every  23 Hours


    5C4B9AC000100008  custom occur every  1 day

    5C4B9AC000100010  custom occur every  2 days

    5C4B9AC0001000F8  custom occur every  31 days

     You need to find the regular pattern and translate the hex number above to human readable string.


    Juke Chou

    TechNet Community Support


    Tuesday, February 18, 2014 8:21 AM
    Moderator
  • To add the Juke's note. Within the CM12 SDK, it will tell you how to decode the string to a more readable format.

    http://www.enhansoft.com/

    • Marked as answer by FGoodoff Tuesday, February 18, 2014 3:33 PM
    Tuesday, February 18, 2014 1:29 PM
    Moderator

All replies

  • Actually, the query in the above link works but it doesn't give me an interval- just last change time, eval start time, last refresh time, and last member change time.

    So, does anyone know what to query to find the fields listed on the Membership Rules tab of the Collection Properties below the "Schedule a full update on this collection"?  For instance:"Occurs every 7 days effective 10/11/2013 11:27 AM"  Where can I pull those fields, especially the number and the units (i.e. 7 days, 1 hour, etc)?

    • Edited by FGoodoff Friday, February 14, 2014 9:24 PM
    Friday, February 14, 2014 7:40 PM
  • I still haven't been able to track down the fields for those collection update times.  Bump for help from the Monday crowd?
    Monday, February 17, 2014 1:25 PM
  • Hi,

    The Wmi instances do not have the refreshSchedule Property defined so that the script you are using cannot return the value.

    I queried directly from the database and found the value in database is not human readable. If you realy want to query the value out, you need to test multiple times, then make a translation for the value to get the readable output.

    e.g.

    Select collectionname, schedule  from dbo.collections_g 

    order by collectionname

    5C4B9AC000102000  custom occur every 1 minute

    5C4B9AC000104000  custom occur every 2 minute

    5C4B9AC000106000  custom occur every  3 minute    (From the last fourth position, add 2 per minuter starting from 2)

    5C4B9AC000176000  custom occur every  59 minute

    5C4B9AC000100100  custom occur every  1 Hours

    5C4B9AC000100200  custom occur every  2 Hours

    5C4B9AC000101700  custom occur every  23 Hours


    5C4B9AC000100008  custom occur every  1 day

    5C4B9AC000100010  custom occur every  2 days

    5C4B9AC0001000F8  custom occur every  31 days

     You need to find the regular pattern and translate the hex number above to human readable string.


    Juke Chou

    TechNet Community Support


    Tuesday, February 18, 2014 8:21 AM
    Moderator
  • To add the Juke's note. Within the CM12 SDK, it will tell you how to decode the string to a more readable format.

    http://www.enhansoft.com/

    • Marked as answer by FGoodoff Tuesday, February 18, 2014 3:33 PM
    Tuesday, February 18, 2014 1:29 PM
    Moderator
  • Thanks to both for the help.  I could see within the collection from the query and started discerning patterns to see what I needed.

    I didn't know about the SDK and the report there looks quite helpful for this.  I'm going to have to check it out:

    http://cm12sdk.net/?p=1826

    Tuesday, February 18, 2014 3:31 PM