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)
Please log in to answer
Posted by:
JasonEgg
7 years ago
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
