Using a ticket rule to update license count?
Hey KACE people!!
I'm probably average when it comes to KACE knowledge and SQL expertise... I'm wondering if anyone has had experience with trying to have KACE count up the number of license "slots" that are taken up by devices in the Approved for Device field?? Is that even best practice? We've imported our Microsoft Office licenses into KACE, but some of our licenses have 40+ count and I'm trying to have KACE count them up so we don't have to. The weird part is I've ran the select query through MySQL workbench and it works as intended (without the select subquery), but in the Update query in KACE we get totally different results.
The select query returns the office license ID in the ASSET_DATA_7 table in the form of <TICKET_IDS>:
LEFT JOIN ASSET ON ASSET.ASSET_DATA_ID = ASSET_DATA_7.ID
WHERE ASSET.ASSET_TYPE_ID = 7
The update query is supposed to update a custom field called "Slots Remaining" but its not doing its job:
LEFT JOIN ASSET ON ASSET_DATA_7.ID = ASSET.ASSET_DATA_ID AND ASSET.ASSET_TYPE_ID=7
LEFT JOIN ASSET_ASSOCIATION J3 ON J3.ASSET_ID = ASSET.ID AND J3.ASSET_FIELD_ID=3
LEFT JOIN ASSET A3 ON A3.ID = J3.ASSOCIATED_ASSET_ID
SET FIELD_15 = (SELECT ASSET_DATA_7.FIELD_1 - LENGTH(GROUP_CONCAT(DISTINCT(ASSET.NAME))) - LENGTH(REPLACE(GROUP_CONCAT(DISTINCT(ASSET.NAME)), ',', '') + 1))
WHERE ASSET_DATA_7.ID in (<TICKET_IDS>)
Anyone have any experience with this? Help is always appreciated!
Be the first to answer this question