To fetch endate from the ARS page

[PrameetPandey]

We have a requirement to create a report that gives below columns

• account name
• ENTITLEMENT NAME
• application name
• Endate mentioned by user on ARS form

The issue here is the revoke access task for firefighter roles / entitlements was not getting created even after the end date. This was happening when arstaskkey was not being fetched by accounts entitlement table that comes from arstasks table. Since arstaskkey is the only unique attribute, we are finding difficulty to add end date from ARS form. As per our understanding it should come from request_access table. We tried joining below tables, but are unable to get the enddate (It won't be there in the account_entitlements1 table because of the issue.)

• account_entitlements1
• accounts
• entitlement_values
• endpoints
• arstasks
• request_access

Could you please help us on how we can add enddate from ARS form ?

[sk]

Try below

select at.accountname, ev.entitlement_value, e.ENDPOINTNAME, ra.enddate from arstasks at inner join entitlement_values ev on at.ENTITLEMENT_VALUEKEY=ev.ENTITLEMENT_VALUEKEY inner join endpoints e on at.endpoint=e.endpointkey inner join request_access ra on at.REQUESTKEY=ra.REQUESTKEY

[rushikeshvartak]

For Emergency Request

SELECT DISTINCT
       SUBSTR(jbpmprocessinstanceid,
           INSTR(jbpmprocessinstanceid, '.') + 1,
           LENGTH(jbpmprocessinstanceid)) AS `REQUEST ID`,
       'Emergency Access Request' AS `REQUEST TYPE`,
       (SELECT 
               USERNAME
           FROM
               users
           WHERE
               userkey = RA.USERKEY) AS `REQUESTED FOR`,
       (SELECT 
               USERNAME
           FROM
               users
           WHERE
               userkey = AR.REQUESTOR) AS `REQUESTED BY`,
       AR.REQUESTDATE AS `REQUEST SUBMITTED DATE (UTC)`,
       (select r.DISPLAYNAME from roles r where r.ROLEKEY=ra.ACCESSKEY) AS `REQUEST EMERGENCY ROLE`,
       RA.STARTDATE AS `START DATE (UTC)`,
       (CASE
           WHEN
               RA.ENDDATE IS NULL
           THEN
               CONCAT(RA.STARTDATE,
                       ' - Independent revoke access request')
           ELSE RA.ENDDATE
       END) AS `END DATE (UTC)`,
       RA.COMMENTS AS `BUSINESS JUSTIFICATION`,
       AR.COMMENTS AS `COMMENT`,
       (SELECT 
               GROUP_CONCAT(DISTINCT USERNAME
                       SEPARATOR ',')
           FROM
               users
           WHERE
               userkey = AA.approverkey) AS `ASSIGNEE`,
       E.DISPLAYNAME AS `ENDPOINTNAME`,
       (CASE
           WHEN ar.status = 1 THEN 'Open'
           WHEN ar.STATUS = 2 THEN 'In Process'
           WHEN ar.STATUS = 3 THEN 'Completed'
           WHEN ar.status = 4 THEN 'Expired'
           ELSE 'Discontinued'
       END) AS `REQUEST STATUS`,
       
       (SELECT 
               CASE
                       WHEN
                           TIMESTAMPDIFF(MINUTE,
                               RA2.STARTDATE,
                               MIN(att2.TASKDATE)) < 0
                       THEN
                           'Violation'
                       WHEN
                           TIMESTAMPDIFF(MINUTE,		
                               RA2.ENDDATE,
                               MIN(att2.TASKDATE)) > 0
                       THEN
                           'Violation'
                       ELSE 'Ok'
                   END
           FROM
               ARSTASKS att2,
               REQUEST_ACCESS RA2
           WHERE
               RA2.REQUESTKEY = ra.REQUESTKEY
                   AND att2.REQUESTKEY = RA2.REQUESTKEY
                   AND att2.TASKTYPE = 1
                   AND att2.status = ATT.status) AS `GRANT ACCESS`,
       (SELECT 
               CASE
                       WHEN
                           TIMESTAMPDIFF(MINUTE,
                               RA2.ENDDATE,
                               MAX(att2.UPDATEDATE)) > 10
                       THEN
                           'Violation'
                       ELSE 'Ok'
                   END
           FROM
               ARSTASKS att2,
               REQUEST_ACCESS RA2
           WHERE
               RA2.REQUESTKEY = ra.REQUESTKEY
                   AND att2.REQUESTKEY = RA2.REQUESTKEY
                   AND att2.TASKTYPE = 2
                   AND att2.status = att.status) AS `REVOKE ACCESS`
   FROM
       ARS_REQUESTS AR,
       REQUEST_ACCESS RA,
       ACCESS_APPROVERS AA,
       ARSTASKS ATT,
       ENDPOINTS E
   WHERE
       AR.REQUESTKEY = RA.REQUESTKEY
           AND RA.REQUEST_ACCESSKEY = AA.REQUEST_ACCESS_KEY
           #AND ra.enddate > (SELECT MAX(aah.updatedate) FROM analytics_analyticshistory aah, analyticsconfig a WHERE a.analyticsname = 'Emergency Access Summary Report' AND a.analyticskey = aah.analyticsconfig)
           AND AR.REQUESTKEY = ATT.REQUESTKEY

           AND ATT.ENDPOINT = E.ENDPOINTKEY
           AND AR.REQUESTTYPE = 11
           
         
   Group By `REQUEST ID`