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.