Kace report for computers with specific software installed to also display that software title?
Answers (3)
SELECT M.ID, M.NAME, M.LAST_INVENTORY, M.OS_NAME,
GROUP_CONCAT(DISTINCT SOFTWARE.DISPLAY_NAME SEPARATOR '\n') AS "Software Name"
FROM ORG1.MACHINE M
LEFT JOIN MACHINE_SOFTWARE_JT ON (MACHINE_SOFTWARE_JT.MACHINE_ID = M.ID)
LEFT JOIN SOFTWARE ON (SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID and SOFTWARE.PUBLISHER like "%Adobe%")
GROUP BY M.ID ORDER BY M.NAME
SELECT M.ID, M.NAME, M.LAST_INVENTORY, M.OS_NAME,
(SELECT GROUP_CONCAT(CONCAT(DISPLAY_NAME, " (", DISPLAY_VERSION, ")")) as Titles
FROM SOFTWARE
JOIN MACHINE_SOFTWARE_JT on MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID
WHERE PUBLISHER like "%Adobe%"
and MACHINE_SOFTWARE_JT.MACHINE_ID = M.ID
GROUP BY MACHINE_ID) AS "Titles"
FROM ORG1.MACHINE M
GROUP BY M.ID ORDER BY M.NAME
Comments:
-
Thanks for the reply! Sorry about the delay, I was trying to get my mind wrapped around the SQL in my current report. In your SQL how would I limit the scope to only computers in a particular label (BU_Machines)?
I can post the SQL from the report if that helps. - mikefletcher85 7 years ago-
See this post:
http://www.itninja.com/question/modifying-an-existing-report-to-target-a-specific-label - chucksteel 7 years ago
-
Thanks for posting this. Its something I have always been meaning to do in our reports and always kept forgetting to look into it - trevorhalse 7 years ago
-
Thanks, 2 years later still useful! Why cant we filter this out in the Wizard? - lama01 5 years ago
-
That's a great question that you could ask at UserKon. - chucksteel 5 years ago
Here is an updated SQL query that works. Only thing is you have to retype the single quotes around "google" - This was modified from the SQL wizard.
SELECT MACHINE.NAME AS SYSTEM_NAME, MACHINE.IP, OS_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 AND DISPLAY_NAME like '%google%') AS DISPLAY_NAME 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 '%Google%')) )) GROUP BY MACHINE.ID ORDER BY SYSTEM_NAME
Try adding a filter in the last section of the report creating wizard.
Filter the software by the publisher name.
Comments:
-
Thanks for the reply, but I already have that setup. Although in my original question I said I have a filter for "Software Publisher = Adobe" I really meant that my filter is "Software Publisher contains Adobe". All that seems to do is select machines that have that Software Publisher but the report then shows all the software installed on any computer that has at least one Adobe product installed. - mikefletcher85 7 years ago
