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

Comments

  • The quick smart label I just threw together with the wizard has a few other fields selected and they are likely required. Try adding the other fields to the smart label query and force one of the systems to check in, since the smart labels are applied at check-in.

    SELECT MACHINE.NAME AS SYSTEM_NAME, SYSTEM_DESCRIPTION, MACHINE.IP, MACHINE.MAC, MACHINE.ID as TOPIC_ID FROM MACHINE WHERE ....
  • SELECT DISTINCT M.NAME AS SYSTEM_NAME, SYSTEM_DESCRIPTION, IP, MAC, M.ID as TOPIC_ID
    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?
    • Have any of the intended systems checked in? the smart label is only applied when the system checks in. Do a force update on one of the systems you expect to be in this label.
  • yes they have all checked in
  • we get the following error in the system error logs


    [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
Please log in to comment

There are no answers at this time

Answers

Answer this question or Comment on this question for clarity