/build/static/layout/Breadcrumb_cap_w.png

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.


0 Comments   [ + ] Show comments

Answers (1)

Posted by: MGruber 5 years ago
Senior White Belt
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.

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

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