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


0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

1

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


Answered 08/03/2016 by: mtcooper
White Belt

Please log in to comment
1

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


Answered 08/03/2016 by: mtcooper
White Belt

Please log in to comment
Answer this question or Comment on this question for clarity