and more in a single search tool across platforms. Read the announcement here. |
09/01/2022 08:54 AM
Hi Team,
Is there any way to capture previous month requests like for last month of 1st date to last date request.
report will be running on 1st of each month ,Is there way to capture such requests with below data
Endpoint
Entitlement Value
username
approver name
request date
approval date/rejection date
action(add/revoke)
Thanks and Regards,
Gagan Juneja
Solved! Go to Solution.
09/01/2022 09:35 AM
SELECT ENDPOINTASCSV AS 'APPLICATION',
SUBSTR(jbpmprocessinstanceid, INSTR(jbpmprocessinstanceid, '.') + 1, LENGTH(jbpmprocessinstanceid)) AS 'REQUEST ID',
CASE
WHEN ra.accesstype = 3
AND ra.accesskey = 0 THEN 'Account'
WHEN ra.accesstype = 2 THEN
(SELECT entitlement_value
FROM entitlement_values v
WHERE v.entitlement_valuekey = ra.accesskey
AND ra.ACCESSTYPE != 3)
WHEN ra.accesstype in(7,
1) THEN
(SELECT role_name
FROM ROLES r
WHERE r.ROLEKEY = ra.ACCESSKEY)
END AS 'REQUESTED ENTITLEMENT',
u.username AS 'REQUESTED FOR',
app.username AS 'Approver Name',
ar.REQUESTDATE AS 'REQUEST DATE',
aa.APPROVEDATE,
CASE
WHEN (ar.requesttype = 1
OR ar.requesttype = 3) THEN 'Grant Access'
WHEN ar.requesttype = 2 THEN 'Revoke Access'
END AS 'REQUEST TYPE'
FROM ars_requests ar,
request_Access ra,
users u,
USERS app,
ACCESS_APPROVERS aa
WHERE ra.requestkey=ar.requestkey
AND u.userkey = ra.userkey
AND aa.approveby = u.userkey
AND ar.requesttype in (1,
2)
AND ar.REQUESTDATE > curdate() - INTERVAL 6 MONTH
AND aa.APPROVEDATE > curdate()
09/06/2022 05:05 AM
Thanks Rushikesh for the solution but It is not working as per my requirement, I need the data only for one particular security system having 4 endpoints, I tried to add one filter for the endpointname in your mentioned query but data is not matching with request history, I am attaching my query:
SELECT endpoints.endpointname AS 'Endpoint',
SUBSTR(jbpmprocessinstanceid, INSTR(jbpmprocessinstanceid, '.') + 1, LENGTH(jbpmprocessinstanceid)) AS 'REQUEST ID',
CASE
WHEN ra.accesstype = 3
AND ra.accesskey = 0 THEN 'Account'
WHEN ra.accesstype = 2 THEN
(SELECT entitlement_value
FROM entitlement_values v
WHERE v.entitlement_valuekey = ra.accesskey
AND ra.ACCESSTYPE != 3)
WHEN ra.accesstype in(7,
1) THEN
(SELECT role_name
FROM ROLES r
WHERE r.ROLEKEY = ra.ACCESSKEY)
END AS 'REQUESTED ENTITLEMENT',
u.username AS 'REQUESTED FOR',
app.username AS 'Approver Name',
ar.REQUESTDATE AS 'REQUEST DATE',
aa.APPROVEDATE,
CASE
WHEN (ar.requesttype = 1
OR ar.requesttype = 3) THEN 'Grant Access'
WHEN ar.requesttype = 2 THEN 'Revoke Access'
END AS 'REQUEST TYPE'
FROM ars_requests ar,
request_Access ra,
users u,
USERS app,
ACCESS_APPROVERS aa,endpoints
WHERE ra.requestkey=ar.requestkey
AND u.userkey = ra.userkey
AND aa.approveby = u.userkey
AND ar.requesttype in (1,
2) AND endpoints.endpointname in('Endpoint1',' Endpoint2','Endpoint3','Endpoint4')
AND ar.REQUESTDATE > curdate() - INTERVAL 1 MONTH
AND aa.APPROVEDATE > curdate() LIMIT 100
I need only the required data not complete data, specific to 4 endpoints under common security system.
Thanks and Regards,
gagan
09/06/2022 06:34 AM - edited 09/06/2022 08:08 AM
SELECT ENDPOINTASCSV AS 'APPLICATION',
SUBSTR(jbpmprocessinstanceid, INSTR(jbpmprocessinstanceid, '.') + 1, LENGTH(jbpmprocessinstanceid)) AS 'REQUEST ID',
CASE
WHEN ra.accesstype = 3
AND ra.accesskey = 0 THEN 'Account'
WHEN ra.accesstype = 2 THEN
(SELECT entitlement_value
FROM entitlement_values v
WHERE v.entitlement_valuekey = ra.accesskey
AND ra.ACCESSTYPE != 3)
WHEN ra.accesstype in(7,
1) THEN
(SELECT role_name
FROM ROLES r
WHERE r.ROLEKEY = ra.ACCESSKEY)
END AS 'REQUESTED ENTITLEMENT',
u.username AS 'REQUESTED FOR',
app.username AS 'Approver Name',
ar.REQUESTDATE AS 'REQUEST DATE',
aa.APPROVEDATE,
CASE
WHEN (ar.requesttype = 1
OR ar.requesttype = 3) THEN 'Grant Access'
WHEN ar.requesttype = 2 THEN 'Revoke Access'
END AS 'REQUEST TYPE'
FROM ars_requests ar,
request_Access ra,
users u,
USERS app,
ACCESS_APPROVERS aa
WHERE ra.requestkey=ar.requestkey
AND u.userkey = ra.userkey
AND aa.approveby = app.userkey
AND ar.requesttype in (1,
2)
AND ar.REQUESTDATE > curdate() - INTERVAL 6 MONTH
AND aa.APPROVEDATE > curdate()
AND ra.REQUEST_ACCESSKEY = aa.REQUEST_ACCESS_KEY
AND ar.ENDPOINTASCSV in ('E1','E2')
09/06/2022 06:44 AM
@rushikeshvartak : Thanks again for quick reply but still this query is showing me query limit error, I tried to add LIMIT also but still it is not allowing to run the query. In analytical module it is not allowing me to save it. Could you please help me out.
09/06/2022 06:48 AM - edited 09/06/2022 07:46 AM
Its working as expected.
09/08/2022 04:05 AM
@gagan94
It would help if you share a screenshot of the error you are getting.