Article: Report - Patches Missing By Devices IF Device Is Missing Patches

I've always thought this should be a canned report but after wanting it for a while I finally dug in on how to do it. All of the previous reports I could find were pre-10.0 so the SQL report examples I could find examples of no longer worked.

The below report will list missing patches per device that is actually missing patches. You can remove the P.SEVERITY != 'low' if you are interested in those patches as well, depending on if you are detecting them.

SELECT
M.NAME AS ComputerName,
OS_NAME,
M.USER_LOGGED AS USER_LOGGED,
P.PUBLISHER,
P.TITLE AS DISPLAY_NAME,
P.SEVERITY,
MS.DETECT_STATUS

FROM
PATCH_MACHINE_STATUS MS

JOIN KBSYS.PATCH P ON P.ID = MS.PATCH_ID
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
MS.DETECT_STATUS != 'PATCHED' AND
P.IS_SUPERCEDED = '0' AND
P.SEVERITY != 'low' AND
L.NAME = 'INSERT LABEL NAME HERE'

ORDER BY M.NAME

I hope this helps someone out there.

Report - Patches Missing By Devices IF Device Is Missing Patches

Comments

Posted by:

Related Posts

Report - Patches Missing By Devices IF Device Is Missing Patches

Comments

Posted by:

Don't be a Stranger!

Related Posts

Share