Software License Compliance Filtered by Tags or machine name
Hello!
We are starting to use Kace to check the licences we are using. But I found myself with a problem:
We have two locations, Spain and Portugal, there is no diference between them just ip addresses and who pays the licenses.
So using the Software License Compliance reports I would like to be able to tell the licenses that are being used by computers in Spain and by computers in Portugal.
Can you give me any clue as for where to start? I have created smart labels to identify the locations by IP.
Thank you very much!
Answers (2)
Can you post your current SQL query. You should just need to join the label table. Then add something like label.name like '%label_name%'.
Comments:
-
SELECT M.NAME, IP
FROM MACHINE M
LEFT JOIN MACHINE_LABEL_JT ML ON ML.MACHINE_ID = M.ID
LEFT JOIN LABEL L ON L.ID = ML.LABEL_ID
WHERE L.NAME LIKE 'LABEL_NAME%' - dugullett 11 years ago
I haven't done a SQL query, I've been playing with the wizard, as my SQL knowledge is = null
Comments:
-
Ok. If you've created the report in the wizard click the link to the report. This will open up the report and provide you with the SQL Select Statement. Copy and paste that here. Reports for assets are more custom based on your Kbox. So in order for it to be accurate I'll need that. - dugullett 11 years ago
-
here is the SQL Statement I want to be able to filter the results so it shows the license install count for the machines that have a give label.
SELECT ASSET.NAME AS ASSET_NAME,(SELECT COUNT(DISTINCT MACHINE_SOFTWARE_JT.MACHINE_ID) FROM ASSET_ASSOCIATION JX2
LEFT JOIN ASSET AX2 ON AX2.ID = JX2.ASSOCIATED_ASSET_ID
LEFT JOIN MACHINE_SOFTWARE_JT ON (SOFTWARE_ID = AX2.MAPPED_ID OR SOFTWARE_ID IN
(SELECT SOFTWARE_ID FROM ASSET_DATA_6 JOIN SOFTWARE_LABEL_JT
ON LABEL_ID = ASSET_DATA_6.FIELD_10001
WHERE ASSET_DATA_6.ID = AX2.ASSET_DATA_ID))
WHERE JX2.ASSET_ID = ASSET.ID AND JX2.ASSET_FIELD_ID=2
) AS INSTALLED_COUNT FROM ASSET_DATA_7 LEFT JOIN ASSET ON ASSET_DATA_7.ID = ASSET.ASSET_DATA_ID AND ASSET.ASSET_TYPE_ID=7 WHERE ((1 in (select 1 from ASSET_ASSOCIATION J2, ASSET A2, ASSET_DATA_6 AD2 where J2.ASSET_ID = ASSET.ID AND J2.ASSET_FIELD_ID=2 AND A2.ID = J2.ASSOCIATED_ASSET_ID and AD2.ID = A2.ASSET_DATA_ID and A2.NAME = '')) ) ORDER BY ASSET_NAME - OnTiltSoon 11 years ago