Can you get a report to only display one Software Title for a device?
I'm trying to create a report that pulls all machines that have software installed from a specific publisher. We have three different software titles that could be installed from this publisher. I can get the report to pull correctly, and it is showing all the devices I expect when compared to the software inventory list inside Kace. However, I can't get the report to just show which of the three titles is installed. If I display the 'Software Titles' I get every bit of software installed, and the same is true for 'Software Version'. I'd like to just display the device name, the name of the software installed (of the three from the publisher) and the version installed. Is this possible? Can I just pull a report against the 'Devices Detected' list at the bottom of the software detail page? I could make 3 different reports, one for each software, but I'd like to see if this is possible before going that route.
Answers (2)
Top Answer
Maybe you can use this SQL and edit it to meet your needs.
select MACHINE.NAME as Computer_Name,
SOFTWARE.DISPLAY_NAME,
SOFTWARE. DISPLAY_VERSION,
MACHINE.IP,
MACHINE.MAC
from SOFTWARE
join MACHINE_SOFTWARE_JT on SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID
join MACHINE on MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID
where SOFTWARE.DISPLAY_NAME like '%MicroMain%' and not IS_PATCH
order by Computer_Name, SOFTWARE.DISPLAY_NAME
There is also the built-in report with the title of "Software Title & Version - Computer List" that may be of interest to you.
You can duplicate it and modify the copy to meet your needs.
Comments:
-
That works great; thanks so much - cheslja09 3 years ago
You may want to provide the following additional information.
SMA version?
The SQL you are currently using?
Comments:
-
Running Version 11.0.273; I made the report with the wizard, but here's the sql
SELECT MACHINE.NAME AS SYSTEM_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 SOFTWARE, MACHINE_SOFTWARE_JT where MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID AND SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID and SOFTWARE.DISPLAY_NAME like '%MicroMain%')) )) GROUP BY MACHINE.ID ORDER BY SYSTEM_NAME - cheslja09 3 years ago
