Here is the query they sent me:

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
GROUP BY M.NAME
ORDER BY M.NAME, PERCENT_PATCHED

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answer Chosen by the Author

1
The possible IMPACTID values in the PATCHLINK_PATCH table are Software, Critical and Recommended. Assuming that by important they mean Recommended you can change this line:
WHERE PP.IMPACTID = 'Critical'
to:
WHERE PP.IMPACTID = 'Recommended'

Answered 03/01/2016 by: chucksteel
Red Belt

Please log in to comment

Answers

Answer this question or Comment on this question for clarity

Share