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:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

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
Fourth Degree Green 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.
Please log in to comment

Answers

Answer this question or Comment on this question for clarity
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share