KACE Report for Devices not compliant by patch excluding "superseded" and "inactive" patches
Hello all. I am new to my KACE K1000 so this is my first report challenge. I am looking for a report to hand to an outside auditor that shows that all my machines are up to date on patches. The closest that I have found is the default report titled "Devices not compliant by patch". The only problem with this report is that, for some devices, it will list older patches even though they have been superseded by a later patch version which has been successfully installed on that device. If you go into SECURITY>CATALOG and search for one of the known older patches from the report, the patch will be listed as Status "Inactive" and Superseded "Yes".
How do I make it so this Inactive and Superseded patches do not show up? I need the SQL code to exclude by the Status and/or Superseded fields. Here is the default report SQL code:
SELECT PP.TITLE AS DISPLAY_NAME,
M.NAME AS ComputerName,
SYSTEM_DESCRIPTION, IP, MAC,
M.USER_LOGGED as USER_LOGGED,
FROM PATCHLINK_MACHINE_STATUS MS
JOIN KBSYS.PATCHLINK_PATCH PP ON PP.UID = MS.PATCHUID
JOIN MACHINE M ON M.ID = MS.MACHINE_ID
WHERE MS.STATUS = 'NOTPATCHED'
ORDER BY PP.TITLE
Any help is much appreciated!