/build/static/layout/Breadcrumb_cap_w.png
08/01/2019 237 views

Hello All,

I have a SQL patch report that will show me # of patches installed / not installed by computer name.  


Select M.NAME as MACHINE_NAME, SUM(MS.STATUS='PATCHED') AS PATCHED, 

SUM(MS.STATUS='NOTPATCHED') AS NOT_PATCHED,

GROUP_CONCAT(CASE WHEN MS.STATUS = 'PATCHED' THEN PP.TITLE END) AS 'Titles Patched',

GROUP_CONCAT(CASE WHEN MS.STATUS = 'NOTPATCHED' THEN PP.TITLE END) AS 'Titles Not Patched'

FROM ORG1.PATCHLINK_MACHINE_STATUS MS 

JOIN ORG1.MACHINE M ON M.ID = MS.MACHINE_ID 

JOIN KBSYS.PATCHLINK_PATCH PP ON PP.UID = MS.PATCHUID 

JOIN PATCHLINK_PATCH_STATUS PPS ON PPS.PATCHUID = PP.UID 

JOIN PATCHLINK_PATCH_LABEL_JT ON PATCHLINK_PATCH_LABEL_JT.PATCHUID = PP.UID

JOIN LABEL ON LABEL.ID = PATCHLINK_PATCH_LABEL_JT.LABEL_ID

where PPS.STATUS = 0 /* 0=active patches */ 

AND LABEL.NAME = "Your Custom Patch Filter Label Here" 

group by MACHINE_NAME

order by MACHINE_NAME, PP.TITLE



My question is, how do I show compliance %?  I need to show overall domain wide compliance %, per PC compliance % would be nice as well but not as important.


Many thanks

0 Comments   [ + ] Show comments

Comments


All Answers

0

I might not be following you exactly but this produces a report that shows every PC, number patched, number unpatched, and the percentage. 

SELECT 
 M.NAME AS MACHINE_NAME,
 SUM(MS.STATUS='PATCHED') AS PATCHED,
 SUM(MS.STATUS='NOTPATCHED') AS NOT_PATCHED,
 ((SUM(MS.STATUS='PATCHED'))*100)/(SUM(MS.STATUS='PATCHED')+SUM(MS.STATUS='NOTPATCHED')) AS PERCENT_PATCHED  
FROM
 MACHINE M
  LEFT JOIN PATCHLINK_MACHINE_STATUS MS
  ON (M.ID = MS.MACHINE_ID)
 LEFT JOIN KBSYS.PATCHLINK_PATCH PP
  ON (PP.UID = MS.PATCHUID)      
WHERE
  PP.IS_SUPERCEDED != 1
GROUP BY
 M.NAME
ORDER BY
 PERCENT_PATCHED, M.NAME



Answered 08/13/2019 by: getElementById
Third Degree Blue Belt