In this article we will show you how to create a more advanced schedule for your patching runs, allowing you to run a patch for a particular weekday each month .
KACE by default does not allow you to set a patch schedule for a particular weekday each month, to get around this issue we have created a smart label that will only pull a label on a selected day, although the patching schedule will run every week on that day, the label associated with the schedule will be empty at all times except for the desired patching day, see below for the smart label SQL.
<---Start of SQL ---->
SELECT
"0" as Week_Day, #Mon - 0, Tue - 1, Wed - 2, Thu - 3, Fri - 4, Sat - 5, Sun - 6
"1" as Week_Number, #Which Week in the Month 1-4
"M_Indigo test group" as Label_Name, #Name of Machine Label to include
MACHINE.NAME AS SYSTEM_NAME,
SYSTEM_DESCRIPTION,
MACHINE.IP,
MACHINE.MAC,
MACHINE.ID as TOPIC_ID
FROM
MACHINE
HAVING ((( exists (select 1 from LABEL, MACHINE_LABEL_JT where MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID AND MACHINE_LABEL_JT.LABEL_ID = LABEL.ID AND LABEL.TYPE <> 'hidden' and LABEL.NAME = Label_Name)) )) and CURRENT_DATE = (DATE_SUB( DATE_ADD( DATE_SUB( CURRENT_DATE, INTERVAL DAYOFMONTH(CURRENT_DATE) - 1 DAY), INTERVAL Week_Number WEEK), INTERVAL ( 7 - ((Week_Day - ((DAYOFWEEK(DATE_SUB( CURRENT_DATE, INTERVAL DAYOFMONTH(CURRENT_DATE) - 1 DAY)) - 2) % 7) + 7) % 7) ) DAY))
<---End of SQL ---->
This SQl will do two things, first of all it will pull in another label, this can be either a basic static label, or another smart label containing the devices you wish to be patched, to add which label will be pulled in, change the text on the third line which is currently set to “M_Indigo test group”. The second part sets when the SQL will pull in the label, the first line sets the variable for the weekday, starting at “0” for Monday, and ending with “6” for sunday, the second line sets the week of the month “1 – 4”.
Then simply add this custom smart label into one of your patching schedules and your done. bear in mind, you will need to duplicate and edit the label for each seperate patch run as you will want to change the label associated with the custom smart label.
I tested this and it need indeed mark device with that the designated label, but how will it stay attached to the label? I was thinking the next day it would no longer be there, but it still is.
Correction to previous post…
I tested this and it need indeed mark the device with the designated label, but how long will it stay attached to the label? I was thinking the next day it would no longer be there, but it still is.
Remember Device labels get device’s allocated to the label when a device agent checks in.
So on the day in question, devices which are in the sub label will be allocated to the label, but will be removed when they check in and don’t meet the criteria, which in the example is if its the wrong week/day.