How To Identify Pending Requests with Inactive User and close them automatically

[MarcoApollo]

Hello Team,

We would like to Identify Pending Requests with Inactive User and close them automatically for a person who left the company. 

Here below see the article that we found related to " How To Identify Pending Requests with Inactive approver ".

How To Identify Pending Requests with Inactive App... - Saviynt Forums - 50756

Could be possible perform this action for Inactive User by using Analytics?

Many thanks

Marco

[rushikeshvartak]

• You need inactive user and end user or requestor or approver ?

[MarcoApollo]

Inactive User (Requested for) from access request

[rushikeshvartak · Accepted solution]

SELECT DISTINCT Substr(jbpmprocessinstanceid, Instr(jbpmprocessinstanceid, '.')
                                              + 1, Length(
                                jbpmprocessinstanceid)) REQUESTID,
                'Pending'                               AS 'REQUEST STATUS',
                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',
                ar.endpointascsv                        AS
                'APPLICATION INSTANCE',
                r.username                              AS REQUESTEE,
                Group_concat(DISTINCT ( u.username ))   'PENDING WITH USER NAME'
                ,
                aa.jbpm_activity_name                   AS
                'APPROVAL STAGE',
                ar.requestdate                          'REQUEST DATE', (
Datediff(Date_format(Ifnull(ar.duedate, Curdate()), '%Y-%m-%d'),
Date_format(ar.requestdate, '%Y-%m-%d'))) 'DAYS TO EXPIRE'
FROM   ars_requests ar,
       request_access ra,
       access_approvers aa,
       users u,
       users r
WHERE

ar.requestkey = ra.requestkey AND ra.request_accesskey = aa.request_access_key AND u.userkey = aa.approverkey AND aa.status = 1 AND ar.status <> 4 AND r.statuskey = 0 AND r.userkey = ar.requestor GROUP BY requestid ORDER  BY requestid ASC 

[MarcoApollo]

Thanks a lot, it worked.

Kind Regards,

Marco