05/03/2023 03:25 AM
Hi Experts,
We are working on Analytics report and we have a use case as follows.
Need list of users who are having more than one role from the same endpoint.
Could you please help with the same query or the logic to achieve this.
Thank you,
Sai Krishna.
Solved! Go to Solution.
05/03/2023 04:30 AM
select u.username,e.endpointname,count(*) from role_user_accounts rua, users u ,roles r ,endpoints e where r.endpointkey=e.endpointkey and r.rolekey=rua.endpointkey and u.userkey =rua.userkey group by u.username,r.role_name,e.endpointname
05/03/2023 04:30 AM
Hello @krishna_sk ,
Could you please try the below query,
SELECT u.username,
Count(r.role_name) AS rolecount,
Group_concat(r.role_name) AS rolename,
e.displayname
FROM users u,
roles r,
role_user_account ra,
accounts a,
endpoints e
WHERE u.userkey = ra.userkey
AND r.rolekey = ra.rolekey
AND ra.accountkey = a.accountkey
AND a.endpointkey = e.endpointkey
AND ra.accountkey IN (SELECT DISTINCT ae1.accountkey
FROM account_entitlements1 ae1,
role_entitlements re
WHERE ae1.entitlement_valuekey =
re.entitlement_valuekey
AND re.rolekey = r.rolekey)
GROUP BY u.username
HAVING Count(r.role_name) > 1
Thanks,
05/03/2023 06:02 AM
Hi,
Could you please try the below query,
SELECT u.username, e.endpointname, COUNT(*) as role_count FROM role_user_accounts rua JOIN users u ON u.userkey = rua.userkey JOIN roles r ON r.rolekey = rua.rolekey JOIN endpoints e ON e.endpointkey = r.endpointkey GROUP BY u.username, e.endpointname HAVING COUNT(*) > 1;
05/03/2023 09:53 AM
Thank you all for your help😊. We achieved the usecase using the below query.
select u.username, u.displayname,u.email,e.endpointname,group_concat(r.role_name) as 'Role Names' from users u join role_user_account rua on u.userkey = rua.userkey join roles r on rua.rolekey = r.rolekey join endpoints e on r.endpointkey = e.endpointkey where u.statuskey=1 group by u.username,e.endpointname having count(*) > 1 order by u.username