Need help with a script
I am trying to figure out how to write a script that will allow me to list every computer in my device inventory that is missing a specific patch. I have a script to show every computer that has the specified patch installed, but can't seem to figure out how to get the opposite; every computer that does NOT have this patch installed. The patch in question is KB4534293, and its specific for Windows 10 version 1803.
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,
JOIN KBSYS.PATCH ON (KBSYS.PATCH.ID = PATCH_MACHINE_STATUS.PATCH_ID)
JOIN MACHINE ON (MACHINE.ID = PATCH_MACHINE_STATUS.MACHINE_ID)
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')
PATCH_MACHINE_STATUS.PATCH_INSTALLED = 0
KBSYS.PATCH.IS_SUPERCEDED = 0
PATCH_MACHINE_STATUS.PATCH_APPLICABLE = 1
GROUP BY MACHINE.ID