/bundles/itninjaweb/img/Breadcrumb_cap_w.png
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.
Answer Summary:
Cancel
0 Comments   [ - ] Hide Comments

Comments

Please log in to comment

Answer Chosen by the Author


Answers

1
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

Answered 05/04/2018 by: chucksteel
Red Belt

  • 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).
    • 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.
Please log in to comment
Answer this question or Comment on this question for clarity

Answers