I need to create a report that shows all of the versions of office that are installed for a particular label.  Does anyone have a good report that shows this.  
1 Comment   [ + ] Show Comment

Comments

  • What type of label are we talking? (Machine or software) I am going to guess hardware, so you need to know the versions of office installed for a group of machines
Please log in to comment

Answers

1
The following SQL was actually built using the reporting wizard, as Software Titles are related to Machines. In the SQL you will see two areas highlighted, the first is the link to the application name that is installed and the second is the link to the label name in which the machines resides.

Note I have used a "contains" in  building the query, but you could be much more specific in terms of label names and Software names 

SELECT (SELECT group_concat(distinct if(LABEL.NAME not like 'HDN_LABEL_%', LABEL.NAME, 'System Hidden') separator '\n') 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 '\n') 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  LEFT JOIN MACHINE_LABEL_JT ON (MACHINE_LABEL_JT.MACHINE_ID = MACHINE.ID)  LEFT JOIN LABEL ON (LABEL.ID = MACHINE_LABEL_JT.LABEL_ID) LEFT JOIN MACHINE_SOFTWARE_JT ON (MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID) LEFT JOIN SOFTWARE ON (SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID) WHERE ((( exists  (select 1 from SOFTWARE, MACHINE_SOFTWARE_JT where MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID AND SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID and SOFTWARE.DISPLAY_NAME like '%Java%')) ) AND (( 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 like '%Tim Test Group%')) ))  GROUP BY MACHINE.ID ORDER BY SYSTEM_NAME
Answered 06/17/2015 by: Hobbsy
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity

Share