/build/static/layout/Breadcrumb_cap_w.png

Looking for help to build a custom report for finding software list by system label

I am trying to create a custom report for show me all the software installed on any system with a specific k1000 label attached to it. 


Any SQL report creation help would be appreciated.


0 Comments   [ + ] Show comments

Answers (3)

Answer Summary:
Posted by: KevinG 3 weeks ago
Red Belt
0

I assume you are referring to devices with a particular device label?


Select

MACHINE.NAME,

SOFTWARE.DISPLAY_NAME,

LABEL.NAME

from MACHINE

join MACHINE_SOFTWARE_JT on MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID

join SOFTWARE on SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID

join MACHINE_LABEL_JT on MACHINE_LABEL_JT.MACHINE_ID = MACHINE.ID

join LABEL on LABEL.ID = MACHINE_LABEL_JT.LABEL_ID

where LABEL.NAME = 'TheLableName'    --- <-- Replace with your label name

Posted by: sbaxter 3 weeks ago
Orange Senior Belt
0

Top Answer

Kevin,

Yes.. Thank you.. that is VERY VERY close to what I am looking for.. 

The only thing I need to figure out is how to consolidate the created list...

I need it to show the software titles but then the total number of times it is installed within that device label group.

This report shows every system in that label and all the software from each machine.

I'd rather it was software title and there are 5 installs of it in that label. that would make for a much cleaner and shorter list... 

The first one I ran had 2250 lines of software titles over 15 devices.


Comments:
  • Try the following SQL

    Select
    SOFTWARE.DISPLAY_NAME,
    COUNT(*)
    from MACHINE
    join MACHINE_SOFTWARE_JT on MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID
    join SOFTWARE on SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID
    join MACHINE_LABEL_JT on MACHINE_LABEL_JT.MACHINE_ID = MACHINE.ID
    join LABEL on LABEL.ID = MACHINE_LABEL_JT.LABEL_ID
    where LABEL.NAME = 'TheLableName'
    group by SOFTWARE.DISPLAY_NAME

    Replace "TheLabelName" with your label name. - KevinG 3 weeks ago
Posted by: sbaxter 3 weeks ago
Orange Senior Belt
0


Thank you so much Kevin...  I'm obviously not an SQL guy but this is exactly what I was looking for!!!! 


Comments:
  • Glad to help! - KevinG 3 weeks ago

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ