Create a sql report that shows all versions of office installed on systems that are part of a particular label
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
-
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 - Hobbsy 8 years ago
Answers (1)
Please log in to answer
Posted by:
Hobbsy
8 years ago
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
