/build/static/layout/Breadcrumb_cap_w.png

How can I list the number of computers missing critical patches?

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

Answers (1)

Posted by: JasonEgg 6 years ago
Red Belt
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

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