K1000 report to find all computers with specific software installed
Is there any way I can do this? Any help appreciated
I'm trying to create a report that lists all the computers that have software installed by specific publishers. I have managed to create a report using the wizard using software as the topic and devices as the subtopic, but that creates line breaks.
My goal is to have columns: Publisher, Software name, Software version, Device name, IP address. e.g.
|Publisher||Software Name||Software Version||Device Name||IP Address|
|Adobe Systems||Acrobat reader||11.1||Computer 1||126.96.36.199|
|Adobe Systems||Acrobat reader||11.1||Computer 2||188.8.131.52|
|Adobe Systems||Acrobat reader||11.1||Computer 3||184.108.40.206|
|Microsoft Corporation||Microsoft Word||15.38||Computer 2||220.127.116.11|
|Microsoft Corporation||Microsoft Word||15.38||Computer 3||18.104.22.168|
Please log in to answer
Posted by: chucksteel 5 years ago
One issue that you will find is inconsistency with publisher names, but this report generates what you are asking to see:
SELECT SOFTWARE.PUBLISHER, SOFTWARE.DISPLAY_NAME, SOFTWARE.DISPLAY_VERSION, MACHINE.NAME AS SYSTEM_NAME,
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.Publisher like 'Adobe%')
GROUP BY MACHINE.ID
ORDER BY SOFTWARE.PUBLISHER, SOFTWARE.DISPLAY_NAME, SYSTEM_NAME
I thought that the publisher information might be more consistent in the software catalog (as opposed to the software inventory which the above uses) but when I checked the KBSYS.SAM_PUBLISHER table it was just as bad. In my K1000 there are 114 results that start with the word Adobe.