To get revoke access request which are getting expired in next 7 days

[awareamit20]

Hi There,

I have below query which is giving a result for the revoke access present in the system and are expired since 30 days-

---------------------------

select * from (
select
DISTINCT (SELECT DISTINCT
SUBSTR(ar.jbpmprocessinstanceid,
INSTR(ar.jbpmprocessinstanceid, '.') + 1,
LENGTH(ar.jbpmprocessinstanceid))
) AS 'REQUESTID' , 'Expired' AS 'Request Status', ar.ENDPOINTASCSV AS 'Application Name', u.username AS 'REQUESTED FOR' ,
ar.REQUESTDATE AS 'REQUEST SUBMISSION DATE', ar.duedate AS 'REQUEST EXPIRATION DATE'
from ars_requests ar,
request_access ra, users u
where
ra.REQUESTKEY = ar.REQUESTKEY
AND ar.requesttype =2
AND ra.requesttype =2
and ar.REQUESTKEY not in (select REQUESTKEY from request_access ra1 where ar.REQUESTKEY = ra1.REQUESTKEY and ra1.accesstype in (3) and ra1.requesttype in (1,12))
and ra.accesstype in (2,3)
AND DATE_FORMAT(ar.duedate,'%Y-%m-%d') between
(DATE_FORMAT(curdate() - interval 7 day,'%Y-%m-%d')) AND DATE_FORMAT(curdate(),'%Y-%m-%d')
and ar.status = 4
AND u.userkey = ra.userkey) as a
where
a.REQUESTID is not null

----------------------

Now, we need a query which will give us Revoke access requests which will be expiring in next 7 days.

Can anyone help here.

Thanks,

Amit Awre

[rushikeshvartak]

select * from (
select
DISTINCT (SELECT DISTINCT
SUBSTR(ar.jbpmprocessinstanceid,
INSTR(ar.jbpmprocessinstanceid, '.') + 1,
LENGTH(ar.jbpmprocessinstanceid))
) AS 'REQUESTID' , 'Expired' AS 'Request Status', ar.ENDPOINTASCSV AS 'Application Name', u.username AS 'REQUESTED FOR' ,
ar.REQUESTDATE AS 'REQUEST SUBMISSION DATE', ar.duedate AS 'REQUEST EXPIRATION DATE'
from ars_requests ar,
request_access ra, users u
where
ra.REQUESTKEY = ar.REQUESTKEY
AND ar.requesttype =2
AND ra.requesttype =2
and ar.REQUESTKEY not in (select REQUESTKEY from request_access ra1 where ar.REQUESTKEY = ra1.REQUESTKEY and ra1.accesstype in (3) and ra1.requesttype in (1,12))
and ra.accesstype in (2,3)
AND DATE_FORMAT(ra.enddate,'%Y-%m-%d') between
(DATE_FORMAT(curdate() - interval 7 day,'%Y-%m-%d')) AND DATE_FORMAT(curdate(),'%Y-%m-%d')
and ar.status = 4
AND u.userkey = ra.userkey) as a
where
a.REQUESTID is not null

[awareamit20]

Hi @rushikeshvartak - I have tried but no data found for the same. If you see below we have one Remove account request which will be expiring in next 7 days-

But when I tried running your query getting below-

Could you please help here.

Thanks,

Amit Aware

[This message has been edited by moderator to mask sensitive information]

[rushikeshvartak]

SELECT *
FROM   (SELECT DISTINCT (SELECT DISTINCT Substr(ar.jbpmprocessinstanceid,
       Instr(ar.jbpmprocessinstanceid, '.') +
       1,
       Length(ar.jbpmprocessinstanceid))) AS 'REQUESTID',
       'Expired'                          AS 'Request Status',
       ar.endpointascsv                   AS 'Application Name',
       u.username                         AS 'REQUESTED FOR',
       ar.requestdate                     AS 'REQUEST SUBMISSION DATE',
       ar.duedate                         AS 'REQUEST EXPIRATION DATE'
        FROM   ars_requests ar,
               request_access ra,
               users u
        WHERE  ra.requestkey = ar.requestkey
               AND ar.requesttype = 2
               AND ra.requesttype = 2
               AND ar.requestkey NOT IN (SELECT requestkey
                                         FROM   request_access ra1
                                         WHERE  ar.requestkey = ra1.requestkey
                                                AND ra1.accesstype IN ( 3 )
                                                AND ra1.requesttype IN ( 1, 12 )
                                        )
               AND ra.accesstype IN ( 2, 3 )
               AND Date_format(ar.requestdate, '%Y-%m-%d') BETWEEN (
                   Date_format(
                   Curdate() - INTERVAL 7 day, '%Y-%m-%d') ) AND
                       Date_format(Curdate(), '%Y-%m-%d')
               AND u.userkey = ra.userkey) AS a
WHERE  a.requestid IS NOT NULL 

[awareamit20]

Hi @rushikeshvartak  - I have tried to run latest query and able to fetch below result-

I can see the request which will be expiring in next 7 days. But, with that there are few more requests which are older requests like today's date is 17th May and still there is request of 14th May in result which is not matching the requirement. We need Remove Account request those are expiring in next 7 days.

I can see the partial result. Could you please help me with this it would be great.

Thanks,

Amit Aware

[rushikeshvartak]

SELECT *
FROM (SELECT DISTINCT (SELECT DISTINCT Substr(ar.jbpmprocessinstanceid,
Instr(ar.jbpmprocessinstanceid, '.') +
1,
Length(ar.jbpmprocessinstanceid))) AS 'REQUESTID',
'Expired' AS 'Request Status',
ar.endpointascsv AS 'Application Name',
u.username AS 'REQUESTED FOR',
ar.requestdate AS 'REQUEST SUBMISSION DATE',
ar.duedate AS 'REQUEST EXPIRATION DATE'
FROM ars_requests ar,
request_access ra,
users u
WHERE ra.requestkey = ar.requestkey
AND ar.requesttype = 2
AND ra.requesttype = 2
AND ar.requestkey NOT IN (SELECT requestkey
FROM request_access ra1
WHERE ar.requestkey = ra1.requestkey
AND ra1.accesstype IN ( 3 )
AND ra1.requesttype IN ( 1, 12 )
)
AND ra.accesstype IN ( 2, 3 )
AND ar.requesttype != 12
AND ar.duedate BETWEEN CURDATE() AND CURDATE() + INTERVAL 7 DAY
AND Date_format(ar.requestdate, '%Y-%m-%d') BETWEEN (
Date_format(
Curdate() - INTERVAL 7 day, '%Y-%m-%d') ) AND
Date_format(Curdate(), '%Y-%m-%d')
AND u.userkey = ra.userkey) AS a
WHERE a.requestid IS NOT NULL;

[Nagateja_K]

Hi @awareamit20 

Please refer below query 
select ra.accesstype , ar.JBPMPROCESSINSTANCEID , ar.duedate , ra.userkey from ars_requests ar,
request_access ra where ra.requestkey = ar.requestkey and ra.accesstype in (2,3) and
DATE_FORMAT(ar.duedate, '%Y-%m-%d') BETWEEN
DATE_FORMAT(CURDATE(), '%Y-%m-%d')
AND DATE_FORMAT(CURDATE() + INTERVAL 7 DAY, '%Y-%m-%d') group by ra.userkey

Please change accesstype accordingly.

Regards,
Nagateja ksheerasagar.