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!
edge_2050
0 Comments
[ + ] Show comments
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
getElementById
8 years ago
Top Answer
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.
Comments:
-
Thanks getElementByID! It's the joins which get me every time. I will live to KACE another day! Your help is appreciated! - eschmidt2050 8 years ago
-
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. - getElementById 8 years ago
