and more in a single search tool across platforms. Read the announcement here. |
05/26/2023 01:15 AM
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
Solved! Go to Solution.
05/26/2023 02:17 AM
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
05/26/2023 06:47 AM
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,
05/26/2023 03:29 AM
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,
05/26/2023 04:59 AM
@sudeshjaiswal - Thanks for the queries.
Sample1: getting below error for this query
Sample2: getting below as result for this
Thanks,
Amit Aware
05/26/2023 05:45 AM - edited 05/26/2023 05:45 AM
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,
Please try the sample 2 query in analytics..
Thanks,
05/26/2023 06:05 AM
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'
05/28/2023 09:45 PM
@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
05/27/2023 09:03 AM
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
05/28/2023 10:00 PM
@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
05/29/2023 12:09 AM
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
05/29/2023 01:09 AM
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
);
05/29/2023 03:16 AM
05/29/2023 03:33 PM
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);
05/29/2023 03:59 AM - edited 05/29/2023 03:59 AM
Hi @SriRanga,
Please let me know if you are getting the same error for both the query mentioned above.
05/29/2023 04:53 AM
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
06/05/2023 04:28 AM
Thanks for the update @SriRanga
06/08/2023 09:58 PM
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