/build/static/layout/Breadcrumb_cap_w.png

Having trouble getting software report to limit the number of titles returned

Hello,

 

I am trying to create a report that will target a specific machine label and look for Junos Pulse on those machines and report back Junos Pulse and verion. 

 

If is working except that when the report completes it lists all software installed on the machines instead of only pulse.

 

I am hoping that it is a simple fix, but obviously one that I cannot see.

 

Any help would be greatly appreciated.

 

SELECT MACHINE.NAME AS SYSTEM_NAME, OS_NAME, MACHINE.USER_NAME, USER_FULLNAME, 

(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, 

(SELECT GROUP_CONCAT(DISPLAY_VERSION order by DISPLAY_VERSION 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_VERSION  FROM MACHINE  

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 = 'Junos Pulse')) ) 

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 = 'MissingJunos')) ))  

ORDER BY SYSTEM_NAME, DISPLAY_NAME, DISPLAY_VERSION


0 Comments   [ + ] Show comments

Answers (1)

Answer Summary:
Posted by: chucksteel 10 years ago
Red Belt
0

Here's a report that looks for computers in a specific label with a specific software title installed. Mine is looking for Flash Player versions in the "Library Services" label:

 SELECT MACHINE.NAME, SOFTWARE.DISPLAY_NAME, SOFTWARE.DISPLAY_VERSIONFROM LABELJOIN MACHINE_LABEL_JT on MACHINE_LABEL_JT.LABEL_ID = LABEL.IDJOIN MACHINE on MACHINE.ID = MACHINE_LABEL_JT.MACHINE_IDJOIN MACHINE_SOFTWARE_JT on MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE_LABEL_JT.MACHINE_IDJOIN SOFTWARE on SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID WHERE LABEL.NAME = "Library Services"AND NOT IS_PATCHAND SOFTWARE.DISPLAY_NAME like "%Flash Player%"ORDER BY MACHINE.NAME

You would need to change the LABEL.NAME and SOFTWARE.DISPLAY_NAME queries to match your needs.


Comments:
  • This is exactly what I needed. Thank you very much. - jparkins 10 years ago
 
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