/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


KACE Report for Devices not compliant by patch excluding "superseded" and "inactive" patches

06/16/2015 3439 views

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


All 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

  • 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")
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

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ