Systems Management

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>:

SELECT ASSET_DATA_7.ID

FROM ASSET_DATA_7

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:

UPDATE 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 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!