/build/static/layout/Breadcrumb_cap_w.png

Kace SQL query with Installed Software and Discovered Software

Hello, I'm running a sql query to get a list of all computers with Autodesk software, but the list is comming with "Installed Programs" and "Discovered Software", is it possible to remove the "Discovered Software" from the SQL? I really didn't find a way to identify the difference between this two classification.


select distinct M.NAME COMPUTER, M.USER_LOGGED, M.LAST_INVENTORY, S.NAME SOFTWARE
from ORG1.SAM_MACHINE_JT S_JT
left join ORG1.MACHINE M ON M.ID = S_JT.MACHINE_ID
left join ORG1.MACHINE_CUSTOM_INVENTORY CI ON CI.ID = S_JT.MACHINE_ID
left join CATALOG.SAM_CATALOG S ON S.ID = S_JT.SAM_CATALOG_ID
where (PUBLISHER like '%autodesk%'
and (SAM_TYPE = 'TITLED_APPLICATION' or SAM_TYPE = 'TITLED_SUITE')
and NOT S.LICENSE_TYPE = 'Freeware'
and NOT S.NAME like 'Alias 20%')
ORDER BY COMPUTER


Thank you.

0 Comments   [ + ] Show comments

Answers (1)

Answer Summary:
Posted by: chucksteel 6 years ago
Red Belt
1

Top Answer

First:
Installed Programs are specific software titles along their specific version installed on a computer, e.g. AutoCAD LT 2015 version 20.0.51.0 is distinct from AutoCAD LT 2015 version 20.0.210.0.
Discovered Software groups specific versions and titles into suites, e.g. AutoCAD LT 2015 includes all subversions of 20.x.

While these two are very similar, they can each serve specific purposes. For a report like yours you can use either one, but I generally use the installed programs data because it is easier to access and I frequently want to know the subversion of the software. Here is an example query for Autodesk:

SELECT SOFTWARE.PUBLISHER, SOFTWARE.DISPLAY_NAME, SOFTWARE.DISPLAY_VERSION, MACHINE.NAME AS SYSTEM_NAME, 
MACHINE.IP
FROM MACHINE  
LEFT JOIN MACHINE_SOFTWARE_JT ON (MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID) 
LEFT JOIN SOFTWARE ON (SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID) 
WHERE (SOFTWARE.Publisher like '%Autodesk%')  
GROUP BY MACHINE.ID 
ORDER BY SOFTWARE.PUBLISHER, SOFTWARE.DISPLAY_NAME, SYSTEM_NAME


Comments:
  • Hi Chucksteel, thanks for your reply, helps a lot. Another question about it, is it possible retrieve only the softwares that are no Freeware using your SQL example? In my query I could do it in the table CATALOG.SAM_CATALOG -> LICENSE_TYPE (my skills on sql are limited). - giovanilg 6 years ago
    • The software inventory and software catalog are not related, so there isn't a good way to get the license type information from the catalog if you are reporting on the inventory. - chucksteel 6 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