Hello, I'm trying to run a report that shows all users in my environment that have a specific piece of software installed, and have the output display the username, hostname, name of the ONE software and it's version. However, when I try to run the report it shows ALL software and versions installed on the machine. Does anyone know how I can set the report up to only output the one software and it's version? Thank you
Answer Summary:
Cancel
4 Comments   [ + ] Show Comments

Comments

  • Can you attach the SQL you have currently for this report?
    • Absolutely. Thanks

      SELECT MACHINE.NAME AS SYSTEM_NAME, MACHINE.USER_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, (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 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 CATALOG.SAM_CATALOG JOIN SAM_MACHINE_JT on CATALOG.SAM_CATALOG.ID = SAM_MACHINE_JT.SAM_CATALOG_ID where MACHINE.ID = SAM_MACHINE_JT.MACHINE_ID and CATALOG.SAM_CATALOG.NAME like '%GlobalMeet ScreenShare%')) )) GROUP BY MACHINE.ID ORDER BY SYSTEM_NAME
  • Absolutely. Thanks

    SELECT MACHINE.NAME AS SYSTEM_NAME, MACHINE.USER_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, (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 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 CATALOG.SAM_CATALOG JOIN SAM_MACHINE_JT on CATALOG.SAM_CATALOG.ID = SAM_MACHINE_JT.SAM_CATALOG_ID where MACHINE.ID = SAM_MACHINE_JT.MACHINE_ID and CATALOG.SAM_CATALOG.NAME like '%GlobalMeet ScreenShare%')) )) GROUP BY MACHINE.ID ORDER BY SYSTEM_NAME
  • If there is more than one version of a program installed, do you want each instance to have its own row?
    • Hello JasonEgg, yes that would be ideal
      • Thank you JasonEgg. However when I run it looking for Software name, I only pull a couple of instances. When I run it looking for Software Title, I get all the installed instances in the environment. Is there a way I can modify the code to pull the Software Title attribute instead?
  • There are at least three fields in these tables that have the name of a piece of software: SOFTWARE.DISPLAY_NAME, CATALOG.SAM_CATALOG.NAME, & CATALOG.SAM_CATALOG.PRODUCT_NAME
    I don't know which one is the most general or comprehensive. However, my strategy for including more instances of the same software is the same for both of the queries I posted below: In the WHERE clause replace "LIKE 'Software Name'" with a more general form by including wildcards (% in SQL), e.g. "LIKE '%Software%Name%'". And/or you could include multiple titles by replacing "LIKE 'Software Name'" with "IN ('Software Name','Alternate Software Name','Third Name Variant')"
    • Thank you JasonEgg I will try and tweak the report a little bit so I can gather the correct results. I will let you know thanks again!
    • Thank you JasonEgg I will try and tweak the report a little bit so I can gather the correct results. I will let you know thanks again!
Please log in to comment

Answer Chosen by the Author

0
Here is a similar query using SAM_CATALOG instead of software:

SELECT MACHINE.NAME AS SYSTEM_NAME, MACHINE.USER_NAME, CATALOG.SAM_CATALOG.NAME,
       CATALOG.SAM_CATALOG.MAJOR_VERSION
FROM   MACHINE
       JOIN SAM_MACHINE_JT ON SAM_MACHINE_JT.MACHINE_ID = MACHINE.ID
       JOIN CATALOG.SAM_CATALOG ON SAM_MACHINE_JT.SAM_CATALOG_ID = CATALOG.SAM_CATALOG.ID
WHERE  CATALOG.SAM_CATALOG.NAME like 'Flash Player'
ORDER BY SYSTEM_NAME
Answered 02/29/2016 by: JasonEgg
Fourth Degree Green Belt

Please log in to comment

Answers

2
Here is a simple version of what you're looking for. This does not include the information from the SAM catalog, however.

SELECT MACHINE.NAME AS SYSTEM_NAME, 
  MACHINE.USER_NAME, 
  SOFTWARE.DISPLAY_NAME, 
  GROUP_CONCAT(SOFTWARE.DISPLAY_VERSION) AS `VERSION(S)` 
FROM MACHINE 
  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 
  SOFTWARE.DISPLAY_NAME like 'Flash Player'
GROUP BY MACHINE.ID
ORDER BY SYSTEM_NAME
Answered 02/26/2016 by: JasonEgg
Fourth Degree Green Belt

  • This query actually returns only one row for each computer and if there are multiple versions of the software then they are separated by commas in the fourth column. To display a separate row for each software instance remove GROUP_CONCAT() functions and the line "GROUP BY MACHINE.ID"
Please log in to comment
Answer this question or Comment on this question for clarity