/build/static/layout/Breadcrumb_cap_w.png

Kace report for computers with specific software installed to also display that software title?

I'm pretty terrible with SQL (its been many years). So I used kace's wizard to make a report for computers that had software installed by a specific publisher. I included the Software Titles and Software Version to be displayed in the report only to find out when I ran it that the report displayed ALL the software on every computer that was on the report. How would I have the report only display the software titles that matched the Software Publisher? Here is how I have the report set up now in case that helps:

Fields to display
    IP Address, System Name, User Name, Service Tag, Software Title, Software Version
Column Order
    <Same order as the fields to display above>
Sorted by System Name
Filters
    Label Names = BU_Machines
    Software Publisher = Adobe

I looked at the SQL and it looks like the wizard made it over complicated. Any help would be appreciated. Thanks!


0 Comments   [ + ] Show comments

Answers (3)

Posted by: chucksteel 7 years ago
Red Belt
3
I believe that you will have to do this with SQL. Here is one approach that changes the join statement to the SOFTWARE table to only include Adobe in the publisher:
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
Another approach would be to use a sub select that pulls the software titles per machine and then groups everything by machine again:
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 4 years ago
    • That's a great question that you could ask at UserKon. - chucksteel 4 years ago
Posted by: lama01 3 years ago
Second Degree Green Belt
1

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

Posted by: akmagnum 7 years ago
Red Belt
0

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