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

Query to get all the roles of type 'Enterprise'

SriRanga
Regular Contributor
Regular Contributor

HI Team,

Could you please help me with a query which I can use to get roles of type 'Enterprise' with few other columns like username, rolename, owner of that role, users associated with that role?

I have tried below query and getting result-

select r.ROLE_NAME as enterpriseRoleName, u.username as roleowner, ro.RANK from roles r, role_owners ro, users u where r.ROLEKEY=ro.ROLEKEY and u.USERKEY=ro.USERKEY and r.ROLETYPE='4'

 

I want users associated with the role. How can U get that ?

 

Thanks,

Amit Aware

17 REPLIES 17

SriRanga
Regular Contributor
Regular Contributor

Adding to previous update. I have tried with below query to get users associated with the Role-

 

select r.ROLE_NAME as enterpriseRoleName, u.username as roleowner, ro.RANK, uu.DISPLAYNAME as account from roles r, role_owners ro, users u, role_user_account rua, users uu where r.ROLEKEY=ro.ROLEKEY and u.USERKEY=ro.USERKEY and rua.USERKEY=uu.USERKEY and r.ROLETYPE='4'

 

I could get the below result but ACCOUNT column is blank. Here in ACCOUNT column I am trying to fetch users associated with Enterprise role we are getting in the result.

 

Thanks,

AMit Aware

Hi @SriRanga ,

Please try to use the queries in analytics and export the result set if you're facing issues to do so in data analyzer if you need the data ASAP.

Thanks,


Regards,
Pruthvi

sudeshjaiswal
Saviynt Employee
Saviynt Employee

Hello @SriRanga,

Could you please try the below query and let me know if it works?

Sample 1 :
SELECT Group_concat(DISTINCT u.username)    AS 'users',
       r.role_name                          AS enterpriseRoleName,
       (SELECT u1.username
        FROM   users u1,
               role_owners ro
        WHERE  r.rolekey = ro.rolekey
               AND u1.userkey = ro.userkey) AS roleowner
FROM   users u,
       roles r,
       role_user_account rua,
       accounts a
WHERE  u.userkey = rua.userkey
       AND r.rolekey = rua.rolekey
       AND a.accountkey = rua.accountkey 


Sample 2:
SELECT r.role_name                           AS enterpriseRoleName,
       u.username                            AS roleowner,
       ro.rank,
       Group_concat(DISTINCT uu.displayname) AS account
FROM   roles r,
       role_owners ro,
       users u,
       role_user_account rua,
       users uu
WHERE  r.rolekey = ro.rolekey
       AND u.userkey = ro.userkey
       AND rua.userkey = uu.userkey 


Thanks,

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

@sudeshjaiswal - Thanks for the queries.

Sample1: getting below error for this query

SriRanga_0-1685102193688.png

 

Sample2: getting below as result for this

SriRanga_2-1685102329578.png

 

Thanks,

Amit Aware

sudeshjaiswal
Saviynt Employee
Saviynt Employee

Hello @SriRanga 

Both queries work fine for me, the only difference is that I grouped them in sample 2.
Please find the attached output of the sample 2 query,

sudeshjaiswal_1-1685105150964.png

 

Please try the sample 2 query in analytics..

Thanks,

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

dgandhi
All-Star
All-Star

Can you try below?

Select distinct a.accountkey as acctKey , a.name as accName,r.role_name,u.username, u.userkey as userKey,
CASE
WHEN r.status = 0 THEN 'INACTIVE'
WHEN r.status = 1 THEN 'ACTIVE'
WHEN r.status = 2 THEN 'COMPOSING'
WHEN r.status = 3 THEN 'SENTFORAPPROVAL'
WHEN r.status = 4 THEN 'DECOMMISION'
WHEN r.status = 5 THEN 'MINING'
END AS 'Role Status',
CASE
WHEN r.roletype = 1 THEN 'Enabler'
WHEN r.roletype = 2 THEN 'Transactional'
WHEN r.roletype = 3 THEN 'FireFighter'
WHEN r.roletype = 4 THEN 'Enterprise'
WHEN r.roletype = 5 THEN 'Application'
WHEN r.roletype = 6 THEN 'EntitlementBasedRole'
END AS 'Roletype'
from roles r,role_user_account rua, users u,user_accounts ua, accounts a
where rua.accountkey = a.accountkey
and re1.rolekey = rua.rolekey
and re1.ROLEKEY = r.ROLEKEY
and u.userkey = ua.userkey and ua.accountkey = a.accountkey
and r.rolekey = '4'

 

 

Thanks,
Devang Gandhi
If this reply answered your question, please Accept As Solution and give Kudos to help others who may have a similar problem.

SriRanga
Regular Contributor
Regular Contributor

@dgandhi --  What the below highlighted object re1 is for.

from roles r,role_user_account rua, users u,user_accounts ua, accounts a
where rua.accountkey = a.accountkey
and re1.rolekey = rua.rolekey
and re1.ROLEKEY = r.ROLEKEY
and u.userkey = ua.userkey and ua.accountkey = a.accountkey
and r.rolekey = '4'

 

We re getting below error-

Error : Unknown column 're1.rolekey' in 'where clause'

 

Thanks,

Amit Aware

yogesh
Regular Contributor III
Regular Contributor III

This is the query to get users assigned to each enterprise role:

select
  r.role_name,
  case
    when r.ROLETYPE = 5 then 'APPLICATION ROLE'
    when r.ROLETYPE = 4 then 'ENTERPRISE ROLE'
    else r.ROLETYPE
  end as ROLE_TYPE,
  case
    when r.SOX_CRITICAL is null then 'NONE'
    when r.SOX_CRITICAL = 0 then 'NONE'
    when r.SOX_CRITICAL = 1 then 'VERY LOW'
    when r.SOX_CRITICAL = 2 then 'LOW'
    when r.SOX_CRITICAL = 3 then 'MEDIUM'
    when r.SOX_CRITICAL = 4 then 'HIGH'
    when r.SOX_CRITICAL = 5 then 'VERY HIGH'
    else r.SOX_CRITICAL
  end as SOX_CRITICALITY,
  u.username,
  u.displayname,
  u.email,
  case
    when u.statuskey = 1 then 'Active'
    when u.statuskey = 0 then 'Inactive'
    else u.statuskey
  end as user_status
from
  role_user_account rua
  left join roles r on r.rolekey = rua.rolekey
  left join users u on u.userkey = rua.userkey
where
  r.status = 1
  and r.roletype = 4

SriRanga
Regular Contributor
Regular Contributor

@yogesh -- Thanks for the query it is giving a result.

Actually we need a data here like if there is any enterprise roles which does have any active user associated with it. We need to add users to all those enterprise roles.

So in addition to above query which you have shared, can you please help me to get the enterprise roles who do not have any active users associated with it ?

 

Thanks for your Patience,

Amit Aware 

yogesh
Regular Contributor III
Regular Contributor III
select
  r.role_name,
  case
    when r.ROLETYPE = 5 then 'APPLICATION ROLE'
    when r.ROLETYPE = 4 then 'ENTERPRISE ROLE'
    else r.ROLETYPE
  end as ROLE_TYPE,
  case
    when r.SOX_CRITICAL is null then 'NONE'
    when r.SOX_CRITICAL = 0 then 'NONE'
    when r.SOX_CRITICAL = 1 then 'VERY LOW'
    when r.SOX_CRITICAL = 2 then 'LOW'
    when r.SOX_CRITICAL = 3 then 'MEDIUM'
    when r.SOX_CRITICAL = 4 then 'HIGH'
    when r.SOX_CRITICAL = 5 then 'VERY HIGH'
    else r.SOX_CRITICAL
  end as SOX_CRITICALITY,
  u.username,
  u.displayname,
  u.email,
  case
    when u.statuskey = 1 then 'Active'
    when u.statuskey = 0 then 'Inactive'
    else u.statuskey
  end as user_status
from
  roles r
  left join role_user_account rua on r.rolekey = rua.rolekey
  left join users u on u.userkey = rua.userkey
where
  r.status = 1
  and r.roletype = 4
  and u.userkey is null

 please try the below query :-

SELECT
r.role_name,
CASE r.ROLETYPE
WHEN 5 THEN 'APPLICATION ROLE'
WHEN 4 THEN 'ENTERPRISE ROLE'
ELSE CAST(r.ROLETYPE AS VARCHAR(20))
END AS ROLE_TYPE,
CASE r.SOX_CRITICAL
WHEN NULL THEN 'NONE'
WHEN 0 THEN 'NONE'
WHEN 1 THEN 'VERY LOW'
WHEN 2 THEN 'LOW'
WHEN 3 THEN 'MEDIUM'
WHEN 4 THEN 'HIGH'
WHEN 5 THEN 'VERY HIGH'
ELSE CAST(r.SOX_CRITICAL AS VARCHAR(20))
END AS SOX_CRITICALITY,
u.username,
u.displayname,
u.email,
CASE u.statuskey
WHEN 1 THEN 'Active'
WHEN 0 THEN 'Inactive'
ELSE CAST(u.statuskey AS VARCHAR(20))
END AS user_status
FROM
role_user_account rua
LEFT JOIN roles r ON r.rolekey = rua.rolekey
LEFT JOIN users u ON u.userkey = rua.userkey
WHERE
r.status = 1
AND r.roletype = 4
AND NOT EXISTS (
SELECT 1
FROM users u2
WHERE u2.userkey = u.userkey
AND u2.statuskey = 1
);

@DixshantValecha -

 

We are getting below error while executing the query-

 

SriRanga_0-1685355363298.png

 

SELECT r.role_name,
       CASE r.roletype
         WHEN 5 THEN 'APPLICATION ROLE'
         WHEN 4 THEN 'ENTERPRISE ROLE'
         ELSE r.roletype
       END AS ROLE_TYPE,
       CASE r.sox_critical
         WHEN NULL THEN 'NONE'
         WHEN 0 THEN 'NONE'
         WHEN 1 THEN 'VERY LOW'
         WHEN 2 THEN 'LOW'
         WHEN 3 THEN 'MEDIUM'
         WHEN 4 THEN 'HIGH'
         WHEN 5 THEN 'VERY HIGH'
         ELSE r.sox_critical 
       END AS SOX_CRITICALITY,
       u.username,
       u.displayname,
       u.email,
       CASE u.statuskey
         WHEN 1 THEN 'Active'
         WHEN 0 THEN 'Inactive'
         ELSE u.statuskey
       END AS user_status
FROM   role_user_account rua
       LEFT JOIN roles r
              ON r.rolekey = rua.rolekey
       LEFT JOIN users u
              ON u.userkey = rua.userkey
WHERE  r.status = 1
       AND r.roletype = 4
       AND NOT EXISTS (SELECT 1
                       FROM   users u2
                       WHERE  u2.userkey = u.userkey
                              AND u2.statuskey = 1); 


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

DixshantValecha
Saviynt Employee
Saviynt Employee

Hi @SriRanga,

Please let me know if you are getting the same error for both the query mentioned above.

l@DixshantValecha - For below query we are able to get the result-

select
  r.role_name,
  case
    when r.ROLETYPE = 5 then 'APPLICATION ROLE'
    when r.ROLETYPE = 4 then 'ENTERPRISE ROLE'
    else r.ROLETYPE
  end as ROLE_TYPE,
  case
    when r.SOX_CRITICAL is null then 'NONE'
    when r.SOX_CRITICAL = 0 then 'NONE'
    when r.SOX_CRITICAL = 1 then 'VERY LOW'
    when r.SOX_CRITICAL = 2 then 'LOW'
    when r.SOX_CRITICAL = 3 then 'MEDIUM'
    when r.SOX_CRITICAL = 4 then 'HIGH'
    when r.SOX_CRITICAL = 5 then 'VERY HIGH'
    else r.SOX_CRITICAL
  end as SOX_CRITICALITY,
  u.username,
  u.displayname,
  u.email,
  case
    when u.statuskey = 1 then 'Active'
    when u.statuskey = 0 then 'Inactive'
    else u.statuskey
  end as user_status
from
  roles r
  left join role_user_account rua on r.rolekey = rua.rolekey
  left join users u on u.userkey = rua.userkey
where
  r.status = 1
  and r.roletype = 4
  and u.userkey is null

DixshantValecha
Saviynt Employee
Saviynt Employee

Thanks for the update @SriRanga 

sudeshjaiswal
Saviynt Employee
Saviynt Employee

Hello,

The below query retrieves roles of the 'Enterprise' type along with additional columns such as username, role name, owner of each role, and the users associated with those roles The aim is to obtain the users associated with the 'Enterprise' role from the results obtained in the ACCOUNT column.
SELECT
r.role_name,
CASE r.roletype WHEN 5 THEN 'APPLICATION ROLE' WHEN 4 THEN 'ENTERPRISE ROLE' ELSE r.roletype END AS ROLE_TYPE,
CASE r.sox_critical WHEN NULL THEN 'NONE' WHEN 0 THEN 'NONE' WHEN 1 THEN 'VERY LOW' WHEN 2 THEN 'LOW' WHEN 3 THEN 'MEDIUM' WHEN 4 THEN 'HIGH' WHEN 5 THEN 'VERY HIGH' ELSE r.sox_critical END AS SOX_CRITICALITY,
u.username,
u.displayname,
u.email,
CASE u.statuskey WHEN 1 THEN 'Active' WHEN 0 THEN 'Inactive' ELSE u.statuskey END AS user_status
FROM
role_user_account rua
LEFT JOIN roles r ON r.rolekey = rua.rolekey
LEFT JOIN users u ON u.userkey = rua.userkey
WHERE
r.status = 1
AND r.roletype = 4
AND NOT EXISTS (
SELECT
1
FROM
users u2
WHERE
u2.userkey = u.userkey
AND u2.statuskey = 1
);

Thanks

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