Modules

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