Alert sent when software asset reaches license limit
We have various software assets set up that are tied to license limits, e.g. Adobe Acrobat / Microsoft Office / etc.
Is it possible to have an email alert sent when the number of software installs reaches the limit / threshold? Or do we just have to keep checking it in Kbox?
Thanks.
Is it possible to have an email alert sent when the number of software installs reaches the limit / threshold? Or do we just have to keep checking it in Kbox?
Thanks.
0 Comments
[ - ] Hide Comments

so that the conversation will remain readable.
Answer this question
or Comment on this question for clarity
Answers
Use the following query in the computer notification, and select the frequency it will give u the list of record where license remaining is zero or less then zero.
select *
from(SELECT ASSET.NAME AS NAME,
COUNT(DISTINCT MACHINE_SOFTWARE_JT.MACHINE_ID) AS INSTALLED,
ASSET_DATA_7.FIELD_1 AS OWNED,
ASSET_DATA_7.FIELD_1 - COUNT(DISTINCT MACHINE_SOFTWARE_JT.MACHINE_ID) AS REMAINING,
A8.NAME AS VENDOR,
ASSET_DATA_7.FIELD_9 AS PURCHASE_ORDER_NUM,
ASSET_DATA_7.FIELD_11 AS NOTES
FROM ASSET_DATA_7
LEFT JOIN ASSET ON ASSET_DATA_7.ID = ASSET.ASSET_DATA_ID AND ASSET.ASSET_TYPE_ID=7
LEFT JOIN ASSET_ASSOCIATION J8 ON J8.ASSET_ID = ASSET.ID AND J8.ASSET_FIELD_ID=8
LEFT JOIN ASSET A8 ON A8.ID = J8.ASSOCIATED_ASSET_ID
LEFT JOIN ASSET_ASSOCIATION J2 ON J2.ASSET_ID = ASSET.ID AND J2.ASSET_FIELD_ID=2
LEFT JOIN ASSET A2 ON A2.ID = J2.ASSOCIATED_ASSET_ID
LEFT JOIN ASSET_ASSOCIATION JX2 ON JX2.ASSET_ID = ASSET.ID AND JX2.ASSET_FIELD_ID=2
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))
GROUP BY ASSET_DATA_7.ID ORDER BY REMAINING,NAME asc) A
where Remaining <=0
select *
from(SELECT ASSET.NAME AS NAME,
COUNT(DISTINCT MACHINE_SOFTWARE_JT.MACHINE_ID) AS INSTALLED,
ASSET_DATA_7.FIELD_1 AS OWNED,
ASSET_DATA_7.FIELD_1 - COUNT(DISTINCT MACHINE_SOFTWARE_JT.MACHINE_ID) AS REMAINING,
A8.NAME AS VENDOR,
ASSET_DATA_7.FIELD_9 AS PURCHASE_ORDER_NUM,
ASSET_DATA_7.FIELD_11 AS NOTES
FROM ASSET_DATA_7
LEFT JOIN ASSET ON ASSET_DATA_7.ID = ASSET.ASSET_DATA_ID AND ASSET.ASSET_TYPE_ID=7
LEFT JOIN ASSET_ASSOCIATION J8 ON J8.ASSET_ID = ASSET.ID AND J8.ASSET_FIELD_ID=8
LEFT JOIN ASSET A8 ON A8.ID = J8.ASSOCIATED_ASSET_ID
LEFT JOIN ASSET_ASSOCIATION J2 ON J2.ASSET_ID = ASSET.ID AND J2.ASSET_FIELD_ID=2
LEFT JOIN ASSET A2 ON A2.ID = J2.ASSOCIATED_ASSET_ID
LEFT JOIN ASSET_ASSOCIATION JX2 ON JX2.ASSET_ID = ASSET.ID AND JX2.ASSET_FIELD_ID=2
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))
GROUP BY ASSET_DATA_7.ID ORDER BY REMAINING,NAME asc) A
where Remaining <=0
Please log in to comment
Comments