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;