Scripting

Hello.

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

(

SELECT 

M.NAME 'MACHINE',

S.PUBLISHER 'PUBLISHER',

DISPLAY_NAME

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

RIGHT JOIN

(

SELECT 

M2.NAME 'device',

A3.NAME 'lic_name',

L5.NAME 'soft_name',

L5.ID 'lic_id'

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%'

)AS assetinfo3

ON Installed.MACHINE = assetinfo3.device

GROUP BY Installed.MACHINE