Systems Management

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.