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.
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:
Please log in to answer
Posted by:
chucksteel
5 years ago
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 5 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 5 years ago
