Modules

Displays the mismatch in entitlements of role assigned to the user.

Role Id, Endpoint name

Data Analyzer, Analytics

SELECT
u.USERNAME,
a.DISPLAYNAME AS ACCOUNT_NAME,
ep.ENDPOINTNAME AS ENDPOINT_NAME,
r.ROLE_NAME,
GROUP_CONCAT(DISTINCT re.ENTITLEMENT_VALUEKEY ORDER BY re.ENTITLEMENT_VALUEKEY SEPARATOR ', ') AS MISSING_ENTITLEMENT_KEYS
FROM
role_user_account rua
JOIN users u ON rua.USERKEY = u.USERKEY
JOIN accounts a ON rua.ACCOUNTKEY = a.ACCOUNTKEY
JOIN endpoints ep ON a.ENDPOINTKEY = ep.ENDPOINTKEY
JOIN roles r ON rua.ROLEKEY = r.ROLEKEY
JOIN role_entitlements re ON rua.ROLEKEY = re.ROLEKEY
LEFT JOIN account_entitlements1 ae
ON rua.ACCOUNTKEY = ae.ACCOUNTKEY
AND re.ENTITLEMENT_VALUEKEY = ae.ENTITLEMENT_VALUEKEY
WHERE
rua.ROLEKEY = <Role Id>
AND (rua.STARTDATE IS NULL OR rua.STARTDATE <= CURRENT_DATE)
AND (rua.ENDDATE IS NULL OR rua.ENDDATE > CURRENT_DATE)
AND ae.ENTITLEMENT_VALUEKEY IS NULL
AND ep.ENDPOINTNAME = <Endpoint_name>
GROUP BY
u.USERNAME,
a.DISPLAYNAME,
ep.ENDPOINTNAME,
r.ROLE_NAME
ORDER BY
ep.ENDPOINTNAME,
u.USERNAME;
                    

No comments.

Prints the failed tasks associated to a particular Role.

Role Id, Endpoint Name

Data Analyzer, Analytics

SELECT
t.TASKKEY,
t.TASKTYPE,
t.ACCOUNTNAME,
ep.ENDPOINTNAME AS ENDPOINT_NAME,
u.USERNAME,
t.STATUS,
t.TASKDATE,
t.COMMENTS,
t.PROVISIONINGCOMMENTS,
t.PROVISIONINGMETADATA,
t.TRAIL
FROM
arstasks t
LEFT JOIN users u ON t.USERKEY = u.USERKEY
LEFT JOIN accounts a ON t.ACCOUNTKEY = a.ACCOUNTKEY
LEFT JOIN endpoints ep ON a.ENDPOINTKEY = ep.ENDPOINTKEY
WHERE
t.ASSIGNEDFROMROLE = <Role_id>
AND t.STATUS IN (7, 8)
AND ep.ENDPOINTNAME = <Endpoint_name>
ORDER BY
ep.ENDPOINTNAME,
t.TASKDATE DESC;
                    

No comments.

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