Need assistance with a script to find Software Titles with counts for a specific device label, please
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!
Please log in to answer
Posted by: getElementById 4 years ago
This should do it.
You'll need to edit this line to specify your device label: WHERE L.NAME = 'YourLabelNameHere'
S.PUBLISHER, S.DISPLAY_NAME, COUNT(S.DISPLAY_NAME) AS Deployment_Count
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.