/build/static/layout/Breadcrumb_cap_w.png

Scripting Question


Help with SQL Query for Software Inventory for multiple device labels

12/11/2015 1644 views
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
Answer Summary:
0 Comments   [ + ] Show comments

Comments


Answer Chosen by the Author

0
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

Answered 12/11/2015 by: JasonEgg
Red Belt

  • 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, 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.

Don't be a Stranger!

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

Sign up! or login

View more:

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