Help with SQL Query for Software Inventory for multiple device labels
I need help with a SQL query which is similar to the canned "Software Title Deployed Count" or "Software Inventory By Vendor" reports in the K1000, I need to be able to pull the software inventory from devices from multiple device labels, but not from all devices in the inventory. Any help which can be offered, is appreciated.
Thanks in advance.
(Software Title Deployed Count)
Select PUBLISHER, DISPLAY_NAME, COUNT(DISPLAY_NAME) as Deployment_Count from (SOFTWARE, MACHINE_SOFTWARE_JT)
where SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID
and not IS_PATCH
GROUP BY DISPLAY_NAME
order by DISPLAY_NAME
(Software Inventory By Vendor)
Select PUBLISHER, DISPLAY_NAME, COUNT(DISPLAY_NAME) as Deployed_Count from (SOFTWARE, MACHINE_SOFTWARE_JT)
where SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID
and not IS_PATCH
GROUP BY DISPLAY_NAME
order by PUBLISHER, DISPLAY_NAME
0 Comments
[ + ] Show comments
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
JasonEgg
8 years ago
Top Answer
Give this a try:
SELECT PUBLISHER, DISPLAY_NAME, COUNT(DISPLAY_NAME) AS Deployed_Count
FROM SOFTWARE
LEFT JOIN MACHINE_SOFTWARE_JT on SOFTWARE.ID=MACHINE_SOFTWARE_JT.SOFTWARE_ID
LEFT JOIN MACHINE on MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID
LEFT JOIN MACHINE_LABEL_JT on MACHINE.ID=MACHINE_LABEL_JT.MACHINE_ID
LEFT JOIN LABEL on MACHINE_LABEL_JT.LABEL_ID = LABEL.ID
WHERE SOFTWARE.IS_PATCH = 0
AND LABEL.NAME = 'YOUR_LABEL_HERE'
GROUP BY DISPLAY_NAME
ORDER BY PUBLISHER, DISPLAY_NAME
Comments:
-
It'll take a little more tweaking if you want to include multiple device labels. You could also be lazy and created a Smart Label which encompasses the other labels (make sure to change evaluation order) - JasonEgg 8 years ago
-
@JasonEgg, you Rock! THank you for the reply! I used your query and changed
"AND LABEL.NAME IN ('LABEL NAME','LABEL NAME','LABEL NAME')"
This gave me the information I am expecting. Thank you for your help with this. - eschmidt2050 8 years ago
