/build/static/layout/Breadcrumb_cap_w.png

KACE SQL issue

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
  • 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'))) - rotherpj 10 years ago

Answers (0)

Be the first to answer this question

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

View more:

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ