Report for getting Previous Month approved rejected request

[gagan94]

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

[rushikeshvartak]

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()

[gagan94]

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

[rushikeshvartak · Accepted solution]

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')

[gagan94]

@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.

[rushikeshvartak]

Its working as expected.

[sahajranajee]

@gagan94 

It would help if you share a screenshot of the error you are getting.