Modules

Displays requestable roles.

nan

Data Analyzer, Analytics

SELECT role_name,displayname,requestable FROM roles WHERE requestable=1;
                    

nan

Displays roles grouped by role type.

nan

Data Analyzer, Analytics

SELECT CASE roletype WHEN 0 THEN 'None' WHEN 1 THEN 'Enabler' WHEN 2 THEN 'Transactional' WHEN 3 THEN 'Firefighter' WHEN 4 THEN 'Enterprise' WHEN 5 THEN 'Application' WHEN 6 THEN 'Entitlement Based' ELSE CONCAT('Unknown (',roletype,')') END AS Role_Type, COUNT(*) AS Total_Roles FROM roles GROUP BY roletype ORDER BY Total_Roles DESC;
                    

nan

Displays roles grouped by status.

nan

Data Analyzer, Analytics

SELECT CASE status WHEN 0 THEN 'Inactive' WHEN 1 THEN 'Active' WHEN 2 THEN 'Composing' WHEN 3 THEN 'Sent For Approval' WHEN 4 THEN 'Decommission' WHEN 5 THEN 'Mining' ELSE CONCAT('Unknown (',status,')') END AS Role_Status, COUNT(*) AS Total_Roles FROM roles GROUP BY status ORDER BY Total_Roles DESC;
                    

nan

Displays user, account and role assignment details.

nan

Data Analyzer, Analytics

SELECT rolekey,userkey,accountkey,startdate,enddate
FROM role_user_account;
                    

nan

Displays recently updated role assignments.

nan

Data Analyzer, Analytics

SELECT rolekey,updatedate,updateuser
FROM role_user_account
ORDER BY updatedate DESC;
                    

nan

Displays total users assigned to each role.

nan

Data Analyzer, Analytics

SELECT rolekey,
COUNT(*) AS Assigned_Users
FROM role_user_account
GROUP BY rolekey
ORDER BY Assigned_Users DESC;
                    

nan

Displays roles that are not assigned to any user.

nan

Data Analyzer, Analytics

SELECT r.rolekey,r.role_name
FROM roles r
LEFT JOIN role_user_account rua ON r.rolekey=rua.rolekey
WHERE rua.userkey IS NULL;
                    

nan

Displays roles that do not have any entitlement mapping.

nan

Data Analyzer, Analytics

SELECT r.rolekey,r.role_name
FROM roles r
LEFT JOIN role_entitlements re ON r.rolekey=re.rolekey
WHERE re.entitlement_valuekey IS NULL;
                    

nan

Get the necessary details of all Roles

nan

Data Analyzer, Analytics

SELECT
r.rolekey,
r.role_name,
r.description,
r.displayname,
ep.endpointname,
re.entitlement_valuekey,
ev.entitlement_value,
et.entitlementname AS entitlement_type_name,
pr.role_name AS parent_role_name,
r.requestable,
CASE
WHEN r.roletype = 0 THEN 'None'
WHEN r.roletype = 1 THEN 'Enabler Role'
WHEN r.roletype = 2 THEN 'Transactional Role'
WHEN r.roletype = 3 THEN 'Emergency Access Role'
WHEN r.roletype = 4 THEN 'Enterprise Role'
WHEN r.roletype = 5 THEN 'Application Role'
WHEN r.roletype = 6 THEN 'Entitlement Role'
ELSE 'Unknown'
END AS Role_Type,
CASE
WHEN r.status = 1 THEN 'Active'
WHEN r.status = 0 THEN 'Inactive'
ELSE 'Composing'
END AS Role_Status,
u.username AS update_user_name,
r.updatedate
FROM roles r
LEFT JOIN endpoints ep
ON r.endpointkey = ep.endpointkey
LEFT JOIN role_entitlements re
ON r.rolekey = re.rolekey
LEFT JOIN entitlement_values ev
ON re.entitlement_valuekey = ev.entitlement_valuekey
LEFT JOIN entitlement_types et
ON ev.entitlementtypekey = et.entitlementtypekey
LEFT JOIN roles pr
ON r.parentroles = pr.rolekey
LEFT JOIN users u
ON r.updateuser = u.userkey
ORDER BY r.rolekey;
                    

nan