/build/static/layout/Breadcrumb_cap_w.png

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:
Posted by: JasonEgg 8 years ago
Red Belt
0

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
 
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