Software Deployment : Output Kace report displaying one piece of software and it's version

Output Kace report displaying one piece of software and it's version

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

4 Comments [ + ] Show comments

Can you attach the SQL you have currently for this report? - grayematter 8 years ago
- 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 - david.fitzpatrick@assetmark.com 8 years ago
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 - david.fitzpatrick@assetmark.com 8 years ago
If there is more than one version of a program installed, do you want each instance to have its own row? - JasonEgg 8 years ago
- Hello JasonEgg, yes that would be ideal - david.fitzpatrick@assetmark.com 8 years ago
  - 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? - david.fitzpatrick@assetmark.com 8 years ago
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')" - JasonEgg 8 years ago
- 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! - david.fitzpatrick@assetmark.com 8 years ago
- 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! - david.fitzpatrick@assetmark.com 8 years ago

Answers (2)

Answer Summary:

Posted by: JasonEgg 8 years ago

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

Comments:

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" - JasonEgg 8 years ago

Posted by: JasonEgg 8 years ago

Posted by:

View more:

Software Deployment Questions

question