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,
CS_DOMAIN
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!
Lance
Answers (2)
Critical & Recomm. Active Patches Short Listing Servers
SELECT ip,M.NAME AS ComputerName,M.USER_NAME as User,identifier asPatch_Identifier,impactid as Impact,
CASE ifnull(PPS.Status,'') WHEN 0 THEN 'Active' WHEN 1 THEN 'Inactive'WHEN 4 THEN 'Disabled' ELSE 'Unknown' END AS Activation,
MS.STATUS,
CASEifnull(PATCHLINK_PATCH_STATUS.IS_SUPERCEDED,'') WHEN 0 THEN 'NO' WHEN 1 THEN'YES' ELSE 'Unknown' END AS Superceded,
date_format(releasedate, '%m/%d/%y') as Released
FROM PATCHLINK_MACHINE_STATUS MS
JOIN KBSYS.PATCHLINK_PATCH PP ON PP.UID = MS.PATCHUID
JOIN MACHINE M ON M.ID = MS.MACHINE_ID
JOIN PATCHLINK_PATCH_STATUS PPS ON PPS.PATCHUID = PP.UID
JOIN ORG1.PATCHLINK_PATCH_STATUS onORG1.PATCHLINK_PATCH_STATUS.PATCHUID = PP.UID
WHERE
(MS.STATUS != 'PATCHED' and PPS.Status = 0 and impactid like'%Critical%' and OS_NAME like '%Server%')
or
(MS.STATUS != 'PATCHED' and PPS.Status = 0 and impactid like'Recommended%' and OS_NAME like '%Server%')
order by user, M.NAME, status,impact, releasedate
Comments:
-
Gave this a try but doesnt work.
mysqli error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHEN 0 THEN 'NO' WHEN 1 THEN 'YES' ELSE 'Unknown' END AS Superceded, date_for' at line 6] in EXECUTE( "SELECT ip,M.NAME AS ComputerName,M.USER_NAME as User,identifier asPatch_Identifier,impactid as Impact, CASE ifnull (PPS.Status,'') WHEN 0 THEN 'Active' WHEN 1 THEN 'Inactive' WHEN 4 THEN 'Disabled' ELSE 'Unknown' END AS Activation, MS.STATUS, CASEifnull(PATCHLINK_PATCH_STATUS.IS_SUPERCEDED,'') WHEN 0 THEN 'NO' WHEN 1 THEN 'YES' ELSE 'Unknown' END AS Superceded, date_format(releasedate, '%m/%d/%y') as Released FROM PATCHLINK_MACHINE_STATUS MS JOIN KBSYS.PATCHLINK_PATCH PP ON PP.UID = MS.PATCHUID JOIN MACHINE M ON M.ID = MS.MACHINE_ID JOIN PATCHLINK_PATCH_STATUS PPS ON PPS.PATCHUID = PP.UID JOIN ORG1.PATCHLINK_PATCH_STATUS onORG1.PATCHLINK_PATCH_STATUS.PATCHUID = PP.UID WHERE (MS.STATUS != 'PATCHED' and PPS.Status = 0 and impactid like '%Critical%' and OS_NAME like '%Server%') or (MS.STATUS != 'PATCHED' and PPS.Status = 0 and impactid like 'Recommended%' and OS_NAME like '%Server%') order by user, M.NAME, status,impact, releasedate LIMIT 0") - rleb29 6 years ago
Critical & Recomm. Active Patches Short Listing Window 7 machines
SELECT ip,M.NAME AS ComputerName,M.USER_NAME as User,identifier asPatch_Identifier,impactid as Impact,
CASE ifnull(PPS.Status,'') WHEN 0 THEN 'Active' WHEN 1 THEN 'Inactive'WHEN 4 THEN 'Disabled' ELSE 'Unknown' END AS Activation,
MS.STATUS,
CASEifnull(PATCHLINK_PATCH_STATUS.IS_SUPERCEDED,'') WHEN 0 THEN 'NO' WHEN 1 THEN'YES' ELSE 'Unknown' END AS Superceded,
date_format(releasedate, '%m/%d/%y') as Released
FROM PATCHLINK_MACHINE_STATUS MS
JOIN KBSYS.PATCHLINK_PATCH PP ON PP.UID = MS.PATCHUID
JOIN MACHINE M ON M.ID = MS.MACHINE_ID
JOIN PATCHLINK_PATCH_STATUS PPS ON PPS.PATCHUID = PP.UID
JOIN ORG1.PATCHLINK_PATCH_STATUS onORG1.PATCHLINK_PATCH_STATUS.PATCHUID = PP.UID
WHERE
(MS.STATUS != 'PATCHED' and PPS.Status = 0 and impactid like'%Critical%' and OS_NAME like '%Windows 7%')
or
(MS.STATUS != 'PATCHED' and PPS.Status = 0 and impactid like'Recommended%' and OS_NAME like '%Windows 7%')
order by user, M.NAME, status,impact, releasedate