Why does this sql work for reporting but not smart labels?
the sql below works for a report by populating with the machines we want, however when we try to make a smart label it does not work anyone know why? this sql is supposed to get all the machine names that have a patch status of suspended, that are not in patch schedules with the word detect and that have our software CReplacement installed.
SELECT DISTINCT M.NAME AS Machine
FROM PATCHLINK_SCHEDULE P LEFT JOIN KBSYS.KONDUCTOR_TASK K ON P.KONDUCTOR_TASK_TYPE = K.TYPE LEFT JOIN MACHINE M ON K.KUID = M.KUID LEFT JOIN PATCHLINK_SCHEDULE_MACHINE_STATUS PSMS ON PSMS.MACHINE_ID = M.ID
WHERE P.LAST_RUN <> 0 AND DESCRIPTION not like '%Detect%' AND Phase like 'suspended' AND M.NAME IN (SELECT MACHINE.NAME
FROM MACHINE, SOFTWARE, MACHINE_SOFTWARE_JT
WHERE MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID AND MACHINE_SOFTWARE_JT.SOFTWARE_ID=SOFTWARE.ID AND SOFTWARE.DISPLAY_NAME LIKE 'CReplacement')
4 Comments
[ + ] Show comments
Answers (0)
Please log in to answer
Be the first to answer this question
SELECT MACHINE.NAME AS SYSTEM_NAME, SYSTEM_DESCRIPTION, MACHINE.IP, MACHINE.MAC, MACHINE.ID as TOPIC_ID FROM MACHINE WHERE .... - grayematter 9 years ago
FROM PATCHLINK_SCHEDULE P LEFT JOIN KBSYS.KONDUCTOR_TASK K ON P.KONDUCTOR_TASK_TYPE = K.TYPE LEFT JOIN MACHINE M ON K.KUID = M.KUID LEFT JOIN PATCHLINK_SCHEDULE_MACHINE_STATUS PSMS ON PSMS.MACHINE_ID = M.ID
WHERE P.LAST_RUN <> 0 AND DESCRIPTION not like '%Detect%' AND Phase like 'suspended' AND M.NAME IN (SELECT MACHINE.NAME
FROM MACHINE, SOFTWARE, MACHINE_SOFTWARE_JT
WHERE MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID AND MACHINE_SOFTWARE_JT.SOFTWARE_ID=SOFTWARE.ID AND SOFTWARE.DISPLAY_NAME LIKE 'CReplacement')
I made the change but still no luck any other ideas? - dragoo 9 years ago
[Fri Aug 08 14:53:20 2014] [error] [client 192.168.49.56] PHP Warning: INVALID FILTER (ID = 185): ''SELECT DISTINCT\r\n M.NAME AS SYSTEM_NAME,\r\n SYSTEM_DESCRIPTION,\r\n IP,\r\n MAC,\r\n M.ID as TOPIC_ID\r\nFROM\r\n ORG1.PATCHLINK_SCHEDULE P,\r\n ORG1.PATCHLINK_SCHEDULE_MACHINE_STATUS PSMS,\r\n ORG1.MACHINE M,\r\n KBSYS.KONDUCTOR_TASK K\r\n\r\nWHERE\r\n P.LAST_RUN <> 0\r\n AND PSMS.MACHINE_ID = M.ID\r\n AND K.KUID = M.KUID\r\n AND P.KONDUCTOR_TASK_TYPE = K.TYPE\r\n AND DESCRIPTION not like '%Detect%'\r\n AND Phase like 'suspended'\r\n AND M.NAME IN\r\n (\r\n SELECT\r\n MACHINE.NAME\r\n FROM\r\n MACHINE,\r\n SOFTWARE,\r\n MACHINE_SOFTWARE_JT\r\n WHERE\r\n MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID\r\n AND MACHINE_SOFTWARE_JT.SOFTWARE_ID=SOFTWARE.ID\r\n AND SOFTWARE.DISPLAY_NAME LIKE 'CReplacement'\r\n ) and MACHINE.ID = 9442'' in /kbox/kboxwww/include/client_service.php on line 155 - dragoo 9 years ago