Add Access Actionable Analytics

[void0703]

Hi All,

I have sql query where i have use group concat to add all those entitlements that i want to give a access to the user but i think group concat doesn't work can you please help me with the query.

Query: 
SELECT U.USERNAME,U.USERKEY AS userKey, A.ACCOUNTKEY AS acctKey ,A.NAME AS accName,(SELECT GROUP_CONCAT(RE.ENTITLEMENT_VALUEKEY) FROM ROLES R JOIN ROLE_ENTITLEMENTS RE ON R.ROLEKEY = RE.ROLEKEY WHERE R.ROLE_NAME = 'Support') AS entvaluekey FROM
ACCOUNTS A
JOIN USER_ACCOUNTS UA ON UA.ACCOUNTKEY = A.ACCOUNTKEY
JOIN USERS U ON U.USERKEY = UA.USERKEY
JOIN ROLE_USER_ACCOUNT RA ON U.USERKEY = RA.USERKEY
JOIN ROLES R ON RA.ROLEKEY = R.ROLEKEY
WHERE U.STATUSKEY = 1
AND U.JOBCODE IN ('50')
GROUP BY U.USERNAME
HAVING MAX(CASE WHEN R.ROLE_NAME IN ('Support') THEN 1 ELSE 0 END) = 0;

I want to give access of all those entitlements from the 'Support' Role to particular user using actionable analytics.

Thanks

[CR]

SELECT U.USERNAME,U.USERKEY AS userKey, A.ACCOUNTKEY AS acctKey ,A.NAME AS accName,(SELECT GROUP_CONCAT(RE.ENTITLEMENT_VALUEKEY) as 'EntValuekey' FROM ROLES R JOIN ROLE_ENTITLEMENTS RE ON R.ROLEKEY = RE.ROLEKEY WHERE R.ROLE_NAME = 'TechSupport') AS entvaluekey FROM
ACCOUNTS A
JOIN USER_ACCOUNTS UA ON UA.ACCOUNTKEY = A.ACCOUNTKEY
JOIN USERS U ON U.USERKEY = UA.USERKEY
JOIN ROLE_USER_ACCOUNT RA ON U.USERKEY = RA.USERKEY
JOIN ROLES R ON RA.ROLEKEY = R.ROLEKEY
WHERE U.STATUSKEY = 1
AND U.JOBCODE IN ('50')
GROUP BY U.USERNAME
HAVING MAX(CASE WHEN R.ROLE_NAME IN ('Support') THEN 1 ELSE 0 END) = 0;

[void0703]

Have you done any changes in the query that I submitted ?

[CR]

yes given alias name

[void0703]

This is not working as the group_concat is giving entvaluekey together so when i run the analytics query it gives a message 

Query returns 

USERNAME USERKEY ACCTKEY ACCNAME ENTVALUEKEY

23452

3242

38732

1224

833,905,1951,1157,2028

Analytics Actionable returns 

Saviynt Security Manager

Alert: Control execution is taking longer than expected. You will be notified when the result is ready

[rushikeshvartak]

ENTVALUEKEY can't be comma seprated.

[naveenss]

Hi @void0703  can you try below?

SELECT U.USERNAME,U.USERKEY AS userKey, A.ACCOUNTKEY AS acctKey ,A.NAME AS accName,RE.ENTITLEMENT_VALUEKEY as entvaluekey FROM
ACCOUNTS A
JOIN USER_ACCOUNTS UA ON UA.ACCOUNTKEY = A.ACCOUNTKEY
JOIN USERS U ON U.USERKEY = UA.USERKEY
JOIN ROLE_USER_ACCOUNT RA ON U.USERKEY = RA.USERKEY
JOIN ROLES R ON RA.ROLEKEY = R.ROLEKEY JOIN ROLE_ENTITLEMENTS RE on R.ROLEKEY=RE.ROLEKEY
WHERE U.STATUSKEY = 1
AND U.JOBCODE IN ('50')
GROUP BY U.USERNAME
HAVING MAX(CASE WHEN R.ROLE_NAME IN ('Support') THEN 1 ELSE 0 END) = 0;