K1000 report to find all computers with specific software installed
Hi,
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 | 1.1.1.1 |
| Adobe Systems | Acrobat reader | 11.1 | Computer 2 | 1.1.1.2 |
| Adobe Systems | Acrobat reader | 11.1 | Computer 3 | 1.1.1.3 |
| Microsoft Corporation | Microsoft Word | 15.38 | Computer 2 | 1.1.1.2 |
| Microsoft Corporation | Microsoft Word | 15.38 | Computer 3 | 1.1.1.3 |
2 Comments
[ + ] Show comments
-
How good are you with MySql scripts? this could be done from there. - chris.poston 7 years ago
-
I figured it could, but I dont know enough to be able to write this myself. - BowdenJ 7 years ago
-
How do i use this in the K1000? it scripting? - msouth 6 years ago
Answers (1)
Please log in to answer
Posted by:
chucksteel
7 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,
MACHINE.IP
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 (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.
Comments:
-
Thanks, that is just what I need. - BowdenJ 7 years ago
-
Thank you chucksteel. You just provided help on something that I have sought an answer for for several hours. I appreciate your contribution to OUR success!! Live long and prosper... - upplink 7 years ago
-
Is there a way, how to extend report with multiple Publishers ? As is in original question, Adobe and Microsoft, etc. All in one report. Thank you! - LuckyLuke 5 years ago
-
yes, you simply need to add the additional publishers in the SQL code:
[ ---- snip -----]
WHERE (SOFTWARE.Publisher like 'Adobe%' OR SOFTWARE.Publisher like 'Microsoft%')
[ ---- snip -----] - Nico_K 5 years ago-
Thank you! I just realize, that the answer does not do, what is in original question. It does not show multiple times same computer(in question Computer 2 and 3 as example), with different software. Is there way how to achieve it ? - LuckyLuke 5 years ago
