Critical patches missing by machine
SELECT M.NAME AS MACHINE_NAME, USER_NAME, OS_NAME AS OS_Name, SUM(MS.STATUS='PATCHED') AS PATCHED, SUM(MS.STATUS='NOTPATCHED') AS NOT_PATCHED, 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 PERCENT_PATCHED, M.NAME
It's a wonderful report that tells me how many critical patches are missing per machine so that I can basically have a point to start at. It's been a great tool that gave me a starting point as I tried to bring the network into compliance. Now, however, I want to have a companion report to it...and to say that I suck at SQL would be a massive understatement (it's an upcoming class for me, so I hope to learn about it soon, but I just don't know it yet).
Now the important question...currently, this report gives me a machine name, user name, OS, Patched count, not patched count, and percentage patched. I would like a report that gives me, broken down by machine, what the actual packages/patches are that are considered "Not Patched". We have a server, for example, that is still missing 4 or 5 despite having been told to patch both application and OS patching....I want to know what those 5 are, but I would like to have it for all of the patches missing per machine as well if possible.
Thanks for any assistance that you can provide, and thanks for bearing with my rambling!