/build/static/layout/Breadcrumb_cap_w.png

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:
Posted by: getElementById 7 years ago
Third Degree Blue Belt
0

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 7 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 7 years ago
 
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