We are delighted to share our new EIC Delivery Methodology for efficiently managing Saviynt Implementations and delivering quick time to value. CLICK HERE.

Need Help on Analytics Query for Users having more than one role in Single endpoint

krishna_sk
New Contributor III
New Contributor III

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.

4 REPLIES 4

rushikeshvartak
All-Star
All-Star

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


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

sudeshjaiswal
Saviynt Employee
Saviynt Employee

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,

If you find the above response useful, Kindly Mark it as "Accept As Solution".

DixshantValecha
Saviynt Employee
Saviynt Employee

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;

krishna_sk
New Contributor III
New Contributor III

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