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

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

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.

Answered 05/19/2014 by: chucksteel
Red Belt

  • This is exactly what I needed. Thank you very much.
Please log in to comment
Answer this question or Comment on this question for clarity