/build/static/layout/Breadcrumb_cap_w.png

Patch Executive Summary Report - KACE SMA version 10.0 or newer only (does not work on 9.1 and older)

Here is a Patch Executive Summary Report for your usage on the K1000 version 10.

This is a screenshot of a PDF report that is generated (obfuscated intentionally):

To install, on your K1000, go to the adminui and click on Reporting -> Reports.  From there, click Choose Action -> New (SQL).  Name these reports as you see fit (I named ours Patch Executive Summary Report, Patch Executive Summary Report - Online Only, and Patch Executive Summary Report - 3rd Party respectively).  Put in a description, select a category (likely Patching).  Then leave Break on columns blank and copy/paste the below SQL into the box (note: Each SQL code box is for a separate report).  Hit Save and then you can run it.

Patch Executive Summary Report:

SELECT 
    M.NAME AS MACHINE_NAME,
    OS_NAME AS WINDOWS_VERSION,
    SUM(MS.DETECT_STATUS = 'PATCHED') AS PATCHED,
    SUM(MS.DETECT_STATUS = 'NOTPATCHED') AS NOTPATCHED,
    FLOOR(ROUND((SUM(MS.DETECT_STATUS = 'PATCHED') / (SUM(MS.DETECT_STATUS = 'PATCHED') + SUM(MS.DETECT_STATUS = 'NOTPATCHED'))) * 100,1)) AS PERCENT_PATCHED
FROM
    PATCH_MACHINE_STATUS MS
        JOIN
    KBSYS.PATCH PP ON (PP.ID = MS.PATCH_ID)
        JOIN
    PATCH_STATUS PPS ON (PPS.PATCH_ID = PP.ID)
        JOIN
    MACHINE M ON (M.ID = MS.MACHINE_ID)
WHERE
    PPS.STATUS = 0 AND PP.IS_SUPERCEDED = 0
        AND PPS.IS_SUPERCEDED = 0
        AND PP.TITLE NOT RLIKE 'Silverlight|Java|Skype|Chrome|Flash|Adobe|7-Zip|Server Management Studio'

GROUP BY MS.MACHINE_ID
ORDER BY PERCENT_PATCHED , M.NAME
Here is the same report but online devices only (Patch Executive Summary Report - Online Only):

SELECT 
    M.NAME AS MACHINE_NAME,
    OS_NAME AS WINDOWS_VERSION,
    SUM(MS.DETECT_STATUS = 'PATCHED') AS PATCHED,
    SUM(MS.DETECT_STATUS = 'NOTPATCHED') AS NOTPATCHED,
    FLOOR(ROUND((SUM(MS.DETECT_STATUS = 'PATCHED') / (SUM(MS.DETECT_STATUS = 'PATCHED') + SUM(MS.DETECT_STATUS = 'NOTPATCHED'))) * 100,1)) AS PERCENT_PATCHED
FROM
    PATCH_MACHINE_STATUS MS
        JOIN
    KBSYS.PATCH PP ON (PP.ID = MS.PATCH_ID)
        JOIN
    PATCH_STATUS PPS ON (PPS.PATCH_ID = PP.ID)
        JOIN
    MACHINE M ON (M.ID = MS.MACHINE_ID)
        JOIN
    KBSYS.SMMP_CONNECTION SC ON (M.KUID = SC.KUID)
WHERE
    PPS.STATUS = 0 AND PP.IS_SUPERCEDED = 0
        AND PPS.IS_SUPERCEDED = 0
        AND PP.TITLE NOT RLIKE 'Silverlight|Java|Skype|Chrome|Flash|Adobe|7-Zip|Server Management Studio'
        AND SC.CLIENT_CONNECTED = 1

GROUP BY MS.MACHINE_ID
ORDER BY PERCENT_PATCHED , M.NAME
Here is the report but with a limiter to only show devices assigned with a specific label (thanks j.catlin@nmscolo.org for the suggestion!).
Note that you need to change the label to one currently in your KACE appliance or that you create for this specific report (bolded below):
SELECT
M.NAME AS MACHINE_NAME,
OS_NAME AS WINDOWS_VERSION,
SUM(MS.DETECT_STATUS = 'PATCHED') AS PATCHED,
SUM(MS.DETECT_STATUS = 'NOTPATCHED') AS NOTPATCHED,
FLOOR(ROUND((SUM(MS.DETECT_STATUS = 'PATCHED') / (SUM(MS.DETECT_STATUS = 'PATCHED') + SUM(MS.DETECT_STATUS = 'NOTPATCHED'))) * 100,1)) AS PERCENT_PATCHED

FROM
PATCH_MACHINE_STATUS MS
JOIN
KBSYS.PATCH PP ON (PP.ID = MS.PATCH_ID)
JOIN
PATCH_STATUS PPS ON (PPS.PATCH_ID = PP.ID)
JOIN
MACHINE M ON (M.ID = MS.MACHINE_ID)
JOIN
KBSYS.SMMP_CONNECTION SC ON (M.KUID = SC.KUID)

JOIN
MACHINE_LABEL_JT on MACHINE_LABEL_JT.MACHINE_ID = M.ID
JOIN
LABEL on LABEL.ID = MACHINE_LABEL_JT.LABEL_ID
WHERE
PPS.STATUS = 0 AND PP.IS_SUPERCEDED = 0
AND PPS.IS_SUPERCEDED = 0
AND PP.TITLE NOT RLIKE 'Silverlight|Java|Skype|Chrome|Flash|Adobe|7-Zip|Server Management Studio'
AND SC.CLIENT_CONNECTED = 1
and LABEL.NAME = "Server Device Patch Test Group"


GROUP BY MS.MACHINE_ID
ORDER BY PERCENT_PATCHED , M.NAME
Finally, here a report that shows 3rd party patching status (Patch Executive Summary Report - 3rd Party):

SELECT 
    M.NAME AS MACHINE_NAME,
    OS_NAME AS WINDOWS_VERSION,
    SUM(MS.DETECT_STATUS = 'PATCHED') AS PATCHED,
    SUM(MS.DETECT_STATUS = 'NOTPATCHED') AS NOTPATCHED,
    FLOOR(ROUND((SUM(MS.DETECT_STATUS = 'PATCHED') / (SUM(MS.DETECT_STATUS = 'PATCHED') + SUM(MS.DETECT_STATUS = 'NOTPATCHED'))) * 100,1)) AS PERCENT_PATCHED
FROM
    PATCH_MACHINE_STATUS MS
        JOIN
    KBSYS.PATCH PP ON (PP.ID = MS.PATCH_ID)
        JOIN
    PATCH_STATUS PPS ON (PPS.PATCH_ID = PP.ID)
        JOIN
    MACHINE M ON (M.ID = MS.MACHINE_ID)
WHERE
    PPS.STATUS = 0 AND PP.IS_SUPERCEDED = 0
        AND PPS.IS_SUPERCEDED = 0
        AND PP.TITLE RLIKE 'Silverlight|Java|Skype|Chrome|Flash|Adobe|7-Zip|Server Management Studio'

GROUP BY MS.MACHINE_ID
ORDER BY PERCENT_PATCHED , M.NAME
Hope this helps!

Ryan

Edited 2019-09-24 to add screenshot example and reorder post for better flow.
Edited 2020-08-10 to add suggested report by j.catlin@nmscolo.org to show only systems that are assigned a specific label.

Comments

  • looks interesting. I'll have to try this out later - jonniipalos 4 years ago
  • Thanks - lama01 4 years ago
  • This small change allows you to specify which patch label you would like to list devices from. Please note you would likely need to change the LABEL.NAME = "Server Device Patch Test Group" line to read something useful to you and team.
    SELECT
    M.NAME AS MACHINE_NAME,
    OS_NAME AS WINDOWS_VERSION,
    SUM(MS.DETECT_STATUS = 'PATCHED') AS PATCHED,
    SUM(MS.DETECT_STATUS = 'NOTPATCHED') AS NOTPATCHED,
    FLOOR(ROUND((SUM(MS.DETECT_STATUS = 'PATCHED') / (SUM(MS.DETECT_STATUS = 'PATCHED') + SUM(MS.DETECT_STATUS = 'NOTPATCHED'))) * 100,1)) AS PERCENT_PATCHED

    FROM
    PATCH_MACHINE_STATUS MS
    JOIN
    KBSYS.PATCH PP ON (PP.ID = MS.PATCH_ID)
    JOIN
    PATCH_STATUS PPS ON (PPS.PATCH_ID = PP.ID)
    JOIN
    MACHINE M ON (M.ID = MS.MACHINE_ID)
    JOIN
    KBSYS.SMMP_CONNECTION SC ON (M.KUID = SC.KUID)

    JOIN
    MACHINE_LABEL_JT on MACHINE_LABEL_JT.MACHINE_ID = M.ID
    JOIN
    LABEL on LABEL.ID = MACHINE_LABEL_JT.LABEL_ID
    WHERE
    PPS.STATUS = 0 AND PP.IS_SUPERCEDED = 0
    AND PPS.IS_SUPERCEDED = 0
    AND PP.TITLE NOT RLIKE 'Silverlight|Java|Skype|Chrome|Flash|Adobe|7-Zip|Server Management Studio'
    AND SC.CLIENT_CONNECTED = 1
    and LABEL.NAME = "Server Device Patch Test Group"


    GROUP BY MS.MACHINE_ID
    ORDER BY PERCENT_PATCHED , M.NAME - j.catlin@nmscolo.org 3 years ago
    • Thanks for sharing! This is great in situations where you need to create individual reporting or want to exclude specific devices from the report. Admittedly since we include all devices, didn't even think to include this. If you are okay with it, I can include this in the blog post (with attribution to you). - RyanTech 3 years ago
      • Sure your welcome to post in the original with my suggestions. I enjoy trying to build from what others have done to help the community. - j.catlin@nmscolo.org 3 years ago
  • This is great! Thank you for sharing. I would say the one thing missing is "last patched date". That would be so handy to have. - awingren 2 years ago
This post is locked

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

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