/build/static/layout/Breadcrumb_cap_w.png
04/11/2019 69 views

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.

0 Comments   [ + ] Show comments

Comments


All Answers

0

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.
Answered 04/16/2019 by: MGruber
Senior White Belt