Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.

Query to extract add access tasks details

Saviyntreh
New Contributor III
New Contributor III

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

6 REPLIES 6

rushikeshvartak
All-Star
All-Star

rushikeshvartak_0-1716225837286.png

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 

 


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

Saviyntreh
New Contributor III
New Contributor III

Please include the approver details also

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`;


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

Saviyntreh
New Contributor III
New Contributor III

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

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

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; 


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.