Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.
No ratings
Saviynt Employee
Saviynt Employee

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:




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.



Connection, Endpoint, Entitlement, Role

Applicable Version(s)



Analytical Control:

SELECT DISTINCT Substr(jbpmprocessinstanceid, Instr(jbpmprocessinstanceid, '.')
                                              + 1, Length(
                AS 'Request_Access Status',
                  WHEN ar.requesttype = 4 THEN 'Enterprise Role Request'
                  ELSE ar.endpointascsv
                AS 'APPLICATION',
                  WHEN ra.requesttype = 1 THEN 'Grant Access'
                  WHEN ra.requesttype = 2 THEN 'Revoke Access'
                  WHEN ra.requesttype = 3 THEN 'Role Modify'
                '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
                        ra2.requestkey = ra.requestkey)) AS
                'Last Approval Date',
                  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'
                '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 )
                       FROM   access_approvers aa1
                       WHERE  aa1.request_access_key IN(SELECT request_accesskey
                              request_access ra1
                              ra1.requestkey = ra.requestkey)
                              AND status = 1)
       AND ra.status = 1
SELECT Substr(jbpmprocessinstanceid, Instr(jbpmprocessinstanceid, '.') + 1,
       AS 'Request_Access Status',
         WHEN ar1.requesttype = 4 THEN 'Enterprise Role Request'
         ELSE ar1.endpointascsv
         WHEN ra1.requesttype = 1 THEN 'Grant Access'
         WHEN ra1.requesttype = 2 THEN 'Revoke Access'
         WHEN ra1.requesttype = 3 THEN 'Role Modify'
       'REQUEST TYPE',
       Date_format(ar1.requestdate, '%m-%d-%Y %T')
       (SELECT Max(aa11.approvedate)
        FROM   access_approvers aa11
        WHERE  aa11.request_access_key IN (SELECT request_accesskey
                                           FROM   request_access ra22
               ra22.requestkey = ra1.requestkey)) AS
       'Last Approval Date',
         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'
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.



Version history
Last update:
‎06/14/2023 09:27 PM
Updated by: