/build/static/layout/Breadcrumb_cap_w.png
06/23/2017 4298 views
Hi,

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.

PublisherSoftware NameSoftware VersionDevice NameIP Address
Adobe SystemsAcrobat reader11.1Computer 11.1.1.1
Adobe SystemsAcrobat reader11.1Computer 21.1.1.2
Adobe SystemsAcrobat reader11.1Computer 31.1.1.3
Microsoft CorporationMicrosoft Word15.38Computer 21.1.1.2
Microsoft CorporationMicrosoft Word15.38Computer 31.1.1.3
Is there any way I can do this? Any help appreciated
2 Comments   [ + ] Show comments

Comments

  • How good are you with MySql scripts? this could be done from there.
    • I figured it could, but I dont know enough to be able to write this myself.
  • How do i use this in the K1000? it scripting?

All Answers

2
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.
Answered 06/23/2017 by: chucksteel
Red Belt

  • Thanks, that is just what I need.
  • 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...
  • 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!
    • 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 -----]
      • 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 ?