/build/static/layout/Breadcrumb_cap_w.png

Systems Management Question


K1000 Custom Report broken in Version 10

12/02/2019 366 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

 
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