KACE SMAReport for Critical Recent Patches NOT Installed (4272844)

This SQL report expands on the Recent Critical Patches report included with the KACE SMA (Systems Management Appliance)

Included under the patching reports is a report titled "Critical and Recent Bulletin List" which is a report of Critical patches released in the last two weeks. It doesn't show if they are already installed or not or if they were caught by a label and had an issue or just escaped our labels all together.

This report does both of those things. It filters and removes the items that KACE already deployed and adds a patch label (if any) to those that we need to look into.

The following SQL is provided as a courtesy and not supported by KACE Technical Support.

WARNING: Any reports created should be tested using mysql workbench to verify results before creating a report in the SMA appliance.

To add this report create a New SQL report by doing the following.

Reporting | Choose Action | Add New SQL Report

Give your report the appropriate name and category of your choosing.

NOTE *Adding notes as to what the report does will make it easier to reference for others and yourself in the future
It is also a good idea to add a date of last edited to this field and update it whenever editing the rule. This allows possible issues to be tracked back to changes made.

Use the following SQL code

*************************************************
BEGIN SQL CODE
*************************************************

select IDENTIFIER,
       case IFNULL(PATCHLINK_PATCH_STATUS.STATUS, '')
           when 0 then 'Active'
           when 1 then 'Inactive'
           when 4 then 'Disabled'
           else 'Unknown'
       end as PP_STATUS,
       TITLE,
       RELEASEDATE,
       IMPACTID,
       case IFNULL(LABEL.NAME, '')
           when '' then 'Not Found in Label'
           else LABEL.NAME
       end as 'Patch Label'
from KBSYS.PATCHLINK_PATCH
left join PATCHLINK_PATCH_STATUS 
	on PATCHLINK_PATCH_STATUS.PATCHUID = PATCHLINK_PATCH.UID
left join PATCHLINK_MACHINE_STATUS 
	on PATCHLINK_MACHINE_STATUS.PATCHUID = PATCHLINK_PATCH.UID
left join PATCHLINK_PATCH_LABEL_JT 
	on PATCHLINK_PATCH_LABEL_JT.PATCHUID = PATCHLINK_MACHINE_STATUS.PATCHUID
left join LABEL 
	on PATCHLINK_PATCH_LABEL_JT.LABEL_ID = LABEL.ID
where ((IMPACTID = 'Critical')
       and (DATE(RELEASEDATE) >= DATE_SUB(CURDATE(), interval DAYOFWEEK(CURDATE()) - 1 + (7 * 1) day)
            and DATE(RELEASEDATE) < DATE_ADD(CURDATE(), interval DAYOFWEEK(CURDATE()) - 1 day)))
  and PATCHLINK_MACHINE_STATUS.STATUS != 'PATCHED'
order by IMPACTID ,
         PP_STATUS ,
         RELEASEDATE

*************************************************
END SQL CODE
*************************************************