Modules

Displays recently submitted ARS requests.

nan

Data Analyzer, Analytics

SELECT requestor, requestdate, requesttype, endpointascsv
FROM ars_requests
ORDER BY requestdate DESC;
                    

nan

Displays ARS requests grouped by request type.

nan

Data Analyzer, Analytics

SELECT
CASE requesttype
WHEN 1 THEN 'Add'
WHEN 2 THEN 'Delete'
WHEN 3 THEN 'New Account'
WHEN 4 THEN 'New Role Request'
WHEN 5 THEN 'Create Role'
WHEN 6 THEN 'Modify Role'
WHEN 11 THEN 'New Firefighter Role Request'
WHEN 12 THEN 'Update Account'
WHEN 15 THEN 'Proposed Account Owners'
WHEN 16 THEN 'Modify Organization'
WHEN 17 THEN 'Proposed Entitlement Owners'
WHEN 18 THEN 'Create User'
WHEN 19 THEN 'Update User'
WHEN 20 THEN 'Create Rule'
WHEN 21 THEN 'Update Rule'
WHEN 22 THEN 'Disable Rule'
WHEN 23 THEN 'Firefighter ID'
WHEN 24 THEN 'Firefighter ID Access'
WHEN 25 THEN 'T2P'
WHEN 26 THEN 'Enable Rule'
WHEN 28 THEN 'Extend Access'
WHEN 29 THEN 'Enable Account'
WHEN 30 THEN 'Disable Account'
WHEN 31 THEN 'Lock Account'
WHEN 32 THEN 'Unlock Account'
ELSE CONCAT('Unknown (',requesttype,')')
END AS Request_Type,
COUNT(*) AS Total_Requests
FROM ars_requests
GROUP BY requesttype
ORDER BY Total_Requests DESC;
                    

nan

Analytics- All the request of Endpoint in 30 days

Endpoint Name

Data Analyzer, Analytics

SELECT
SUBSTR(ar.jbpmprocessinstanceid, INSTR(ar.jbpmprocessinstanceid, '.') + 1) AS 'REQUEST ID',
CASE
WHEN AR.REQUESTTYPE = 1 THEN 'Add Access'
WHEN AR.REQUESTTYPE = 2 THEN 'Remove Access'
WHEN AR.REQUESTTYPE = 3 THEN 'New Account'
WHEN AR.REQUESTTYPE = 4 THEN 'Role Request'
WHEN AR.REQUESTTYPE = 5 THEN 'Create Role Request'
WHEN AR.REQUESTTYPE = 6 THEN 'Modify Role Request'
WHEN AR.REQUESTTYPE = 11 THEN 'Firefighter Role Request'
WHEN AR.REQUESTTYPE = 12 THEN 'Update Account'
WHEN AR.REQUESTTYPE = 18 THEN 'Create User'
WHEN AR.REQUESTTYPE = 19 THEN 'Update User'
WHEN AR.REQUESTTYPE = 25 THEN 'Transport'
WHEN AR.REQUESTTYPE = 28 THEN 'Extend Access'
ELSE 'Others'
END AS 'REQUEST TYPE',
CASE
WHEN ra.ACCESSTYPE = 1 THEN 'Role'
WHEN ra.ACCESSTYPE = 2 THEN 'Entitlement'
WHEN ra.ACCESSTYPE = 3 THEN 'Account'
WHEN ra.ACCESSTYPE = 4 THEN 'Organisation'
WHEN ra.ACCESSTYPE = 5 THEN 'User'
WHEN ra.ACCESSTYPE = 6 THEN 'Rule'
WHEN ra.ACCESSTYPE = 7 THEN 'Role Based Entitlement'
WHEN ra.ACCESSTYPE = 8 THEN 'Emergency ID'
WHEN ra.ACCESSTYPE = 9 THEN 'Emergency Access'
WHEN ra.ACCESSTYPE = 10 THEN 'Transport'
ELSE 'Others'
END AS 'ACCESS TYPE',
ar.REQUESTDATE AS 'REQUEST SUBMIT DATE',
u2.username AS 'REQUESTED FOR',
CONCAT(u2.FIRSTNAME, ' ', u2.LASTNAME) AS 'REQUESTEE NAME',
ar.ENDPOINTASCSV AS 'APPLICATION',
CASE
WHEN ra.ACCESSTYPE = 1
THEN (SELECT r.ROLE_NAME
FROM ROLES r
WHERE r.ROLEKEY = ra.ACCESSKEY)
WHEN ra.ACCESSTYPE = 2
THEN (SELECT v.ENTITLEMENT_VALUE
FROM ENTITLEMENT_VALUES v
WHERE v.ENTITLEMENT_VALUEKEY = ra.ACCESSKEY)
WHEN ra.ACCESSTYPE = 3
THEN 'Account'
END AS 'REQUESTED ENTITLEMENT',
reqUser.username AS 'REQUESTED BY',
aa.JBPM_ACTIVITY_NAME AS 'APPROVAL TYPE',
u.username AS 'APPROVED BY',
CASE
WHEN aa.status = 1 THEN 'Pending'
WHEN aa.status = 2 THEN 'Approved'
WHEN aa.status = 3 THEN 'Rejected'
WHEN aa.status = 4 THEN 'Escaled'
WHEN aa.status = 5 THEN 'Expired'
WHEN aa.status = 6 THEN 'Discontinued'
WHEN aa.status = 7 THEN 'Re-assigned'
ELSE CONCAT('Unknown (', aa.status, ')')
END AS 'REQUEST STATUS',
TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX( SUBSTRING(ar.comments, LOCATE(']', ar.comments)+ 1), '<', 1 ), '>', 1)) AS 'REQUEST COMMENTS',
TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX( SUBSTRING(ra.comments, LOCATE(']', ra.comments) + 1), '<', 1 ), '>', 1)) AS 'BUSINESS JUSTIFICATION',
ar.DUEDATE AS 'END DATE'
FROM ARS_REQUESTS ar
INNER JOIN REQUEST_ACCESS ra
ON ar.REQUESTKEY = ra.REQUESTKEY
LEFT JOIN ACCESS_APPROVERS aa
ON aa.REQUEST_ACCESS_KEY = ra.REQUEST_ACCESSKEY
AND aa.ACCESS_APPROVERSKEY = (
SELECT MAX(a2.ACCESS_APPROVERSKEY)
FROM ACCESS_APPROVERS a2
WHERE a2.REQUEST_ACCESS_KEY = ra.REQUEST_ACCESSKEY)
LEFT JOIN USERS u
ON u.USERKEY = aa.APPROVEBY
INNER JOIN USERS u2
ON u2.USERKEY = ra.USERKEY
LEFT JOIN USERS reqUser
ON reqUser.USERKEY = ar.REQUESTOR
WHERE aa.ACCESS_APPROVERSKEY IS NOT NULL
and ar.endpointascsv='Active Directory_Pre-created'
AND ar.requestdate >= CURRENT_DATE - INTERVAL 30 DAY
ORDER BY ar.REQUESTDATE DESC;
                    

nan