Kace K1000 SQL query for the 'missing' flag on a patch
I can do an advanced search on the security catalogue for individual patches filtered to:
Superseded is NO
Impact is CRITICAL
Operating System is MACINTOSH
Missing is TRUE
Status is ACTIVE
Now if I want to run a sql report manually listing all machines meeting the same criteria, I can't figure out how to find or calculate the 'missing' or 'active' flag. Does anyone have any suggestions of tables or commands required to filter for 'is missing' and 'is active' on a report such as this:
MACHINE M ON M.ID = MS.MACHINE_ID
KBSYS.PATCHLINK_PATCH PP ON PP.UID = MS.PATCHUID
PP.IS_SUPERCEDED = 0 AND PP.IMPACTID = 'Critical' AND M.OS_NAME rlike 'mac' AND PP.IS_APP = 1 AND MS.STATUS != 'Patched'
ORDER BY M.NAME, PP.TITLE
would a filter like
MS.DEPLOY_ATTEMPT_COUNT >= MS.MAX_DEPLOY_ATTEMPT
do it? Or something similar? I need to somehow flag that a computer actually has the application installed, as if it doesn't, I beleive the max_deploy_attempt field will still populate... I don't know for sure though.
There is a setting in KACE to set patches that got superseded will be marked as inactive. This means that you will receive only active patches with your SQL query (if the setting is enabled).
You will find out if a patch is inactive when you join the table PATCHLINK_PATCH_STATUS to your query . 1 is inactive and 0 is active.
The missing flag is the MS.STATUS. If it contains NOTPATCHED, then the patch is missing.Hope this helps.