Hell,
as we've upgraded to EIC, some of our crucial "Execute Extension Queries" needs to be updated/overwritten to be reused as an "Enhanced Query Execution job" .
Can some please help us here and tell us how this can be managed?
Please see for example the query below.
This query contains several Update parameters and is used to update newly added users and to assign them to an "Organization" and other things to make the users fully available in Saviynt.
UPDATE users u
SET u.customproperty35 =
(SELECT GROUP_CONCAT(DISTINCT user_groups.user_groupname ORDER BY user_groups.user_groupname ASC SEPARATOR ' ') AS 'Local Mitigating Controls Groups'
FROM usergroup_users, user_groups
WHERE user_groups.usergroupkey = usergroup_users.user_groupkey
AND usergroup_users.userkey = u.userkey )
WHERE u.userkey IN (SELECT userkey FROM usergroup_users, user_groups WHERE usergroup_users.user_groupkey = user_groups.usergroupkey AND (user_groupname LIKE 'WB-NL %'));
UPDATE users u
SET customproperty36 =
(select * from (select GROUP_CONCAT(uu.username ORDER BY uu.username ASC SEPARATOR ',') AS 'KeyUsersUsernamelList'
from usergroup_users ugu, user_groups ug, users uu
WHERE uu.userkey = ugu.userkey
and ugu.user_groupkey = ug.usergroupkey
AND ug.user_groupname = 'WB-NL') as subResult)
where u.customproperty10 = 1101;
UPDATE users u
SET customproperty37 =
(select * from (select GROUP_CONCAT(uu.email ORDER BY uu.email ASC SEPARATOR ',') AS 'KeyUsersEmailList'
from usergroup_users ugu, user_groups ug, users uu
WHERE uu.userkey = ugu.userkey
and ugu.user_groupkey = ug.usergroupkey
AND ug.user_groupname = 'WB-NL') as result)
where u.customproperty10 = 1101;
UPDATE users u
SET customproperty38 =
(select * from (select GROUP_CONCAT(uu.username ORDER BY uu.username ASC SEPARATOR ',') AS 'CFOUsernameList'
from usergroup_users ugu, user_groups ug, users uu
WHERE uu.userkey = ugu.userkey
and ugu.user_groupkey = ug.usergroupkey
AND ug.user_groupname = 'WB-NL_financedirector') as subResult)
where u.customproperty10 = 1101;
UPDATE users u
SET customproperty39 =
(select * from (select GROUP_CONCAT(uu.email ORDER BY uu.email ASC SEPARATOR ',') AS 'CFOEmailList'
from usergroup_users ugu, user_groups ug, users uu
WHERE uu.userkey = ugu.userkey
and ugu.user_groupkey = ug.usergroupkey
AND ug.user_groupname = 'WB-NL_financedirector') as subResult)
where u.customproperty10 = 1101;
UPDATE users SET customproperty33 = SYSDATE() +0 where customproperty10 = 1101;
UPDATE accounts a, users u, user_accounts ua
SET a.customproperty11 = 'WB-NL'
WHERE u.userkey = ua.userkey
and ua.accountkey = a.accountkey
and a.endpointkey !=2
and u.customproperty10 = 1101;
UPDATE accounts a, users u, user_accounts ua
SET a.customproperty10 = 1101
WHERE u.userkey = ua.userkey
and ua.accountkey = a.accountkey
and a.endpointkey !=2
and u.customproperty10 = 1101;
UPDATE users SET customer = 66
WHERE customproperty16 IN ('FUL','FAC','KIO','EXT')
AND customproperty10 = 1101;
UPDATE users u, customer c SET u.customproperty20 = c.customername
WHERE c.customerkey = u.customer
AND u.customproperty10 = 1101;
UPDATE users u
SET customproperty45 =
(select * from (select GROUP_CONCAT(uu.email ORDER BY uu.email ASC SEPARATOR ', ') AS 'KeyUsersEmailList'
from usergroup_users ugu, user_groups ug, users uu
WHERE uu.userkey = ugu.userkey
and ugu.user_groupkey = ug.usergroupkey
AND ug.user_groupname = 'WB-NL') as result)
where u.customproperty10 = 1101;
Thank you for your help.
BR, Michael Schmid
