/build/static/layout/Breadcrumb_cap_w.png

Systems Management Question


K1000 Custom Report broken in Version 10

12/02/2019 120 views

Hi

we have got a custom report which does not work anymore. could you help me with it?

SELECT

M.ID as NUMBER,

M.NAME as MACHINE,

M.IP as IP_ADDRESS,

M.CHASSIS_TYPE as DEVICE_TYPE,

M.OS_NAME as "OS Name",

SC.CONNECT_TIME,

CASE

WHEN CLIENT_CONNECTED =1 THEN 'Currently Connected'

WHEN CLIENT_CONNECTED =0 AND SC.CONNECT_TIME  >= DATE_SUB(now(), INTERVAL 30 DAY) THEN 'Temporarily Offline'

WHEN CLIENT_CONNECTED =0 AND SC.CONNECT_TIME  >= DATE_SUB(now()+30, INTERVAL 60 DAY) THEN 'Offline'

WHEN CLIENT_CONNECTED =0 AND SC.CONNECT_TIME  < DATE_SUB(now()+60, INTERVAL 120 DAY) THEN 'Missing' ELSE 'Unknown' END AS Online_State, MS.STATUS as PATCH_STATUS, PP.IMPACTID as SEVERITY, PP.VENDOR as VENDOR, PP.TITLE as TITLE, PP.RELEASEDATE as RELEASE_DATE, (IF(V.ATTRVALUE <> '', V.ATTRVALUE, 'Not Available')) AS VENDOR_RATING, LABEL.NAME as SITE_NAME FROM PATCHLINK_MACHINE_STATUS MS JOIN KBSYS.PATCHLINK_PATCH PP ON PP.UID = MS.PATCHUID JOIN MACHINE M on ((M.ID = MS.MACHINE_ID) and (M.ID >= 12000) and (M.ID <= 13000)) LEFT JOIN KBSYS.PATCHLINK_VENDORATTRIBUTE V ON V.PATCHUID = PP.UID AND V.ATTR = 'MaximumSeverityRating'

LEFT JOIN KBSYS.SMMP_CONNECTION SC on SC.KUID = M.KUID LEFT JOIN MACHINE_LABEL_JT ON (MACHINE_LABEL_JT.MACHINE_ID = M.ID) LEFT JOIN LABEL ON (LABEL.ID = MACHINE_LABEL_JT.LABEL_ID  AND LABEL.TYPE <> 'hidden') LEFT JOIN PATCHLINK_PATCH_LABEL_JT on (PP.UID = PATCHLINK_PATCH_LABEL_JT.PATCHUID)

LEFT JOIN LABEL PATCHLABEL ON (PATCHLABEL.ID = PATCHLINK_PATCH_LABEL_JT.LABEL_ID  AND LABEL.TYPE <> 'hidden') LEFT JOIN PATCHLINK_PATCH_STATUS on PP.UID = PATCHLINK_PATCH_STATUS.PATCHUID WHERE ((LABEL.NAME like 'RP -%') OR (LABEL.NAME like 'Remote Users') and not (LABEL.NAME like '%SITE - Servers%')) AND (PP.IS_SUPERCEDED = '0') AND (PATCHLINK_PATCH_STATUS.STATUS = '0') AND PATCHLABEL.NAME in ('DE_patches [REPORT]','KS_Patch_Adobe_Products','KS_Patch_Apple_Products','KS_Patch_Mozilla_Products','KS_Patch_Google_Products','KS_Patch_7zip_Products_v1','KS_Patch_VideoLan_Products','KS_Patch_MICROSOFT_Products_PILOT');

 

Thanks.

0 Comments   [ + ] Show comments

Comments


All Answers

1

This is a patch report. You need to modify it for the new patching engine (since the old one will not patch anymore after 2020-02-01
Review this article (including the link to the schema changes) to rework the report.

Answered 12/02/2019 by: Nico_K
Red Belt

1

Hi,

version 10 has a new and overhauled Patching Module, probably all reports or custom reports and labels, might not work:

https://support.quest.com/kace-systems-management-appliance/kb/264454/deprecated-patching-items-in-the-10-0-release

I would start there.

and here:
https://support.quest.com/kace-systems-management-appliance/kb/309572/updating-custom-sql-queries-for-10-0

Answered 12/02/2019 by: Channeler
Red 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