Query to extract add access tasks details

[Saviyntreh]

Query to extract an application add access  tasks, user name details, request details, security system ,end point details, approver details.

[rushikeshvartak]

SELECT a.taskkey                          AS 'TaskID',
       a.taskdate                         AS 'TaskDate',
       u.systemusername                   AS 'Username',
       a.accountname                      AS 'AccountName',
       ev.entitlement_value               AS 'EntitlementValue',
       e.endpointname                     AS 'Endpoint',
       a.comments                         AS 'TaskComments',
       a.source                           AS 'Source',
       CASE
         WHEN a.tasktype = '1' THEN 'ADD'
         WHEN a.tasktype = '2' THEN 'REMOVE ACCESS'
         WHEN a.tasktype = '3' THEN 'NEWACCOUNT'
         WHEN a.tasktype = '4' THEN 'ROLE REQUEST'
         WHEN a.tasktype = '5' THEN 'CHANGEPASSWORD '
         WHEN a.tasktype = '6' THEN 'ENABLE ACCOUNT'
         WHEN a.tasktype = '7' THEN 'PROPOSED ACCOUNT OWNERS'
         WHEN a.tasktype = '8' THEN 'DELETE ACCOUNT'
         WHEN a.tasktype = '9' THEN 'UPDATE USER'
         WHEN a.tasktype = '12' THEN 'UPDATE ACCOUNT '
         WHEN a.tasktype = '13' THEN 'PROPOSED Entitlement OWNERS '
         WHEN a.tasktype = '14' THEN 'DISABLE ACCOUNT '
         WHEN a.tasktype = '23' THEN 'MODIFY PRIVILEGE '
         WHEN a.tasktype = '24' THEN 'CREATE ENTITLEMENT'
         WHEN a.tasktype = '27' THEN 'UPDATE ENTITLEMENT'
         WHEN a.tasktype = '28' THEN 'DELETE ENTITLEMENT '
         WHEN a.tasktype = '25' THEN 'UPDATE ENTITLEMENT ACCESS ADD'
         WHEN a.tasktype = '26' THEN 'UPDATE ENTITLEMENT ACCESS REMOVE'
         WHEN a.tasktype = '29' THEN 'FIREFIGHTER ID GRANT ACCESS'
         WHEN a.tasktype = '30' THEN 'FIREFIGHTER ID REVOKE ACCESS'
         WHEN a.tasktype = '31' THEN 'UPDATE ACCESS END DATE'
         WHEN a.tasktype = '32' THEN 'LOCK ACCOUNT'
         WHEN a.tasktype = '33' THEN 'UNLOCK ACCOUNT'
         ELSE a.tasktype
       END                                AS 'TaskType',
       Right(a.provisioningcomments, 200) AS 'LatestError',
       '> 12 Hours'                       AS SLA
FROM   arstasks a,
       users u,
       endpoints e,
       entitlement_values ev
WHERE  a.taskdate <= Date_sub(Now(), interval 12 hour)
       AND a.endpoint = e.endpointkey
       AND u.userkey = a.userkey
       AND a.entitlement_valuekey = ev.entitlement_valuekey
ORDER  BY taskdate ASC 

[Saviyntreh]

Please include the approver details also

[rushikeshvartak]

Select `REQUEST ID` as 'Request ID', `REQUEST TYPE` as 'Request Type', `REQUEST SUBMIT DATE` as 'Request Submission Date', `REQUESTED FOR` as 'Requested For', `REQUESTEE NAME` as 'Request Name', APPLICATION as 'Application', `REQUESTED ENTITLEMENT` as 'Requested Entitlement', SOD_EXCEPTION as 'SOD Exception', MITIGATINGCONTROL as 'Mitigating Control', `RISK TYPE` as 'Risk Type', `REQUESTED BY` as 'Requested By', `APPROVAL TYPE` as 'Approval Type', `ASSIGNEE ID` as 'Assignee ID', `ASSIGNEE NAME` as 'Assignee Name', case when `RequestAccessStatus` = 3 then max(APPROVEDATE) else null end as `Approval Date`, `REQUEST STATUS` as 'Request Status', case when `RequestAccessStatus` = 3 then max(APPROVEDATE) else null end `Request Completion Date`, case when `TASK STATUS` = 'Completed' then (taskupdatedate) else null end `Task Completion Date`, `TASK STATUS` as 'Task Status' from (SELECT DISTINCT SUBSTR(jbpmprocessinstanceid, INSTR(jbpmprocessinstanceid, '.') + 1, LENGTH(jbpmprocessinstanceid)) AS 'REQUEST ID', CASE WHEN (ar.requesttype = 1 or ar.requesttype = 3) THEN 'Grant Access' WHEN ar.requesttype = 2 THEN 'Revoke Access' WHEN ar.requesttype = 12 THEN 'Update Account' WHEN ar.requesttype = 11 THEN 'Emergency Access Request' END AS 'REQUEST TYPE', ar.REQUESTDATE AS 'REQUEST SUBMIT DATE', u2.username AS 'REQUESTED FOR', CONCAT(u2.FIRSTNAME, ' ', u2.LASTNAME) AS 'REQUESTEE NAME', ENDPOINTASCSV AS 'APPLICATION', case when ar.requesttype != 11 then IFNULL((SELECT entitlement_value FROM entitlement_values v WHERE v.entitlement_valuekey = ra.accesskey), 'Account') else (select role_name from roles r where r.ROLEKEY = ra.ACCESSKEY) end AS 'REQUESTED ENTITLEMENT', (select GROUP_CONCAT(distinct EXCEPTIONNAME SEPARATOR ',') from request_exceptions ex where ex.requestkey = ar.requestkey) as SOD_EXCEPTION, (select GROUP_CONCAT(distinct c.MITIGATINGCONTROL SEPARATOR ',') from request_exceptions ex, mitigatingcontrols c where ex.requestkey = ar.requestkey and c.MITIGATINGCONTROLID = ex.MITIGATINGCONTROL) as MITIGATINGCONTROL, (SELECT CASE WHEN v.RISK = 0 THEN 'None' WHEN v.RISK = 1 THEN 'Very Low' WHEN v.RISK = 2 THEN 'Low' WHEN v.RISK = 3 THEN 'Medium' WHEN v.RISK = 4 THEN 'High' WHEN v.RISK = 5 THEN 'Very High' END AS 'RISK TYPE' FROM entitlement_values v WHERE v.entitlement_valuekey = ra.accesskey) AS 'RISK TYPE', (SELECT username FROM users t WHERE t.userkey = ar.requestor) AS 'REQUESTED BY', aa.JBPM_ACTIVITY_NAME AS 'APPROVAL TYPE', u.username AS 'ASSIGNEE ID', CONCAT(u.FIRSTNAME, ' ', u.LASTNAME) AS 'ASSIGNEE NAME' , CASE WHEN aa.status = 1 THEN 'Pending Approval' WHEN aa.STATUS = 2 THEN 'Approved' WHEN aa.STATUS = 3 THEN 'Rejected' WHEN aa.status = 4 THEN 'Escalated' WHEN aa.STATUS = 6 THEN 'Discontinued' END 'REQUEST STATUS', ra.status as RequestAccessStatus, aa.APPROVEDATE, t2.updatedate as taskupdatedate, CASE WHEN t2.status = 1 THEN 'Open' WHEN t2.status = 2 THEN 'InProcess' WHEN t2.status = 3 THEN 'Completed' WHEN t2.status = 4 THEN 'Discontinued' END AS 'TASK STATUS' FROM ARS_REQUESTS ar , request_access ra, ACCESS_APPROVERS aa, USERS u, USERS u2, arstasks t2 WHERE ar.REQUESTKEY = ra.REQUESTKEY AND ra.REQUEST_ACCESSKEY = aa.REQUEST_ACCESS_KEY AND u.userkey = aa.APPROVEBY AND u2.userkey = ra.userkey AND t2.requestaccesskey = ra.request_accesskey and t2.ASSIGNEDFROMRule is null ORDER BY jbpmprocessinstanceid , JBPM_ACTIVITY_NAME , u.username) as t1 group by `REQUEST ID`, `REQUEST TYPE`, `REQUEST SUBMIT DATE`, `REQUESTED FOR`, `REQUESTEE NAME`, `REQUESTED ENTITLEMENT`, SOD_EXCEPTION, MITIGATINGCONTROL, `RISK TYPE`, `REQUESTED BY`, `APPROVAL TYPE`, `ASSIGNEE ID`, `ASSIGNEE NAME`, `TASK STATUS`;

[Saviyntreh]

thanks for your reply, is it possible to include the approver details in first query:

[PremMahadikar · Accepted solution]

Hi @Saviyntreh ,

Approver column added.

SELECT 
	SUBSTR(ar.JBPMPROCESSINSTANCEID,INSTR(ar.JBPMPROCESSINSTANCEID, '.') + 1,LENGTH(ar.JBPMPROCESSINSTANCEID)) AS 'RequestID',
	uappr.username AS 'Approver',
	uappr.email AS 'Approvers Email',
	a.taskkey AS 'TaskID',
    a.taskdate AS 'TaskDate',
    u.systemusername AS 'Username',
    a.accountname AS 'AccountName',
    ev.entitlement_value AS 'EntitlementValue',
    e.endpointname AS 'Endpoint',
    a.comments AS 'TaskComments',
    a.source AS 'Source',
    CASE
        WHEN a.tasktype = '1' THEN 'ADD'
        WHEN a.tasktype = '2' THEN 'REMOVE ACCESS'
        WHEN a.tasktype = '3' THEN 'NEWACCOUNT'
        WHEN a.tasktype = '4' THEN 'ROLE REQUEST'
        WHEN a.tasktype = '5' THEN 'CHANGEPASSWORD '
        WHEN a.tasktype = '6' THEN 'ENABLE ACCOUNT'
        WHEN a.tasktype = '7' THEN 'PROPOSED ACCOUNT OWNERS'
        WHEN a.tasktype = '8' THEN 'DELETE ACCOUNT'
        WHEN a.tasktype = '9' THEN 'UPDATE USER'
        WHEN a.tasktype = '12' THEN 'UPDATE ACCOUNT '
        WHEN a.tasktype = '13' THEN 'PROPOSED Entitlement OWNERS '
        WHEN a.tasktype = '14' THEN 'DISABLE ACCOUNT '
        WHEN a.tasktype = '23' THEN 'MODIFY PRIVILEGE '
        WHEN a.tasktype = '24' THEN 'CREATE ENTITLEMENT'
        WHEN a.tasktype = '27' THEN 'UPDATE ENTITLEMENT'
        WHEN a.tasktype = '28' THEN 'DELETE ENTITLEMENT '
        WHEN a.tasktype = '25' THEN 'UPDATE ENTITLEMENT ACCESS ADD'
        WHEN a.tasktype = '26' THEN 'UPDATE ENTITLEMENT ACCESS REMOVE'
        WHEN a.tasktype = '29' THEN 'FIREFIGHTER ID GRANT ACCESS'
        WHEN a.tasktype = '30' THEN 'FIREFIGHTER ID REVOKE ACCESS'
        WHEN a.tasktype = '31' THEN 'UPDATE ACCESS END DATE'
        WHEN a.tasktype = '32' THEN 'LOCK ACCOUNT'
        WHEN a.tasktype = '33' THEN 'UNLOCK ACCOUNT'
        ELSE a.tasktype
    END AS 'TaskType',
    Right(a.provisioningcomments, 200) AS 'LatestError',
    '> 12 Hours' AS SLA
FROM   
	arstasks a left join users u on u.userkey = a.userkey
    left join endpoints e on a.endpoint = e.endpointkey
    left join entitlement_values ev on a.entitlement_valuekey = ev.entitlement_valuekey
	left join ars_requests ar on ar.REQUESTKEY = a.REQUESTKEY
	left join request_access ra on ar.REQUESTKEY = ra.REQUESTKEY 
	left join access_approvers aa on ra.REQUEST_ACCESSKEY = aa.REQUEST_ACCESS_KEY 
	left join users uappr on aa.APPROVERKEY = uappr.userkey
WHERE  
	a.taskdate >= Date_sub(Now(), interval 12 hour)
    ORDER  BY taskdate ASC 

If this helps your query, please Accept As Solution and hit Kudos

[rushikeshvartak · Accepted solution]

SELECT Substr(ar.jbpmprocessinstanceid, Instr(ar.jbpmprocessinstanceid, '.') + 1
       ,
              Length(ar.jbpmprocessinstanceid)) AS RequestID,
       uappr.username                           AS Approver,
       uappr.email                              AS `Approvers Email`,
       a.taskkey                                AS TaskID,
       a.taskdate                               AS TaskDate,
       u.systemusername                         AS Username,
       a.accountname                            AS AccountName,
       ev.entitlement_value                     AS EntitlementValue,
       e.endpointname                           AS Endpoint,
       a.comments                               AS TaskComments,
       a.source                                 AS Source,
       CASE
         WHEN a.tasktype = '1' THEN 'ADD'
         WHEN a.tasktype = '2' THEN 'REMOVE ACCESS'
         WHEN a.tasktype = '3' THEN 'NEWACCOUNT'
         WHEN a.tasktype = '4' THEN 'ROLE REQUEST'
         WHEN a.tasktype = '5' THEN 'CHANGEPASSWORD '
         WHEN a.tasktype = '6' THEN 'ENABLE ACCOUNT'
         WHEN a.tasktype = '7' THEN 'PROPOSED ACCOUNT OWNERS'
         WHEN a.tasktype = '8' THEN 'DELETE ACCOUNT'
         WHEN a.tasktype = '9' THEN 'UPDATE USER'
         WHEN a.tasktype = '12' THEN 'UPDATE ACCOUNT '
         WHEN a.tasktype = '13' THEN 'PROPOSED Entitlement OWNERS '
         WHEN a.tasktype = '14' THEN 'DISABLE ACCOUNT '
         WHEN a.tasktype = '23' THEN 'MODIFY PRIVILEGE '
         WHEN a.tasktype = '24' THEN 'CREATE ENTITLEMENT'
         WHEN a.tasktype = '27' THEN 'UPDATE ENTITLEMENT'
         WHEN a.tasktype = '28' THEN 'DELETE ENTITLEMENT '
         WHEN a.tasktype = '25' THEN 'UPDATE ENTITLEMENT ACCESS ADD'
         WHEN a.tasktype = '26' THEN 'UPDATE ENTITLEMENT ACCESS REMOVE'
         WHEN a.tasktype = '29' THEN 'FIREFIGHTER ID GRANT ACCESS'
         WHEN a.tasktype = '30' THEN 'FIREFIGHTER ID REVOKE ACCESS'
         WHEN a.tasktype = '31' THEN 'UPDATE ACCESS END DATE'
         WHEN a.tasktype = '32' THEN 'LOCK ACCOUNT'
         WHEN a.tasktype = '33' THEN 'UNLOCK ACCOUNT'
         ELSE a.tasktype
       end                                      AS TaskType,
       RIGHT(a.provisioningcomments, 200)       AS LatestError,
       '> 12 Hours'                             AS SLA
FROM   arstasks a
       LEFT JOIN users u
              ON u.userkey = a.userkey
       LEFT JOIN endpoints e
              ON a.endpoint = e.endpointkey
       LEFT JOIN entitlement_values ev
              ON a.entitlement_valuekey = ev.entitlement_valuekey
       LEFT JOIN ars_requests ar
              ON ar.requestkey = a.requestkey
       LEFT JOIN request_access ra
              ON ar.requestkey = ra.requestkey
       LEFT JOIN access_approvers aa
              ON ra.request_accesskey = aa.request_access_key
       LEFT JOIN users uappr
              ON aa.approverkey = uappr.userkey
WHERE  a.taskdate >= Date_sub(Now(), INTERVAL 12 hour)
ORDER  BY a.taskdate;