KACE Product Support

I have the following problem when creating a report that shows the patches of the individual devices that are still to be installed, which release was more than 14 days ago. It is not possible to crate the report using the assistant report, there is no option in the assistant to filter the release of the patch (published). It is also not possible to extract the SQL syntax from a smart lable for the report, because you cannot use the "device" parameter there because you have to crate a patch smart label. So i tried to adapt to the syntax of the following smart lable so that the patches are subordinate to one device.

Smart Label:

SQL of Smart Label:

SELECT UNIX_TIMESTAMP(CREATION_DATE) as
DATEPOSTED_SECONDS,
(CASE KBSYS.PATCH_PACKAGE.PACKAGE_TYPE WHEN 'APP' THEN 1
ELSE 0 END) AS IS_APP,
KBSYS.PATCH.CREATION_DATE AS DATEPOSTED,
KBSYS.PATCH.IS_SUPERCEDED,
KBSYS.PATCH.ID AS BID,
KBSYS.PATCH.PATCH_IDENTIFIER,
KBSYS.PATCH.CLASSIFICATION,
 KBSYS.PATCH.PUBLISHER,
UNPATCHED,
YEAR(KBSYS.PATCH.CREATION_DATE) as DATEPOSTED_YEAR,
PATCHED,
KBSYS.PATCH.TITLE,
PATCH_STATUS.STATUS AS PATCH_STATUS,
KBSYS.PATCH.ID as TOPIC_ID FROM
KBSYS.PATCH
 LEFT JOIN
KBSYS.PATCH_PRODUCT_JT ON
PATCH.ID = PATCH_PRODUCT_JT.PATCH_ID
LEFT JOIN KBSYS.PATCH_PRODUCT ON
PATCH_PRODUCT_JT.PATCH_PRODUCT_ID = PATCH_PRODUCT.ID
LEFT JOIN KBSYS.PATCH_PRODUCT_OS_JT ON
PATCH_PRODUCT_OS_JT.PATCH_PRODUCT_ID =
PATCH_PRODUCT.ID
LEFT JOIN KBSYS.PATCH_OS O ON
O.ID = PATCH_PRODUCT_OS_JT.PATCH_OS_ID
LEFT JOIN KBSYS.PATCH_PACKAGE ON
PATCH_PACKAGE_ID
= PATCH_PACKAGE.ID
 left join PATCH_COUNT on
PATCH_COUNT.PATCH_ID = KBSYS.PATCH.ID left join PATCH_STATUS on
PATCH_STATUS.PATCH_ID = KBSYS.PATCH.ID WHERE ((KBSYS.PATCH.IS_SUPERCEDED = '0')
AND (( exists  (select 1 from
PATCH_MACHINE_STATUS MS where MS.PATCH_ID = KBSYS.PATCH.ID and MS.DETECT_STATUS
= 'NOTPATCHED')) ) AND ((TIMESTAMP(KBSYS.PATCH.CREATION_DATE) > NOW() OR
TIMESTAMP(KBSYS.PATCH.CREATION_DATE) <= DATE_SUB(NOW(),INTERVAL 14 DAY)))
AND (KBSYS.PATCH.SEVERITY != 'Recommended') AND (PATCH_STATUS.STATUS = '0') AND
(KBSYS.PATCH.CLASSIFICATION != 'Full Software') AND (O.ID in
('OSYS152541','OSYS152542','OSYS152543','OSYS152544','OSYS152547','OSYS152548','OSYS152549','OSYS152550','OSYS152551','OSYS152552','OSYS152553','OSYS152556','OSYS152557','OSYS152558','OSYS152559','OSYS198390','OSYS205628','OSYS205629','OSYS205630','OSYS205631','OSYS205632','OSYS205633','OSYS215320')))  GROUP BY KBSYS.PATCH.ID
PATCH_COUNT.PATCH_ID = KBSYS.PATCH.ID left join PATCH_STATUS on
PATCH_STATUS.PATCH_ID = KBSYS.PATCH.ID WHERE ((KBSYS.PATCH.IS_SUPERCEDED = '0')
AND (( exists  (select 1 from
PATCH_MACHINE_STATUS MS where MS.PATCH_ID = KBSYS.PATCH.ID and MS.DETECT_STATUS
= 'NOTPATCHED')) ) AND ((TIMESTAMP(KBSYS.PATCH.CREATION_DATE) > NOW() OR
TIMESTAMP(KBSYS.PATCH.CREATION_DATE) <= DATE_SUB(NOW(),INTERVAL 14 DAY)))
AND (KBSYS.PATCH.SEVERITY != 'Recommended') AND (PATCH_STATUS.STATUS = '0') AND
(KBSYS.PATCH.CLASSIFICATION != 'Full Software') AND (O.ID in
('OSYS152541','OSYS152542','OSYS152543','OSYS152544','OSYS152547','OSYS152548','OSYS152549','OSYS152550','OSYS152551','OSYS152552','OSYS152553','OSYS152556','OSYS152557','OSYS152558','OSYS152559','OSYS198390','OSYS205628','OSYS205629','OSYS205630','OSYS205631','OSYS205632','OSYS205633','OSYS215320')))  GROUP BY KBSYS.PATCH.ID

MACHINE.NAME,
KBSYS.PATCH.TITLE,
MACHINE.IP
 
FROM
KBSYS.PATCH
 
LEFT JOIN
MACHINE ON KBSYS.PATCH.ID = MACHINE.ID
LEFT JOIN
KBSYS.PATCH_PRODUCT_JT ON PATCH.ID = PATCH_PRODUCT_JT.PATCH_ID
LEFT JOIN KBSYS.PATCH_PRODUCT ON
PATCH_PRODUCT_JT.PATCH_PRODUCT_ID = PATCH_PRODUCT.ID
LEFT JOIN KBSYS.PATCH_PRODUCT_OS_JT ON
PATCH_PRODUCT_OS_JT.PATCH_PRODUCT_ID = PATCH_PRODUCT.ID
LEFT JOIN KBSYS.PATCH_OS O ON O.ID = PATCH_PRODUCT_OS_JT.PATCH_OS_ID
LEFT JOIN KBSYS.PATCH_PACKAGE ON PATCH_PACKAGE_ID =
PATCH_PACKAGE.ID
LEFT JOIN PATCH_COUNT on PATCH_COUNT.PATCH_ID =
KBSYS.PATCH.ID 
LEFT JOIN PATCH_STATUS on PATCH_STATUS.PATCH_ID =
KBSYS.PATCH.ID WHERE ((KBSYS.PATCH.IS_SUPERCEDED = '0') AND (( exists  (select 1 from PATCH_MACHINE_STATUS MS where
MS.PATCH_ID = KBSYS.PATCH.ID and MS.DETECT_STATUS = 'NOTPATCHED')) ) AND
((TIMESTAMP(KBSYS.PATCH.CREATION_DATE) > NOW() OR TIMESTAMP(KBSYS.PATCH.CREATION_DATE)
<= DATE_SUB(NOW(),INTERVAL 14 DAY))) AND (KBSYS.PATCH.SEVERITY !=
'Recommended') AND (PATCH_STATUS.STATUS = '0') AND (KBSYS.PATCH.CLASSIFICATION
!= 'Full Software') AND (O.ID in
('OSYS152541','OSYS152542','OSYS152543','OSYS152544','OSYS152547','OSYS152548','OSYS152549','OSYS152550','OSYS152551','OSYS152552','OSYS152553','OSYS152556','OSYS152557','OSYS152558','OSYS152559','OSYS198390','OSYS205628','OSYS205629','OSYS205630','OSYS205631','OSYS205632','OSYS205633','OSYS215320'))) 
GROUP BY PATCH.TITLE
ORDER BY CREATION_DATE desc

problems of the changed report:

The problem whit the customized SQL report is that the target device line return "devicename = NULL" and so there is only a list of patches that are older then 14 days and are not installed on a device in the network, but it is not clear to which device the patches missing.

What have I adjusted:

The difference between the SQL from the smart label and the customized SQL report is that i have removed some fields that are unnecessary for the report, added some such as MACHINE.NAME or MACHINE.ID, selected as upper group the MACHINE.NAME and i added the JOIN "LEFT JOIN MACHINE ON KBSYS.PATCH.ID = MACHINE.ID" Finally, i have changed the sorting so that the latest patches appear at the top and are sorted by date.