Query Software Installed and license
I'm starting in sql and kace, I need help to generate a query that collects the software installed(not discovered) and if the equipment has an allocated license for this software, but if it doesn't have a license, the query should not eliminate this line.
I created the query, but it only brings up when the device has an allocated license, eliminating unlicensed installations and also does not compare the software in cases where I have more than one license allocated on the equipment bringing the wrong license.
SELECT * FROM
FROM MACHINE_SOFTWARE_JT J1
LEFT JOIN MACHINE M ON M.ID = J1.MACHINE_ID
LEFT JOIN SOFTWARE S ON S.ID = J1.SOFTWARE_ID
WHERE (S.DISPLAY_NAME LIKE '%Visual Studio%' )AS Installed
FROM MACHINE M2
LEFT JOIN ASSET ON ASSET.MAPPED_ID = M2.ID AND ASSET.ASSET_TYPE_ID=5
LEFT JOIN ASSET_ASSOCIATION J3 ON J3.ASSOCIATED_ASSET_ID = ASSET.ID AND J3.ASSET_FIELD_ID=3
LEFT JOIN ASSET A3 ON A3.ID = J3.ASSET_ID
LEFT JOIN ASSET_CATALOG_ASSOCIATION J5 ON J5.ASSET_FIELD_ID = 10000 AND J5.ASSET_ID = A3.ID
LEFT JOIN CATALOG.SAM_CATALOG L5 ON L5.ID = J5.ASSOCIATED_CATALOG_ID
WHERE L5.NAME LIKE '%Visual Studio%'
ON Installed.MACHINE = assetinfo3.device
GROUP BY Installed.MACHINE
Be the first to answer this question