/build/static/layout/Breadcrumb_cap_w.png

I have a SQL report that shows compliance per computer with a custom patch label filter , how can I show overall compliance percentage?

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

Answers (1)

Posted by: getElementById 4 years ago
Third Degree Blue Belt
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



 
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