I am working on configuring both reboots and patching.  I have a patch label name set in the registry which is pulled from software inventory.  Reboot label names are also set in the registry.  Both labels should only be visible on the day of the patching or reboot.

I want to exclude the reboot label from being applied on days where patching will occur.  For example, the smart label to apply the patch label to a server that gets patched every 4th Thurday is

select MACHINE.*, C.CLIENT_CONNECTED, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
                       UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_SECONDS
                  from ORG1.MACHINE
                  LEFT JOIN KBSYS.KUID_ORGANIZATION O ON O.KUID=MACHINE.KUID LEFT JOIN KBSYS.SMMP_CONNECTION C ON C.KUID = MACHINE.KUID AND O.ORGANIZATION_ID = 1
                 where ((  (1  in (select 1 from ORG1.MACHINE_CUSTOM_INVENTORY where MACHINE.ID = MACHINE_CUSTOM_INVENTORY.ID and MACHINE_CUSTOM_INVENTORY.SOFTWARE_ID = 382 and MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE = 'PATCH_4TH_THU_9:00PM')) )
AND DAYOFMONTH(CURRENT_DATE)>21 AND dayname(curdate())='THURSDAY')

The reboot smart label has to be smart enough to not apply when the patch label is present.  I have a section in the sql statement that should exclude the reboot patch from being applied if there is a visible label containing the word PATCH.

select MACHINE.*, C.CLIENT_CONNECTED, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
                       UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_SECONDS
                  from ORG1.MACHINE
                  LEFT JOIN KBSYS.KUID_ORGANIZATION O ON O.KUID=MACHINE.KUID LEFT JOIN KBSYS.SMMP_CONNECTION C ON C.KUID = MACHINE.KUID AND O.ORGANIZATION_ID = 1
                 where (((( (1  in (select 1 from ORG1.MACHINE_CUSTOM_INVENTORY where MACHINE.ID = MACHINE_CUSTOM_INVENTORY.ID and MACHINE_CUSTOM_INVENTORY.SOFTWARE_ID = 156 and MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE LIKE '%REBOOT_4TH_THU_9:00PM%')) ) AND (1 NOT in (select 1 from ORG1.LABEL, ORG1.MACHINE_LABEL_JT where MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID and MACHINE_LABEL_JT.LABEL_ID = LABEL.ID and LABEL.NAME like 'PATCH%')) ) AND (DAYOFMONTH(CURRENT_DATE)>21 AND dayname(curdate())='THURSDAY')))

The statement works perfectly when i paste it into MYSql workbench, but the server has both labels visible in KACE.  Any help on this would be appreciated.

1 Comment   [ + ] Show Comment

Comments

  • Correction. The code i pasted in did not account for hidden labels. The original posting was missing and LABEL.TYPE != 'hidden'

    select MACHINE.*, C.CLIENT_CONNECTED, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
    UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_SECONDS
    from ORG1.MACHINE
    LEFT JOIN KBSYS.KUID_ORGANIZATION O ON O.KUID=MACHINE.KUID LEFT JOIN KBSYS.SMMP_CONNECTION C ON C.KUID = MACHINE.KUID AND O.ORGANIZATION_ID = 1
    where (((( (1 in (select 1 from ORG1.MACHINE_CUSTOM_INVENTORY where MACHINE.ID = MACHINE_CUSTOM_INVENTORY.ID and MACHINE_CUSTOM_INVENTORY.SOFTWARE_ID = 156 and MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE LIKE '%REBOOT_4TH_THU_9:00PM%')) ) AND (1 NOT in (select 1 from ORG1.LABEL, ORG1.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 NOT like '%PATCH%')) ) AND (DAYOFMONTH(CURRENT_DATE)>21 AND dayname(curdate())='THURSDAY')))
Please log in to comment

There are no answers at this time

Answers

Answer this question or Comment on this question for clarity
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share