KACE Product Support

We think we have resolved the issue of patching in the way that you requested.

Commands are executed by cron when the minute, hour, and month of year fields match the current time, and when at least one of the two day fields (day of month, or day of week) match the current time.

Also see: http://itninja.com/question/silent-uninstall-oracle-8-cilent7140&mpage=1&key=cron&#77314

[Full disclosure. I represent a software vendor.]

Our enterprise job scheduler, JAMS (http://www.jamsscheduler.com/LinuxUNIX.aspx) is designed to address the issues you raise about date calculations. We also offer a Cron converter to make it easy for you convert your existing cron jobs.

The scheduling cannot directly get the sophisticated. You could say every monday or the 21st day of every month, but not exactly what you want.

However, you could do this through labels. It may sound odd at first, but let's say that you had a single label that represented all your target machines. We'll call this label "California".

You could create another smart label that would be based on California except that it would contain some date information. Thus the label would only have members at the right time of the month.

You still set your patching schedule to run every Monday but it will only have target machines every 3rd monday of the month.

Here is what the smart label's SQL would look like:
select MACHINE.*
from MACHINE
JOIN MACHINE_LABEL_JT ML ON ML.MACHINE_ID=MACHINE.ID
JOIN LABEL L ON L.ID=ML.LABEL_ID
WHERE
L.NAME='California'
and DAYOFMONTH>14 and DAYOFMONTH<22

Note that we do not need to worry about it being Monday in the SQL because the patch schedule can do that part for us.

If "California" is also a smart label then make sure that this date-based smart label runs AFTER "California"by setting the order on the smart label configuration screen.

The only drawback would be if you had check-in intervals longer than 3.75 hours. Since a machine might check-in on the Sunday and not get the new label but then the patching schedule on monday clicks off 3.75 hours later.

You could add more date-stuff to the label to compensate. Specifically for 0345h you could have:

blah....
WHERE
L.NAME='California'
/* if tomorrow is Monday, the 15th then label the machine on the 14th */
and( (DAYOFWEEK=1 and DAYOFMONTH=14)
or (DAYOFMONTH>14 and DAYOFMONTH<22))


If for some reason you didn't want to do this in a machine label then you could do it similarly in a patch label.

Hi GillySpy,

Thx for your help. Your exemple will help me to find a way to do it.

Best regards

or under custom schedule
you can enter

45 3 */14-22 * 1

Thanks for the quick reply GillySpy :)

Okay, so, I'm still not 100% clear on what is needed regarding the "assigned label" vs. the L.NAME in the script. Let me explain what I need:

I have machine labels set up with machines attached that I want to use to deploy Microsoft O/S patches
I have a lot of machines that I want to patch, so I set separate labels for every weekday.
I want the patches to run in the 3rd full week of every month (so for monday I want it to look for dates between the 15th and the 21st, Friday would be 19th-25th.
for argument's sake, let's call monday's label patch monday

When I create the smart label, do I put patch monday in the "Assigned Label" or do I create a patch monday2 and assign the smart label to this new label?

Here is my script:

select MACHINE.*
from MACHINE
JOIN MACHINE_LABEL_JT ML ON ML.MACHINE_ID=MACHINE.ID
JOIN LABEL L ON L.ID=ML.LABEL_ID
WHERE
L.NAME='patch monday'
and DAYOFMONTH(CURRENT_DATE)>14 and DAYOFMONTH(CURRENT_DATE)<22

btw, it didn't like just DAYOFMONTH>14, it gave me a mysql error so I had to add (CURRENT_DATE) for it to be accepted...

When I create the smart label, do I put patch monday in the "Assigned Label" or do I create a patch monday2 and assign the smart label to this new label?

Since a cron job cannot use the intersection of the day-of-month and day-of-week (it uses the UNION)
Your options are:
1. put the day of the month in the SQL and use the day of the week on the patch schedule
2. put the day of the month in the schedule and use the day of the week in the label
3. put both in the SQL

I'm not clear on the definition of "Patch Monday". It sounds like option 1 or 3 above. Either way it will work if you use one of these options.

P.S. good call on CURRENT_DATE

"Patch Monday" is my label that includes all of the systems I need to patch on the 3rd Monday of every month. I'll try to make my question a little more clear...

Do I need an additional blank label to assign the SQL smart label to, or should I be assigning to the label that I already have all of the systems attached to?

Does "Patch Monday" contain machines or a date or both? Do you have the SQL?

Since I don't have the SQL then it cannot narrow it down further yet, but you either have scenario 1 or 3. Or need to create a new blank label to get scenario 1 or 3.

I am looking at achieving option 1, yes. Patch Monday currently contains machines only. So, if I read you correctly, I create a new blank label, set the patch schedule on this new label, and attach the smart label with the SQL to the new label (which reads the "Patch Monday" label in the L.NAME='patch monday' line and only populates when the day of the month line matches)

Thanks for the tips, we are looking to do something similar. I thought I would share our solution (so far). Please let me know if you see something that can be done in a better way.

We want to setup the following Patch cycle:


Patch Action Patches Servers Day Hour
Detect OS - All All 3rd and 4th Sunday 12am - 12pm
Deploy OS - All Test 3rd and 4th Sunday 11:30pm - Mon 6am
Deploy OS - Critical Production 1st and 2nd Sunday 12am - 6am & 11:30pm - 6am (monday)
Deploy OS - Recommended Production 2nd Sunday 12am - 6am & 11:30pm - 6am (monday)


Reasoning: We wanted to an automated way to Detect once, then deploy to our test environment. If we find some patches that cause issues we can remove them from the list, otherwise we deploy all patches to all other servers.


In my explanation we will use the Detect Patches on the 3rd sunday.

The Patch Schedule is set to run every Sunday at 12am (later we changed it to 1:30am, see below for reason why).

I have a patch label "Servers - all" which contains all our servers.
I create a smart label "Servers - all - 3rd Sunday" using the following SQL query:


select
MACHINE.*
from
MACHINE
JOIN MACHINE_LABEL_JT ML on ML.MACHINE_ID=MACHINE.ID
JOIN LABEL L on L.ID=ML.LABEL_ID
where
L.NAME='Servers - All'
and
DAYOFMONTH(NOW())>15
and
DAYOFMONTH(NOW())<21

This seems to work but my concern is when a system gets added (or removed) from the label.

From my tests a smart label is updated when a machine checks in. This mean if it is Saturday the 14th the machine will not be in the label. When it is Sunday the 15th, 12:00am the machine is still not in the label. It will only be added to the label when the machine checks-in next. This can be in a few minutes to a few hours. Our hope was to run the Patch 12:00am on Sunday. We could post-pone it until 3am, but how can we guarantee that the machine has checked-in and has been added to the label.

One way is to run a script to force a check-in at 12am every Sunday. According the Default description for the script it should not be run on more than 50 systems at one time. Well we have more the 50 servers. We will have to create some more labels (this is starting to be messy) and force the check-ins at staggered times. EG. Server Group 1 @ 12am, Server Group 2 @ 12:30am and Server Group 3 @ 1am. Then at 1:30am we can run the schedule the patches.

I just wanted to share how I am scheduling patches with the Kbox. So I have it set to run my patches on the 3rd Tuesday of every month and here is how I'm doing it and currently running this on Kbox version 5.2.38773.

Disclaimer: Please note the only way I have found to accomplish this on my own is to use Ticket Rules. By using this you will be modifying your Kbox database and is probably not supported by Dell Kace. If you want to use this please test throughly in a test environment before you use this in production. Please use at your own risk!

So I have a ticket rule that runs on the first day of each month that sets the patching schedule to the correct date for that month. For example, this January my ticket rule ran on the 1st and set the patch schedule date to the 17th. Below is what is included in the Ticket Rule:

Select Query Section:

select distinct IM_CRON.days_of_month, IM_CRON.ID, PATCHLINK_SCHEDULE.DESCRIPTION, PATCHLINK_SCHEDULE.IM_CRON_ID
FROM IM_CRON, PATCHLINK_SCHEDULE
where PATCHLINK_SCHEDULE.DESCRIPTION
= 'Name of Schedule' and IM_CRON.ID = PATCHLINK_SCHEDULE.IM_CRON_ID;

Update Query:

update IM_CRON set DAYS_OF_MONTH=if(dayofweek(curdate())<4,18-dayofweek(curdate()),25-dayofweek(curdate())) where IM_CRON.ID in (<TICKET_IDS>);

Now it is very important to run this ticket rule on the first day of the month so that you will get the correct date every month.

To explain the update query, if you run the ticket rule on the first day of each month you know in cron what value that day of the week is. So the values for the day of the week in cron is 0-6 where Saturday is 0 and Friday is 6.

(Note: I've found conflicting data that says Sunday should be 0 and Saturday is 6 but while testing this I found this to be not true for creating this ticket rule on my Kbox at version 5.2.38773. I don't know if my kbox is "special" or if my current version has the day of week value skewed. If you try this PLEASE test to make sure you get the correct dates.)

If you know what day of the week the 1st falls on you can then compute which date the 3rd tuesday falls on. So the 3rd tuesday of every month will fall between the dates of the 15th - 21st. The important part of the update query that actually figures out the date is this section "if(dayofweek(curdate())<4,18-dayofweek(curdate()),25-dayofweek(curdate()))".

What this essentially says if the cron value of the 1st of each month is less that 4, then the 3rd tuesday of each month is 18 minus the first day's cron value. If the cron value of the first of each month is 4 or greater then the 3rd tuesday is 25 minus the first day's cron value.

If you want to change the day of the week that you patch or even which week that you patch you just have to adjust the 18 and 25 numbers in the query to get what you want. For example if you want the 3rd monday of each month the line would read if(dayofweek(curdate())<4,17-dayofweek(curdate()),24-dayofweek(curdate())).

One of the reasons I went this route instead of the labeling solution present earlier on this topic is that I have several machines that are not inventoried for large stretches of time and wouldn't fall under the label for that patching week. With this solution, you don't have to worry about if the machine was on to be inventoried because your patch schedule will always have the correct date that it needs to run.

Some caveats to using this is you won't be able to calculate the last day of the month with this. Also if you have a day of the week where it can fall on the 29th of the month or later, you will have some months were this patch schedule won't run. For example February most years only has 28 days.

So in this scenario, how the would the 1st Wed be calculated?

Original: darkhawktman
Disclaimer: Please note the only way I have found to accomplish this on my own is to use Ticket Rules...
This can be a great solution as long as you know what you're doing. Also as soon as you modify the patch schedule, your rule's customizations will be overwritten until your rule runs again. You could get around that by having your rule run more often then once a month -- basically keeping the current settings in place.

The original poster had trouble because his label was on machines which can be subject to latency. To avoid this, you could apply the custom date SQL to the patch labels instead. Custom patch smart labels are updated whenever a new patch is downloaded or on-demand by re-saving the smart label.

So this means that the schedule will run against all eligible machines BUT there will not be any work to do unless it is the correct combination of days.

Original: jeremiahmclean
So in this scenario, how the would the 1st Wed be calculated?
Make you schedule "run every wednesday" and then in either the machine or the patch smart label definitions add this to the bottom of the SQL

-- the existing SQL...
and DAYOFMONTH(NOW())<7

ORIGINAL: craig.thatcher

or under custom schedule
you can enter

45 3 */14-22 * 1



hmm..

I tried to have my custom schedule run on the 4th week of each month on Monday at 1 am but no success; it runs on the 1st week of each month on Monday instead:

0 1 */25-31 * 1

Does anyone have any suggestions?