Creating Reports for Task History

[JohnDoe]

Hello everyone.
I want to create a report with both Pending Task and Complete Task information in analytics.

I want to get a report with the following statuses if possible.
・Task ID
・TASK Type
・User Name
・Accout Name
・Entitlement Name
・Security System Name
・Endpoint Name
・Task Status (Complete or Pending and etc ...)
・Approver
・Request Completion Date

Could you tell me which query satisfies this?

I created the report once with reference to the report below, but it did not include the Endpoint Name, Entitlement Name, etc.

https://forums.saviynt.com/t5/identity-governance/request-status-query-for-request-completed-task-pe...

[sudeshjaiswal · Accepted solution]

Hello @JohnDoe,

Please use below query for your use case:
SELECT a.taskkey            AS 'TASK ID',
       a.taskdate           AS 'TASK CREATE DATE',
       a.updatedate         AS 'TASK UPDATE DATE',
       CASE
         WHEN a.tasktype = 1 THEN 'Add Access'
         WHEN a.tasktype = 2 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 = 14 THEN 'DISABLE ACCOUNT'
         WHEN a.tasktype = 12 THEN 'UPDATE ACCOUNT'
         WHEN a.tasktype = 8 THEN 'DELETE ACCOUNT'
         ELSE a.tasktype
       END                  AS 'TASK TYPE',
       u.username           AS 'USERNAME',
       CASE
         WHEN u.statuskey = 0 THEN 'Inactive'
         WHEN u.statuskey = 1 THEN 'Active'
         ELSE u.statuskey
       END                  AS 'User_Status',
       a.accountname        AS 'ACCOUNT NAME',
       s.systemname         AS 'Security System',
       e.endpointname       AS 'ENDPOINT NAME',
       ev.entitlement_value AS 'ENTITLEMENT VALUE',
       CASE
         WHEN a.status = 1 THEN 'NEW'
         WHEN a.status = 2 THEN 'IN PROGRESS'
         WHEN a.status = 3 THEN 'COMPLETE'
         WHEN a.status = 4 THEN 'DISCONTINUED'
         WHEN a.status = 5 THEN 'PENDING CREATE'
         WHEN a.status = 6 THEN 'PENDING PROVISIONING'
         WHEN a.status = 7 THEN 'PROVISIONING FAILED'
         WHEN a.status = 8 THEN 'ERROR'
         WHEN a.status = 9 THEN 'NO_ACTION_REQUIRED'
         ELSE a.status
       END                  AS 'TaskStatus'
FROM   arstasks a
       INNER JOIN users u
               ON u.userkey = a.userkey
       INNER JOIN endpoints e
               ON a.endpoint = e.endpointkey
       INNER JOIN securitysystems s
               ON s.systemkey = e.securitysystemkey
       LEFT JOIN entitlement_values ev
              ON a.entitlement_valuekey = ev.entitlement_valuekey 

Thanks,

[JohnDoe]

Thank you.

You have been a great help to me.

[jbaskaran]

Hi @sudeshjaiswal,

I am trying to create a similar report but just to pull all failed tasks in Pending/Complete status i.e where provisioning tries are more than twice. Since after maximum retires the Task moves to complete status.

Below is the query, just wanted to validate before creating the report.

SELECT a.taskkey AS 'TASK ID',
a.taskdate AS 'TASK CREATE DATE',
a.updatedate AS 'TASK UPDATE DATE',
a.provisioningtries AS 'PROVISIONINGTRIES',

CASE
WHEN a.tasktype = 1 THEN 'Add Access'
WHEN a.tasktype = 2 THEN 'REMOVE ACCESS'
WHEN a.tasktype = 3 THEN 'NEWACCOUNT'
WHEN a.tasktype = 6 THEN 'ENABLE ACCOUNT'
WHEN a.tasktype = 8 THEN 'DELETE ACCOUNT'
WHEN a.tasktype = 9 THEN 'UPDATE USER'
WHEN a.tasktype = 12 THEN 'UPDATE ACCOUNT'
WHEN a.tasktype = 14 THEN 'DISABLE ACCOUNT'
ELSE a.tasktype
END AS 'TASK TYPE',
u.username AS 'USERNAME',
CASE
WHEN u.statuskey = 0 THEN 'Inactive'
WHEN u.statuskey = 1 THEN 'Active'
ELSE u.statuskey
END AS 'User_Status',
a.accountname AS 'ACCOUNT NAME',
s.systemname AS 'Security System',
e.endpointname AS 'ENDPOINT NAME',
ev.entitlement_value AS 'ENTITLEMENT VALUE',
CASE
WHEN a.status = 2 THEN 'IN PROGRESS'
WHEN a.status = 3 THEN 'COMPLETE'
WHEN a.status = 4 THEN 'DISCONTINUED'
WHEN a.status = 5 THEN 'PENDING CREATE'
WHEN a.status = 6 THEN 'PENDING PROVISIONING'
WHEN a.status = 7 THEN 'PROVISIONING FAILED'
WHEN a.status = 8 THEN 'ERROR'
WHEN a.status = 9 THEN 'NO_ACTION_REQUIRED'
ELSE a.status
END AS 'TaskStatus'
FROM arstasks a
INNER JOIN users u
ON u.userkey = a.userkey
INNER JOIN endpoints e
ON a.endpoint = e.endpointkey
INNER JOIN securitysystems s
ON s.systemkey = e.securitysystemkey
LEFT JOIN entitlement_values ev
ON a.entitlement_valuekey = ev.entitlement_valuekey
WHERE a.provisioningtries > 2;

Thank you in advance.

[sudeshjaiswal]

Hello @jbaskaran,

Query looks fine, you may try!

Thanks

[jbaskaran]

Thank you for your confirmation @sudeshjaiswal.