Report on patch compliance % on each machine
Hello all!
I have a report that shows me how much in % each machine is patched (will post the sql query below). I want to step a little further in details and need the percentage on critical patches, optional patches and firmware update of each machine. It would be something like this:
Computer % critical patches % optional patches % firmware updates
CP01 90 65 30
The SQL query I have for overall % is:
SELECT M.NAME AS MACHINE_NAME,
OS_NAME AS WINDOWS_VERSION,
SUM(MS.STATUS='PATCHED') AS PATCHED,
SUM(MS.STATUS='NOTPATCHED') AS NOTPATCHED,
ROUND((SUM(MS.STATUS='PATCHED')/(SUM(MS.STATUS='PATCHED')
+SUM(MS.STATUS='NOTPATCHED')))*100,0) AS PERCENT_PATCHED
FROM PATCHLINK_MACHINE_STATUS MS
JOIN KBSYS.PATCHLINK_PATCH PP ON (PP.UID = MS.PATCHUID)
JOIN PATCHLINK_PATCH_STATUS PPS ON (PPS.PATCHUID = PP.UID)
JOIN MACHINE M ON (M.ID = MS.MACHINE_ID)
JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
JOIN LABEL L ON (ML.LABEL_ID = L.ID)
WHERE PP.IMPACTID = 'Critical'
AND PPS.STATUS = 0
AND PP.IS_SUPERCEDED = 0
AND L.NAME = 'Machines: Desktops (via Chassis/OS Name)'
GROUP BY M.NAME
ORDER BY PERCENT_PATCHED, M.NAME
Could you help me with the SQL query? Many thanks in advance!
1 Comment
[ + ] Show comment
-
i'd be interested in a report like this. - dschraplau 6 years ago
Answers (0)
Please log in to answer
Be the first to answer this question
