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!
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

There are no answers at this time

Answers

Answer this question or Comment on this question for clarity