Scripting

Hey Josh,

I just did this last week, here is what I used. In this case I chose to use a smart label instead of a report, so that I can more easily target these machines for patching, but the same SQL query should work for your report. In this case there is a list of patches because they all fixed the same CVE-2020-0601 that Microsoft patched the other week, just a different update depending on OS. You could remove all the patches in the brackets and just insert the one that you are looking to check.

SELECT MACHINE_ID as TOPIC_ID,

KBSYS.PATCH.TITLE AS PATCH,

MACHINE.NAME AS MACHINE,

IP

FROM PATCH_MACHINE_STATUS

JOIN KBSYS.PATCH ON (KBSYS.PATCH.ID = PATCH_MACHINE_STATUS.PATCH_ID)

JOIN MACHINE ON (MACHINE.ID = PATCH_MACHINE_STATUS.MACHINE_ID)

WHERE

KBSYS.PATCH.PATCH_IDENTIFIER IN ('KB4534306','KB4534306','KB4534271','KB4534271','KB4534276','KB4534276','KB4534276','KB4534293','KB4534293','KB4534293','KB4534273','KB4534273','KB4534273','KB4528760','KB4528760','KB4528760','KB4528760','KB4528760','KB4528760','KB4534271','KB4534271','KB4534273','KB4534273','KB4534293','KB4528760','KB4528760')

AND

PATCH_MACHINE_STATUS.PATCH_INSTALLED = 0

AND

KBSYS.PATCH.IS_SUPERCEDED = 0

AND

PATCH_MACHINE_STATUS.PATCH_APPLICABLE = 1

GROUP BY MACHINE.ID