/build/static/layout/Breadcrumb_cap_w.png
02/21/2017 4000 views
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

Comments



Community Chosen Answer

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


Answered 02/22/2017 by: chucksteel
Red Belt

  • 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.
    • See this post:
      http://www.itninja.com/question/modifying-an-existing-report-to-target-a-specific-label
  • Thanks for posting this. Its something I have always been meaning to do in our reports and always kept forgetting to look into it
  • Thanks, 2 years later still useful! Why cant we filter this out in the Wizard?
    • That's a great question that you could ask at UserKon.

All Answers

0

Try adding a filter in the last section of the report creating wizard.

Filter the software by the publisher name.

Answered 02/22/2017 by: akmagnum
Red Belt

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