/build/static/layout/Breadcrumb_cap_w.png

Systems Management Question


Create a sql report that shows all versions of office installed on systems that are part of a particular label

06/16/2015 2960 views
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

All 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

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ