Entitlement value and approver not visible from data analyzer

[SQ]

Hi,

I have two reports in which i cannot get the entitlement value and the approver from the email approval workflow.

Please check the below query,

SELECT
distinct A.TASKKEY AS 'TASK ID',
case when a.tasktype = 24 then 'Create Entitlement' ELSE A.TASKTYPE END AS 'TASK TYPE',
case when a.status=1 then 'Pending' when a.status=2 then 'In Progress' when a.status=3 then 'Completed' when a.status=8 then 'Error' when a.status=4 then 'Discontinued' else 'a.status' end as 'Task Status',
CONCAT(
U.FIRSTNAME, ' ', U.LASTNAME, ' (',
U.USERNAME, ')'
) AS 'REQUESTED USER',
A.ACCOUNTNAME AS 'ACCOUNT NAME',
SS.DISPLAYNAME AS 'SECURITY SYSTEM',
E.ENDPOINTNAME AS 'ENDPOINT',
ET.DISPLAYNAME AS 'ENTITLEMENT TYPE',
EV.ENTITLEMENT_VALUE AS 'ENTITLEMENT',
A.TASKDATE AS 'TASK DATE',
A.STARTDATE AS 'TASK START DATE'
FROM
arstasks A
LEFT JOIN users U ON U.USERKEY = A.USERKEY
LEFT JOIN endpoints E ON A.ENDPOINT = E.ENDPOINTKEY
LEFT JOIN securitysystems SS ON A.SECURITYSYSTEM = SS.SYSTEMKEY
LEFT JOIN entitlement_values EV ON A.ENTITLEMENT_VALUEKEY = EV.ENTITLEMENT_VALUEKEY
LEFT JOIN entitlement_types ET on EV.ENTITLEMENTTYPEKEY = ET.ENTITLEMENTTYPEKEY
WHERE
a.tasktype=24
and a.UPDATEDATE >= DATE_SUB(SYSDATE(), INTERVAL 60 DAY);

the above query does nt return the entitlement name in te data analyzer, but the value is populated in Completed tasks.


and the other  query,

SELECT
distinct A.TASKKEY AS 'TASK ID',
CASE WHEN A.TASKTYPE = 1 THEN 'ADD Access' WHEN A.TASKTYPE = 2
AND A.ENTITLEMENT_VALUEKEY IS NOT NULL THEN 'Remove Access' WHEN A.TASKTYPE = 3 THEN 'New Account' WHEN A.TASKTYPE = 4 THEN 'New Role Request' WHEN A.TASKTYPE = 5 THEN 'Change Password' WHEN A.TASKTYPE = 6 THEN 'Enable Account' WHEN A.TASKTYPE = 8 THEN 'Remove Account' WHEN A.TASKTYPE = 2
AND A.ENTITLEMENT_VALUEKEY IS NULL THEN 'Remove Account' WHEN A.TASKTYPE = 9 THEN 'Update User' WHEN A.TASKTYPE = 12 THEN 'Update Account' WHEN A.TASKTYPE = 14 THEN 'Disable Account' WHEN a.TASKTYPE='24' THEN 'CREATE ENTITLEMENT'
WHEN a.TASKTYPE='27' THEN 'UPDATE ENTITLEMENT'
WHEN a.TASKTYPE='28' THEN 'DELETE ENTITLEMENT' ELSE A.TASKTYPE END AS 'TASK TYPE',
SUBSTRING_INDEX(AR.JBPMPROCESSINSTANCEID, '.', - 1) AS 'REQUEST ID',
case when a.status=1 then 'Pending' when a.status=3 then 'Completed' when a.status=8 then 'Error' when a.status=4 then 'Discontinued' else 'a.status' end as 'Task Status',
ar.COMMENTS as ' Comments',
(SELECT CONCAT(FIRSTNAME, ' ', LASTNAME, ' (', USERNAME, ')') FROM USERS WHERE AR.REQUESTOR = USERKEY) AS 'REQUESTED BY', AR.REQUESTDATE AS 'Request Date',
CONCAT(
U.FIRSTNAME, ' ', U.LASTNAME, ' (',
U.USERNAME, ')'
) AS 'REQUESTED FOR',
CASE WHEN U.STATUSKEY IS NULL THEN 'NOT AVAILABLE' WHEN U.STATUSKEY = 1 THEN 'ACTIVE' WHEN U.STATUSKEY = 0 THEN 'INACTIVE' ELSE U.STATUSKEY END AS 'USER STATUS',
A.ACCOUNTNAME AS 'ACCOUNT NAME',
SS.DISPLAYNAME AS 'SECURITY SYSTEM',
E.ENDPOINTNAME AS 'ENDPOINT',
ET.DISPLAYNAME AS 'ENTITLEMENT TYPE',
EV.ENTITLEMENT_VALUE AS 'ENTITLEMENT',
A.TASKDATE AS 'TASK DATE',
a.requestedby,
A.UPDATEDATE AS 'TASK UPDATE DATE'
FROM
request_access ra,
arstasks A
LEFT JOIN users U ON U.USERKEY = A.USERKEY
LEFT JOIN endpoints E ON A.ENDPOINT = E.ENDPOINTKEY
LEFT JOIN securitysystems SS ON A.SECURITYSYSTEM = SS.SYSTEMKEY
LEFT JOIN entitlement_values EV ON A.ENTITLEMENT_VALUEKEY = EV.ENTITLEMENT_VALUEKEY
LEFT JOIN entitlement_types ET on EV.ENTITLEMENTTYPEKEY = ET.ENTITLEMENTTYPEKEY
LEFT JOIN ars_requests ar on ar.requestkey = a.requestkey
WHERE
A.COMMENTS LIKE '%approved from email%'
and a.UPDATEDATE >= DATE_SUB(SYSDATE(), INTERVAL 15 DAY)

from the above query, we need the details of who approved the access via email. i could not find the connection for the access_approvers table and arstaks table.

it would be helpful if anyone could assist here.

Thanks.

[Dhruv_S]

Hi @SQ 

The query1 is giving the expected results on trying with a.tasktype in ('1','2', '3') etc.

For tasktype 24, it is giving me No data found as we don't have create entitlement task in our environment which is also expected behaviour.

Please check whether any tasks exist in your environment with task type =24. Also please check the same query for other task types - 1/2/3 etc. Query seems to be fine as it is giving Entitlement names.

Regards,

Dhruv Sharma

[rushikeshvartak · Accepted solution]

SELECT DISTINCT A.taskkey                                                   AS
                'TASK ID',
                CASE
                  WHEN a.tasktype = 24 THEN 'Create Entitlement'
                  ELSE A.tasktype
                END                                                         AS
                'TASK TYPE',
                CASE
                  WHEN a.status = 1 THEN 'Pending'
                  WHEN a.status = 2 THEN 'In Progress'
                  WHEN a.status = 3 THEN 'Completed'
                  WHEN a.status = 8 THEN 'Error'
                  WHEN a.status = 4 THEN 'Discontinued'
                  ELSE 'a.status'
                END                                                         AS
                'Task Status',
                Concat(U.firstname, ' ', U.lastname, ' (', U.username, ')') AS
                'REQUESTED USER',
                r.role_name,
                A.accountname                                               AS
                'ACCOUNT NAME',
                SS.displayname                                              AS
                'SECURITY SYSTEM',
                E.endpointname                                              AS
                'ENDPOINT',
                ET.displayname                                              AS
                'ENTITLEMENT TYPE',
                EV.entitlement_value                                        AS
                'ENTITLEMENT',
                A.taskdate                                                  AS
                'TASK DATE',
                A.startdate                                                 AS
                'TASK START DATE'
FROM   arstasks A
       left join users U
              ON U.userkey = A.userkey
       left join endpoints E
              ON A.endpoint = E.endpointkey
       left join securitysystems SS
              ON A.securitysystem = SS.systemkey
       left join entitlement_values EV
              ON A.entitlement_valuekey = EV.entitlement_valuekey
       left join entitlement_types ET
              ON EV.entitlementtypekey = ET.entitlementtypekey
       left join ROLES r
              ON A.sourceid = r.rolekey
WHERE  a.tasktype = 24
       AND a.updatedate >= Date_sub(SYSDATE(), interval 60 day);