I need help with a script from the K1000, which allows us to limit devices by device label.  We have a device smart label called servers, and one called workstations.  I need to be able to report what software (with the deployment count) is installed for each label.  We would like the report to show the Software Publisher, Title, Deployment Count and specify the device labels to search against. The canned report "Software Title Deployed Count" has everything except the ability to specify a device label.

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

If there is a better way, please let me know. 

Thanks in advance!

edge_2050
Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answer Chosen by the Author

0
This should do it.
You'll need to edit this line to specify your device label:  WHERE L.NAME = 'YourLabelNameHere'

SELECT 
    S.PUBLISHER, S.DISPLAY_NAME, COUNT(S.DISPLAY_NAME) AS Deployment_Count
FROM 
    SOFTWARE S LEFT JOIN MACHINE_SOFTWARE_JT MS 
ON S.ID = MS.SOFTWARE_ID
    LEFT JOIN MACHINE M ON MS.MACHINE_ID = M.ID 
    LEFT JOIN MACHINE_LABEL_JT ML ON M.ID = ML.MACHINE_ID
    LEFT JOIN LABEL L ON ML.LABEL_ID = L.ID
WHERE L.NAME = 'ML - Server'
GROUP BY S.DISPLAY_NAME
ORDER BY S.DISPLAY_NAME

Let me know how it goes.

Answered 06/08/2016 by: getElementById
Second Degree Blue Belt

  • Thanks getElementByID! It's the joins which get me every time. I will live to KACE another day! Your help is appreciated!
    • Glad it worked! It does return a lot of results. If you wanted to narrow it down to a more specific software list you could add AND S.DISPLAY_NAME LIKE '%flash%' to the end of the where clause. The percent sign is wildcard and flash could be replaced with office, adobe, or whatever is in the title of the software you're looking for.
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