Click HERE to see how Saviynt Intelligence is transforming the industry. |
05/15/2024 05:11 AM
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
05/15/2024 05:44 AM
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
05/16/2024 02:23 AM - last edited on 05/16/2024 04:19 AM by Sunil
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]
05/16/2024 07:58 PM - edited 05/16/2024 07:58 PM
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
05/16/2024 11:46 PM
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
05/17/2024 07:04 AM
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;
05/17/2024 12:29 AM
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.