/build/static/layout/Breadcrumb_cap_w.png
05/20/2017 613 views

Say I have 10 computers 8 have all patches installed 2 are missing one or more critical patches. How can I get a report that would show

Impact    Patched    Not Patched

Critical     8            2

This one seems to be close but seems to count the patches not machines. 

select P.impactid as Impact,
ROUND((SUM(MS.STATUS='PATCHED')/COUNT(MS.MACHINE_ID)) * 100, 2) AS COMPLIANCE,
    SUM(MS.STATUS='PATCHED') AS Patched,
    SUM(MS.STATUS='NOTPATCHED') AS Not_Patched,
    SUM((MS.DEPLOY_ATTEMPT_COUNT >= 3 and MS.STATUS != 'PATCHED')
or MS.STATUS = 'FAIL' or MS.DEPLOY_STATUS = 'FAIL') as Error
from KBSYS.PATCHLINK_PATCH as P
LEFT JOIN PATCHLINK_MACHINE_STATUS MS ON MS.PATCHUID = P.UID
    join PATCHLINK_PATCH_STATUS PS ON PS.PATCHUID = P.UID
Where PS.status = 0
group by P.impactid;
0 Comments   [ + ] Show comments

Comments


All Answers

0
SELECT PP.IMPACTID AS 'Impact',
  COUNT(CASE WHEN MS.STATUS = 'PATCHED' THEN M.ID END) AS 'Patched',
  COUNT(CASE WHEN MS.STATUS != 'PATCHED' THEN M.ID END) AS 'Not 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
  JOIN PATCHLINK_PATCH_STATUS PPS 
    ON PPS.PATCHUID = PP.UID
WHERE PPS.STATUS = 0
GROUP BY PP.IMPACTID
Answered 05/26/2017 by: JasonEgg
Red Belt