/build/static/layout/Breadcrumb_cap_w.png

K1000 Reports - Patching Reports for Completion by Patch, Machine & Vendor using Labels

K1000 Reports - Patching Reports for Completion by Patch, Machine & Vendor using Labels
______________________________________

This is a set of patching completion reports I created in helping a user with this question...
http://www.itninja.com/question/patch-report-for-critical-patches

...and after seeing sklauminzer's excellent blog here (which I redid & tweaked to add label support):
http://www.itninja.com/blog/view/report-of-critical-paths-deployed-by-vendor

This basically takes the completion idea and runs it across several variations, along with the ability to restrict on a label (or labels):

Concise listing of completion rate for all active critical patches by machine.
Detailed listing of completion rate for all active critical patches by machine.
Completion rate for all active critical patches.
Completion rate for all active critical patches grouped by vendor.

Just be aware that as these are doing some major calculations across thousands of patches and joining on multiple tables, they can take several seconds to run.

Hope these help somebody out there!  ^_^

John
______________________________________

Notes:
________________

If you want to change the label being targeted in any of these reports, just change this line:
AND L.NAME = 'server'

So it has your label name (instead of 'server').  If you want to target multiple labels, use this instead:
AND L.NAME rlike 'label1|label2|label3'
________________

To restrict listing to ## days, add this line to the WHERE statements and change the number before DAY.

For example this will restrict to the last 60 days of patches:
AND RELEASEDATE > DATE_SUB(NOW(),INTERVAL 60 DAY)
______________________________________
______________________________________

*Title*
Patch listing completion rate by machine - concise (server)

*Category*
Patching - Completion (Custom)

*Description*
Concise listing of completion rate for all active critical patches by machine (servers).

*SQL Query*
SELECT M.NAME AS MACHINE_NAME,
OS_NAME AS WINDOWS_VERSION,
SUM(MS.STATUS='PATCHED') AS PATCHED,
SUM(MS.STATUS='NOTPATCHED') AS NOTPATCHED,
ROUND((SUM(MS.STATUS='PATCHED')/(SUM(MS.STATUS='PATCHED')
  +SUM(MS.STATUS='NOTPATCHED')))*100,0) AS PERCENT_PATCHED
FROM PATCHLINK_MACHINE_STATUS MS
JOIN KBSYS.PATCHLINK_PATCH PP ON (PP.UID = MS.PATCHUID)
JOIN PATCHLINK_PATCH_STATUS PPS ON (PPS.PATCHUID = PP.UID)
JOIN MACHINE M ON (M.ID = MS.MACHINE_ID)
JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
JOIN LABEL L ON (ML.LABEL_ID = L.ID)
WHERE PP.IMPACTID = 'Critical'
AND PPS.STATUS = 0
AND PP.IS_SUPERCEDED = 0
AND L.NAME = 'server'
GROUP BY M.NAME
ORDER BY PERCENT_PATCHED, M.NAME
______________________________________

Example Output:

Title: Patch listing completion rate by machine - concise (server)
Description: Concise listing of completion rate for all active critical patches by machine (servers).
Category: Patching - Completion (Custom)
Server Hostname: kbox.company.net
Generated: 2012/10/31 14:20:30

#   Machine Name  Windows Version   Patched  Notpatched  Percent Patched  
 1  pgh-test      Microsoft(R)...       119          40               75  
...
10  gso-file      Microsoft(R)...       167           4               98  
...
28  pgh-net       Microsoft(R)...       170           0              100  
29  pgh-print     Microsoft Wi...       124           0              100  
etc...
______________________________________
______________________________________

*Title*
Patch listing completion rate by machine - detailed (server)

*Category*
Patching - Completion (Custom)

*Description*
Detailed listing of completion rate for all active critical patches by machine (servers).

*SQL Query*
SELECT M.NAME AS MACHINE_NAME,
OS_NAME AS WINDOWS_VERSION,
PP.TITLE AS PATCH_NAME,
SUM(MS.STATUS='PATCHED') AS PATCHED,
SUM(MS.STATUS='NOTPATCHED') AS NOTPATCHED,
ROUND((SUM(MS.STATUS='PATCHED')/(SUM(MS.STATUS='PATCHED')
  +SUM(MS.STATUS='NOTPATCHED')))*100,0) AS PERCENT_PATCHED,
DATE_FORMAT(PP.RELEASEDATE, '%Y-%m-%d') AS RELEASED
FROM PATCHLINK_MACHINE_STATUS MS
JOIN KBSYS.PATCHLINK_PATCH PP ON (PP.UID = MS.PATCHUID)
JOIN PATCHLINK_PATCH_STATUS PPS ON (PPS.PATCHUID = PP.UID)
JOIN MACHINE M ON (M.ID = MS.MACHINE_ID)
JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
JOIN LABEL L ON (ML.LABEL_ID = L.ID)
WHERE PP.IMPACTID = 'Critical'
AND PPS.STATUS = 0
AND PP.IS_SUPERCEDED = 0
AND L.NAME = 'server'
GROUP BY M.NAME, PATCH_NAME
ORDER BY M.NAME, PP.RELEASEDATE, PP.TITLE

*Break on Columns*
MACHINE_NAME
______________________________________

Example Output:

Title: Patch listing completion rate by machine - detailed (server)
Description: Detailed listing of completion rate for all active critical patches by machine (servers).
Category: Patching - Completion (Custom)
Server Hostname: kbox.company.net
Generated: 2012/10/31 14:23:35

#     Windows Version  Patch Name         Patched  Notpatched  Percent Patched     Released    
115 of Machine Name: pgh-back
   1  Microsoft®...    MS06-061 MSX...          1           0              100   2006-10-13  
   2  Microsoft®...    MS 921896 Mic...         1           0              100   2007-02-18  
   3  Microsoft®...    MS07-028 Sec...          1           0              100   2007-05-08  
   4  Microsoft®...    MS 942288 Win...         1           0              100   2008-06-02  
etc...
______________________________________
______________________________________

*Title*
Patch listing completion rate by patch (server)

*Category*
Patching - Completion (Custom)

*Description*
Completion rate for all active critical patches (servers).

*SQL Select Statement*
SELECT P.PATCH_NAME, P.PATCHED, P.NOTPATCHED,
P.PERCENT_PATCHED, P.RELEASED
FROM (SELECT PP.TITLE AS PATCH_NAME,
SUM(MS.STATUS='PATCHED') AS PATCHED,
SUM(MS.STATUS='NOTPATCHED') AS NOTPATCHED,
IFNULL(ROUND((SUM(MS.STATUS='PATCHED')/(SUM(MS.STATUS='PATCHED')
 +SUM(MS.STATUS='NOTPATCHED')))*100,0), 0) AS PERCENT_PATCHED,
DATE_FORMAT(PP.RELEASEDATE, '%Y-%m-%d') AS RELEASED
FROM PATCHLINK_MACHINE_STATUS MS
JOIN KBSYS.PATCHLINK_PATCH PP ON PP.UID = MS.PATCHUID
JOIN PATCHLINK_PATCH_STATUS PPS ON PPS.PATCHUID = PP.UID
JOIN MACHINE M ON M.ID = MS.MACHINE_ID
JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
JOIN LABEL L ON (ML.LABEL_ID = L.ID)
WHERE PP.IMPACTID = 'Critical'
AND PPS.STATUS = 0
AND PP.IS_SUPERCEDED = 0
AND L.NAME = 'server'
GROUP BY PP.TITLE) P
WHERE P.PERCENT_PATCHED != 0
ORDER BY P.RELEASED desc, P.PATCH_NAME
______________________________________

Example Output:

Title: Patch listing completion rate by patch (server)
Description: Completion rate for all active critical patches (servers)
Category: Patching - Completion (Custom)
Server Hostname: kbox.company.net
Generated: 2012/10/31 14:26:28

#    Patch Name                 Patched  Notpatched  Percent Patched     Released    
  1  MS12-054 Security Upd...        12           0              100   2012-10-09  
  2  MS12-054 Security Upd...         3           3               50   2012-10-09  
  3  MS12-054 Security Upd...         1           4               20   2012-10-09  
  4  MS12-054 Security Upd...         1           0              100   2012-10-09  
etc...
______________________________________
______________________________________

*Title*
Patch listing completion rate by vendor (server)

*Category*
Patching - Completion (Custom)

*Description*
Completion rate for all active critical patches grouped by vendor (servers).

*SQL Select Statement*
Select PP.VENDOR AS 'Critical Patches by Vendor',
COUNT(CASE WHEN MS.STATUS='PATCHED' THEN MS.MACHINE_ID END) as PATCHED,
COUNT(CASE WHEN MS.STATUS='NOTPATCHED' THEN MS.MACHINE_ID END) as UNPATCHED,
ROUND((SUM(MS.STATUS='PATCHED')/(SUM(MS.STATUS='PATCHED')
  +SUM(MS.STATUS='NOTPATCHED')))*100,0) AS PERCENT_PATCHED
FROM PATCHLINK_MACHINE_STATUS MS
JOIN KBSYS.PATCHLINK_PATCH PP ON (PP.UID = MS.PATCHUID)
JOIN PATCHLINK_PATCH_STATUS PPS ON (PPS.PATCHUID = PP.UID)
JOIN MACHINE M ON (M.ID = MS.MACHINE_ID)
JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
JOIN LABEL L ON (ML.LABEL_ID = L.ID)
WHERE PP.IMPACTID = "Critical"
AND PP.IS_SUPERCEDED = 0
AND PPS.STATUS = 0
AND L.NAME = 'server'
GROUP BY PP.VENDOR
order by PP.VENDOR
______________________________________

Title: Patch listing completion rate by vendor (server)
Description: Completion rate for all active critical patches grouped by vendor (servers).
Category: Patching - Completion (Custom)
Server Hostname: kbox.company.net
Generated: 2012/10/31 14:37:43

#  Critical Patches By Vendor  Patched  Unpatched  Percent Patched  
1  Adobe Systems, Inc                1          0              100  
2  Microsoft Corp.                4007        170               96  


Comments

  • Great stuff, Thanks for sharing - jdornan 11 years ago
  • No problem at all, happy to help! ^~^

    John - jverbosk 11 years ago
  • John,

    You're the man! Thanks! After you've "crossed over", I'll probably hammer you with more Patching Report requests. r2

    Ron Colson
    KACE Koach - ronco 11 years ago
  • Thanks so much... these are great! - jdrouillard 9 years ago
  • I ran the first report and it just goes into an infinite loop while the report is queued. Left it for an hour and no report generates. - emarkum 9 years ago
    • emarkum - This is most likely due to the label filtering line. If you comment it out like this, it should work fine:

      -- AND L.NAME = 'server'

      Also, always be sure to test SQL queries outside of the K1000 first. If you aren't sure how to do this, please refer to the SQL Primer blog post I wrote a while back. - jverbosk 9 years ago
      • jverbosk - Yep. That fixed my issues! Thank you. Oversight on my end. Report is what I needed. I am curious as to whether you can take the patched and not patched number and create a total patches column? Thanks again for your assistance. - emarkum 9 years ago
      • emarkum - Sure, just insert this line after the SUM(MS.STATUS='NOTPATCHED') AS NOTPATCHED, line:



        SUM(MS.STATUS='PATCHED') + SUM(MS.STATUS='NOTPATCHED') AS TOTALPATCHED, - jverbosk 9 years ago
  • This is great, but the date is not working. When I add the and statement, it returns nothing. - annien1 9 years ago
    • annien - Did you add the date filtering statement to the correct location (i.e. before the GROUP BY statement)? This is working fine here:

      SELECT M.NAME AS MACHINE_NAME,
      OS_NAME AS WINDOWS_VERSION,
      SUM(MS.STATUS='PATCHED') AS PATCHED,
      SUM(MS.STATUS='NOTPATCHED') AS NOTPATCHED,
      ROUND((SUM(MS.STATUS='PATCHED')/(SUM(MS.STATUS='PATCHED')
      +SUM(MS.STATUS='NOTPATCHED')))*100,0) AS PERCENT_PATCHED
      FROM PATCHLINK_MACHINE_STATUS MS
      JOIN KBSYS.PATCHLINK_PATCH PP ON (PP.UID = MS.PATCHUID)
      JOIN PATCHLINK_PATCH_STATUS PPS ON (PPS.PATCHUID = PP.UID)
      JOIN MACHINE M ON (M.ID = MS.MACHINE_ID)
      JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
      JOIN LABEL L ON (ML.LABEL_ID = L.ID)
      WHERE PP.IMPACTID = 'Critical'
      AND PPS.STATUS = 0
      AND PP.IS_SUPERCEDED = 0
      -- AND L.NAME = 'server'
      AND RELEASEDATE > DATE_SUB(NOW(),INTERVAL 60 DAY)
      GROUP BY M.NAME
      ORDER BY PERCENT_PATCHED, M.NAME - jverbosk 9 years ago
      • Certain I'm doing something wrong, cause now I don't get any results. SELECT M.NAME AS MACHINE_NAME, OS_NAME AS WINDOWS_VERSION, SUM(MS.STATUS='PATCHED') AS PATCHED, SUM(MS.STATUS='NOTPATCHED') AS NOTPATCHED, ROUND((SUM(MS.STATUS='PATCHED')/(SUM(MS.STATUS='PATCHED') +SUM(MS.STATUS='NOTPATCHED')))*100,0) AS PERCENT_PATCHED FROM PATCHLINK_MACHINE_STATUS MS JOIN KBSYS.PATCHLINK_PATCH PP ON (PP.UID = MS.PATCHUID) JOIN PATCHLINK_PATCH_STATUS PPS ON (PPS.PATCHUID = PP.UID) JOIN MACHINE M ON (M.ID = MS.MACHINE_ID) JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID) JOIN LABEL L ON (ML.LABEL_ID = L.ID) WHERE PP.IMPACTID = 'Critical' AND PPS.STATUS = 0 AND PP.IS_SUPERCEDED = 0 -- AND L.NAME = 'Patches - Workstations Agency Wide|Patches - Servers Oracle|Patches - Servers Production' AND RELEASEDATE > DATE_SUB(NOW(),INTERVAL 30 DAY) GROUP BY M.NAME ORDER BY PERCENT_PATCHED, M.NAME0 - annien1 9 years ago
      • annien - Not sure if that zero on the end (ORDER BY PERCENT_PATCHED, M.NAME0) is intentional or not, but that will cause your query not to work. Also, you have your AND L.NAME = filter commented out - not sure if that's intentional or not, or if you have the subsequent AND RELEASEDATE > statement on a new line (due to the lack of formatting here in the comments section), but if everything from that point on is commented out, it won't work either.

        John - jverbosk 9 years ago
  • I did get this to work. Is there one that would just give me an average. - annien1 9 years ago
  • Is it possible for the "Patch listing completion rate by machine - concise (server)" report to not return the machines with 0 NOTPATCHED status? - farley 8 years ago
  • For some reason number of machines listed in first report does not match number of machines reported by KACE inventory script. Any insight how to rectify this? Thanks! - nenadb 7 years ago
    • Did you figure it out I didn't. Near as I can figure it has to do with the type of patch. - anaccarato 7 years ago
  • I thought you saved my life -- nope. Now I have to explain why it says it's installed on 12 servers when I have 42, but it's 100% and when I look in Kace it says it's installed on 49 -- when again I only have 42. Certain it's not your script - it's KACE and it's driving me insane. - anaccarato 7 years ago
  • Is there a way to change the Patch listing completion rate by vendor report to show total computers instead of total patches?

    The report seems to go Vendor - total number of patches applied - total number of patches not applied - percent patched.

    Can you change it more to be Vendor - Total number of computers fully patched- total number of computers missing patches- percent patched ???

    New to KACE and SQL, so thanks for any help. - Bash 5 years ago
  • Thank you jverbosk !!! This Report works like a charm. Happy Holidays !!! - endlessknot 5 years ago
  • KACE 10.x has changed the patching engine. These reports no longer work and need to be corrected. - omorganx 4 years ago
    • I wish he could correct them. The author, John Verbosky, was fired 2 years ago. Quest/KACE have no intentions of assisting on custom SQL and will only advise you to use the wizard. Feel free to sound off to Quest/KACE about their lack of support on this! - jgunter 4 years ago
  • jgunter

    I also had the same problem with my reports!
    I found this list with the tables that changed in this new version of the search appliance and modified the report ...
    I hope it helps :)

    https://support.quest.com/kb/309572/updating-custom-sql-queries-for-10-0

    SELECT M.NAME AS MACHINE_NAME,
    USER_NAME,
    OS_NAME AS OS_Name,
    SUM(MS.DETECT_STATUS ='PATCHED') AS PATCHED,
    SUM(MS.DETECT_STATUS ='NOTPATCHED') AS NOT_PATCHED,
    ROUND((SUM(MS.DETECT_STATUS ='PATCHED')/(SUM(MS.DETECT_STATUS ='PATCHED')
    +SUM(MS.DETECT_STATUS ='NOTPATCHED')))*100,0) 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 MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
    JOIN LABEL L ON (ML.LABEL_ID = L.ID)
    WHERE SEVERITY = 'Critical'
    AND PPS.STATUS = 0
    AND PP.IS_SUPERCEDED = 0
    AND L.NAME rlike 'server'
    GROUP BY M.NAME
    ORDER BY PERCENT_PATCHED, M.NAME - CaioAlves 4 years ago
This post is locked
 
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