Saviynt Forums

sudeshjaiswal · ‎05/22/2023

Use Case

Need a report to find Remove access tasks created in Saviynt for a set of accounts for given endpoints.
This report should have details like the type of task, date, status, account details, entitlement details, and provisioning comments.
We can keep changing the input to use the same report for different endpoints and sets of accounts.
Once configured, you can keep updating the Analytics report for different account names, and endpoint IDs and run it as per IA requests.

Pre-requisites

NA

Applicable Version(s)

All

Solution

The following report needs to be configured in order to fetch the desired resultset:

SELECT arstasks.taskkey,
       arstasks.taskdate,
       CASE
         WHEN arstasks.tasktype = 1 THEN 'Add Access'
         WHEN arstasks.tasktype = 2 THEN 'Remove Access'
         WHEN arstasks.tasktype = 3 THEN 'New Account'
         WHEN arstasks.tasktype = 4 THEN 'Role'
         WHEN arstasks.tasktype = 5 THEN 'Change Password'
         WHEN arstasks.tasktype = 6 THEN 'Enable Account'
         WHEN arstasks.tasktype = 7 THEN 'Claim Account'
         WHEN arstasks.tasktype = 8 THEN 'Delete Account'
         WHEN arstasks.tasktype = 9 THEN 'Update User'
         WHEN arstasks.tasktype = 12 THEN 'Update Account'
         WHEN arstasks.tasktype = 13 THEN 'Proposed Entitlement Owner'
         WHEN arstasks.tasktype = 14 THEN 'Disable Account'
         WHEN arstasks.tasktype = 23 THEN 'Modify Privilege'
         WHEN arstasks.tasktype = 24 THEN 'Create Entitlement'
         WHEN arstasks.tasktype = 25 THEN 'Add Access Entitlement'
         WHEN arstasks.tasktype = 26 THEN 'Remove Access Entitlement'
         WHEN arstasks.tasktype = 27 THEN 'Update Entitlement'
         WHEN arstasks.tasktype = 28 THEN 'Delete Entitlement'
         WHEN arstasks.tasktype = 29 THEN 'Grant Firefighter ID'
         WHEN arstasks.tasktype = 30 THEN 'Revoke Firefighter ID'
         ELSE arstasks.tasktype
       END AS TASKTYPE,
       accounts.NAME,
       entitlement_values.entitlement_value,
       entitlement_types.entitlementname,
       endpointname,
       CASE accounts.status
         WHEN '1' THEN 'ACTIVE'
         WHEN '2' THEN 'INACTIVE'
         WHEN 'Manually Provisioned' THEN 'Manually Provisioned'
         WHEN 'Manually Suspended' THEN 'Manually Suspended'
         WHEN 'SUSPENDED FROM IMPORT SERVICE' THEN
         'SUSPENDED FROM IMPORT SERVICE'
         WHEN 'Active' THEN 'Active'
         WHEN 'Inactive' THEN 'Inactive'
       END AS 'ACCOUNT_STATUS',
       CASE arstasks.status
         WHEN '1' THEN 'PENDING'
         WHEN '2' THEN 'IN-PROCESS'
         WHEN '3' THEN 'COMPLETED'
         WHEN '4' THEN 'DISCONTINUED'
         WHEN '8' THEN 'ERROR'
         WHEN '9' THEN 'NO_ACTION_REQUIRED'
       END AS 'TASK_STATUS',
       arstasks.provisioningcomments
FROM   arstasks
       INNER JOIN accounts
               ON arstasks.accountkey = accounts.accountkey
       LEFT JOIN entitlement_values
              ON arstasks.entitlement_valuekey =
                 entitlement_values.entitlement_valuekey
       LEFT JOIN entitlement_types
              ON entitlement_values.entitlementtypekey =
                 entitlement_types.entitlementtypekey
       LEFT JOIN endpoints
              ON accounts.endpointkey = endpoints.endpointkey
WHERE  arstasks.tasktype = 2
       AND accounts.status = 1
       AND accounts.NAME IN ( '10043', '10043' )
       AND endpoints.endpointkey IN ( '1', '2', '32' );

References

https://docs.saviyntcloud.com/bundle/EIC-Admin-v23x/page/Content/Chapter17-EIC-Analytics/Managing-An...