Use Case
Analytical Control to capture the Requests which are approved completely but are still in an open state due to which tasks not created
When the request is approved completely the status in three tables will be:
request_access.status=2
ars_requests.status=3
access_approvers.status=2
This control helps to find out those requests which are approved completely but due to deadlock or specific error the status is not updated in the DB in the request_Access/ars_requests table and hence it stays in the open (status=1 for these two tables) because of which task will not be created.
How to Fix these requests after validation so that a task can be created for approved requests?
1. Get request_key from the below control.
2. Accesskey in request_Access is the entitlement_valuekey/ Rolekey
3. If the entitlement/ role is approved, update the status=2 & If the entitlement/role is rejected, update the status=4
4. Go to Job Control Panel --> Utility --> Enterprise Role Management Job & Run Enterprise role management job to create tasks.
Pre-requisites
Connection, Endpoint, Entitlement, Role
Applicable Version(s)
All
Solution
Analytical Control:
SELECT DISTINCT Substr(jbpmprocessinstanceid, Instr(jbpmprocessinstanceid, '.')
+ 1, Length(
jbpmprocessinstanceid))
'REQUESTID',
ar.requestkey,
ra.status
AS 'Request_Access Status',
CASE
WHEN ar.requesttype = 4 THEN 'Enterprise Role Request'
ELSE ar.endpointascsv
END
AS 'APPLICATION',
CASE
WHEN ra.requesttype = 1 THEN 'Grant Access'
WHEN ra.requesttype = 2 THEN 'Revoke Access'
WHEN ra.requesttype = 3 THEN 'Role Modify'
END
'REQUEST TYPE',
Date_format(ar.requestdate, '%m-%d-%Y %T')
'REQUEST SUBMISSION DATE',
(SELECT Max(aa1.approvedate)
FROM access_approvers aa1
WHERE aa1.request_access_key IN (SELECT request_accesskey
FROM request_access ra2
WHERE
ra2.requestkey = ra.requestkey)) AS
'Last Approval Date',
CASE
WHEN ar.status = 1 THEN 'New'
WHEN ar.status = 2 THEN 'INPROCESS'
WHEN ar.status = 3 THEN 'COMPLETED'
WHEN ar.status = 4 THEN 'EXPIRES'
WHEN ar.status = 6 THEN 'DISCONTINUE'
END
'REQUEST STATUS'
FROM ars_requests ar,
request_access ra,
access_approvers aa,
users u,
users u2
WHERE ar.requestkey = ra.requestkey
AND ra.request_accesskey = aa.request_access_key
AND u.userkey = aa.approverkey
AND u2.userkey = ra.userkey
AND ar.status < 3
AND ( ar.status = 1
OR ar.status = 2 )
AND NOT EXISTS (SELECT *
FROM access_approvers aa1
WHERE aa1.request_access_key IN(SELECT request_accesskey
FROM
request_access ra1
WHERE
ra1.requestkey = ra.requestkey)
AND status = 1)
AND ra.status = 1
UNION
SELECT Substr(jbpmprocessinstanceid, Instr(jbpmprocessinstanceid, '.') + 1,
Length(
jbpmprocessinstanceid))
'REQUESTID',
ar1.requestkey,
ra1.status
AS 'Request_Access Status',
CASE
WHEN ar1.requesttype = 4 THEN 'Enterprise Role Request'
ELSE ar1.endpointascsv
END
AS 'APPLICATION',
CASE
WHEN ra1.requesttype = 1 THEN 'Grant Access'
WHEN ra1.requesttype = 2 THEN 'Revoke Access'
WHEN ra1.requesttype = 3 THEN 'Role Modify'
END
'REQUEST TYPE',
Date_format(ar1.requestdate, '%m-%d-%Y %T')
'REQUEST SUBMISSION DATE',
(SELECT Max(aa11.approvedate)
FROM access_approvers aa11
WHERE aa11.request_access_key IN (SELECT request_accesskey
FROM request_access ra22
WHERE
ra22.requestkey = ra1.requestkey)) AS
'Last Approval Date',
CASE
WHEN ar1.status = 1 THEN 'New'
WHEN ar1.status = 2 THEN 'INPROCESS'
WHEN ar1.status = 3 THEN 'COMPLETED'
WHEN ar1.status = 4 THEN 'EXPIRES'
WHEN ar1.status = 6 THEN 'DISCONTINUE'
END
'REQUEST STATUS'
FROM request_access ra1,
ars_requests ar1
WHERE ra1.requestkey = ar1.requestkey
AND ra1.status = 1
AND ar1.status = 3
AND ar1.requestdate > '2019-01-01'
GROUP BY requested
NOTE: Change the Request date at the bottom as per your requirement.
References
https://docs.saviyntcloud.com/bundle/EIC-Admin-v23x/page/Content/Chapter17-EIC-Analytics/Managing-An...
