K1000 Report: List of installed softwares on the computers under the particular lable
Answers (1)
Resolution:
SELECT (SELECT group_concat(distinct if(LABEL.NAME not like 'HDN_LABEL_%', LABEL.NAME, 'System Hidden') separator '
') FROM MACHINE_LABEL_JT MLJT INNER JOIN LABEL ON MLJT.LABEL_ID = LABEL.ID WHERE MACHINE.ID = MLJT.MACHINE_ID
ORDER BY LABEL.NAME) as LABEL_NAME, MACHINE.NAME AS SYSTEM_NAME, (SELECT GROUP_CONCAT(DISPLAY_NAME order by
DISPLAY_NAME separator '') FROM SOFTWARE S2 INNER JOIN MACHINE_SOFTWARE_JT MSJT ON S2.ID = MSJT.SOFTWARE_ID
WHERE MSJT.MACHINE_ID = MACHINE.ID) AS DISPLAY_NAME FROM MACHINE WHERE ((( exists (select 1 from LABEL, MACHINE_LABEL_JT where
MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID AND MACHINE_LABEL_JT.LABEL_ID = LABEL.ID AND LABEL.TYPE <> 'hidden' and LABEL.NAME = 'Jnk-Comp')) )) ORDER BY LABEL_NAME