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:
SELECT
M.NAME,
M.USER_LOGGED,
PP.IDENTIFIER,
PP.TITLE,
PP.IMPACTID,
PP.IS_SUPERCEDED,
PP.IS_APP,
M.OS_Name,
MS.STATUS,
PP.REBOOT,
MS.DEPLOY_ATTEMPT_COUNT,
MS.MAX_DEPLOY_ATTEMPT
FROM
PATCHLINK_MACHINE_STATUS MS
JOIN
MACHINE M ON M.ID = MS.MACHINE_ID
JOIN
KBSYS.PATCHLINK_PATCH PP ON PP.UID = MS.PATCHUID
WHERE
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.
Answers (1)
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.Comments:
-
Thanks for the response MGruber. (Apologies for the late reply!)
I do have the flag to only grab active patches for the catalogue. However, it still seemed to return results of not patched for machines that didn't have the product installed. I discovered the PATCH_MACHINE_STATUS.PRODUCT_INSTALLED and PATCH_MACHINE_STATUS.PATCH_INSTALLED fields though and thought I sorted what it was I was after... But now I think those tables are OS specific as the query I have now is only returning Windows OSs... Though there could still be something up with my query... It seems to time out when I run it on MySQL workbench without any filters, but runs when filtering shrinks it down a bit I guess. Here is what I'm working with now...
SELECT DISTINCT
M.NAME AS ComputerName,
M.OS_NAME,
PP.TITLE AS DISPLAY_NAME,
PP.IMPACTID,
MS.STATUS,
PMS.PRODUCT_INSTALLED,
PMS.PATCH_INSTALLED,
PP.IS_SUPERCEDED,
PP.IS_APP,
IP,
M.USER_LOGGED AS USER_LOGGED
FROM
PATCHLINK_MACHINE_STATUS MS
JOIN
KBSYS.PATCHLINK_PATCH PP ON PP.UID = MS.PATCHUID
JOIN
MACHINE M ON M.ID = MS.MACHINE_ID
JOIN
PATCH_MACHINE_STATUS PMS ON PMS.MACHINE_ID = M.ID
WHERE
MS.STATUS = 'NOTPATCHED'
AND PMS.PRODUCT_INSTALLED = 1
AND PMS.PATCH_INSTALLED = 0
AND PP.IS_SUPERCEDED = 0
AND PP.IMPACTID = 'Critical'
AND PP.IS_APP = 0
ORDER BY M.NAME, PP.TITLE - kpm8 5 years ago
