Request and Task Status

[Manu269]

Hello Team,

We need assistance in building a query with below use case :

1. We are provisioning the user account and access in application A.

2. Since Account task is auto approved and access goes for an approval. Hence, in this case we end up with creating account and it may happen that Access request is either expired, discontinued or rejected.

Assistance Required :

Can someone help us with query to identify such scenario where we can pull up all such accounts where New Account task is created and completed successfully and child request (Addition of entitlement or removal of entitlement) is pending or rejected or expired or discontinued?

[sudeshjaiswal]

Hello @Manu269,

You may use below query for your use case,
SELECT DISTINCT r.requestkey                                               AS
                'Requestkey',
                r.endpointascsv,
                u.username,
                (SELECT DISTINCT Substr(jbpmprocessinstanceid, Instr(
                                 jbpmprocessinstanceid, '.')
                                                               + 1, Length(
                                                  jbpmprocessinstanceid))) AS
                'Request ID',
                CASE
                  WHEN ( r.status = 1 ) THEN 'Open'
                END                                                        AS
                'Request Status'
FROM   ars_requests r,
       request_access ra,
       entitlement_values v,
       users u
WHERE  ra.requestkey = r.requestkey
       AND u.userkey = ra.userkey
       AND r.requesttype = 3
       AND r.status = 1
       AND v.entitlement_valuekey = ra.accesskey
       AND NOT EXISTS(SELECT *
                      FROM   arstasks t
                      WHERE  t.requestkey = r.requestkey
                             AND t.entitlement_valuekey = v.entitlement_valuekey
                             AND t.entitlement_valuekey IS NOT NULL)
       AND EXISTS(SELECT *
                  FROM   arstasks at
                  WHERE  at.requestkey = r.requestkey
                         AND at.tasktype = 3
                         AND at.status = 3)
GROUP  BY requestkey,
          'REQUEST ID'; 

By executing the provided query, you will receive a list of request IDs and usernames. This list highlights situations where an account request has been approved and the associated task has been completed. However, the approval for entitlement is still pending.

In essence, the query helps identify cases where a new account request has undergone the necessary account task processes, resulting in completion, but the corresponding add access task has yet to be generated. If you require the inclusion of account IDs in the output, additional joins can be performed to accomplish that.

Thanks

[Manu269]

Hi Sudeesh,

Thanks for the information.

Let me validate the same.

Anyhow, can you also let me know how can we also include a case where the requested access has either expired or discontinued or rejected.

Reason : This will help me to identify all the accounts which were created in target without access.

Please assist.

[sudeshjaiswal]

Hello @Manu269,

Please find the below query for your use case,
SELECT DISTINCT r.requestkey                                               AS
                'Requestkey',
                r.endpointascsv,
                u.username,
                (SELECT DISTINCT Substr(jbpmprocessinstanceid, Instr(
                                 jbpmprocessinstanceid, '.'
                                                               ) + 1, Length(
                                                  jbpmprocessinstanceid))) AS
                'Request ID',
                CASE
                  WHEN AA.status = 1 THEN 'Pending Approval'
                  WHEN AA.status = 2 THEN 'Approved'
                  WHEN AA.status = 3 THEN 'Rejected'
                  WHEN AA.status = 4 THEN 'Escalated'
                  WHEN AA.status = 6 THEN 'Discontinued'
                  ELSE AA.status
                END                                                        AS
                'REQUEST STATUS'
FROM   ars_requests r,
       request_access ra,
       access_approvers aa,
       entitlement_values v,
       users u
WHERE  ra.requestkey = r.requestkey
       AND aa.request_access_key = ra.request_accesskey
       AND u.userkey = ra.userkey
       AND r.requesttype = 3
       AND r.status IN ( 1, 3 )
       AND v.entitlement_valuekey = ra.accesskey
       AND NOT EXISTS(SELECT *
                      FROM   arstasks t
                      WHERE  t.requestkey = r.requestkey
                             AND t.entitlement_valuekey = v.entitlement_valuekey
                             AND t.entitlement_valuekey IS NOT NULL)
       AND EXISTS(SELECT *
                  FROM   arstasks at
                  WHERE  at.requestkey = r.requestkey
                         AND at.tasktype = 3
                         AND at.status = 3)
GROUP  BY requestkey,
          'REQUEST ID'; 

Thanks