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.
Answers (3)
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
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 11 months ago