/build/static/layout/Breadcrumb_cap_w.png

Scripting Question


Query Software Installed and license

01/29/2020 182 views

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

2 Comments   [ + ] Show comments

Comments

  • Any particular reason you aren't using the licensing modules in Asset Management?
  • yes, because I also need to see unlicensed installations, so I look for the installations first and after the licenses.
    • The licensing modules will help you with that.
      You associate a license with a title from the Catalog
      The SMA finds the machines with that software
      You assign licenses to computers, then can easily identify unused licenses and unlicensed computers.
      There's even a fancy license compliance dashboard to show you how screwed you are if a vendor comes to perform an audit.

Be the first to answer this question

 
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