03/21/2023 03:37 AM
The usecase is that we need to populate a certain endpoint (App A) accounts' customproperty31 with some value. This endpoint has other endpoint names as account names - like app1, app2 are different accounts on this endpoint, and from the tasks table, we want to pull in certification revoke tasks and populate the cp31 of these accounts thus.
App A - main endpoint
App1, App2 - different accounts on App A and are also actual endpoints that are part of campaigns
It's a little complicated to explain, but this is the query I have:
join arstasks ar on ar.endpoint = a.customproperty1
join account_entitlements1 a1 on a1.accountkey = ar.ACCOUNTKEY
join entitlement_values ev on ev.ENTITLEMENT_VALUEKEY = a1.entitlement_valuekey
a.customproperty31 = GROUP_CONCAT("[",ar.ACCOUNTNAME,"+",ev.ENTITLEMENT_VALUE,"]")
a.endpointkey in ('417') and ar.endpoint = a.customproperty1 and
ar.source in ('CERTIFICATION')
and ar.ENDPOINT in ('349','346','35','366','71','343','388') group by ar.endpoint
where ('349','346','35','366','71','343','388') are the endpointkeys of the endpoints that are accounts on the main endpoint (endpointkey = 417). The query is running successfully in custom query job but the values themselves are not being populated. Does anyone have any input on this?
03/21/2023 04:41 AM - edited 03/21/2023 05:03 AM
Group_concat cannot be used in update query in mysql in this way, you would need write it in a different query to get this updated.
UPDATE accounts a
SELECT ar.endpoint, GROUP_CONCAT(DISTINCT CONCAT('[', ar.ACCOUNTNAME, '+', ev.ENTITLEMENT_VALUE, ']')) AS concatenated_values
FROM arstasks ar
JOIN account_entitlements1 a1 ON a1.accountkey = ar.ACCOUNTKEY
JOIN entitlement_values ev ON ev.ENTITLEMENT_VALUEKEY = a1.entitlement_valuekey
WHERE ar.source = 'ZERODAY' AND ar.ENDPOINT = 2
GROUP BY ar.endpoint
) AS sub ON subq.endpoint = a.endpointkey
SET a.customproperty31 = sub.concatenated_values
WHERE a.endpointkey = 2;
Note: Check the select query inside to get the exact result set and then try to run the update query as per your need.