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

Use case:

 Need a report to view all the requests and their corresponding task ids for specific endpoints

 

SOLUTION:

Please configure an analytical report using the query below:

* Add the relevant endpoint name while performing the query

 

SELECT 

    t.*

FROM

    (SELECT DISTINCT

        at.taskkey AS 'SAVIYNT_TASK_ID',

            CASE

                WHEN at.tasktype = '1' THEN 'ADD'

                WHEN at.tasktype = '2' THEN 'Remove Access'

                WHEN at.tasktype = '3' THEN 'NEWACCOUNT'

                WHEN at.tasktype = '4' THEN 'Role Request'

                WHEN at.tasktype = '5' THEN 'CHANGEPASSWORD'

                WHEN at.tasktype = '6' THEN 'ENABLE ACCOUNT'

                WHEN at.tasktype = '7' THEN 'PROPOSED ACCOUNT OWNERS'

                WHEN at.tasktype = '8' THEN 'DELETE ACCOUNT'

                WHEN at.tasktype = '9' THEN 'UPDATE USER'

                WHEN at.tasktype = '12' THEN 'UPDATE ACCOUNT'

                WHEN at.tasktype = '13' THEN 'PROPOSED Entitlement OWNERS'

                WHEN at.tasktype = '14' THEN 'DISABLE ACCOUNT'

                WHEN at.tasktype = '23' THEN 'MODIFY PRIVILEGE'

                WHEN at.tasktype = '24' THEN 'CREATE ENTITLEMENT'

                WHEN at.tasktype = '27' THEN 'UPDATE ENTITLEMENT'

                WHEN at.tasktype = '28' THEN 'DELETE ENTITLEMENT'

                WHEN at.tasktype = '25' THEN 'UPDATE ENTITLEMENT ACCESS ADD'

                WHEN at.tasktype = '26' THEN 'UPDATE ENTITLEMENT ACCESS REMOVE'

                WHEN at.tasktype = '29' THEN 'FIREFIGHTER ID GRANT ACCESS'

                WHEN at.tasktype = '30' THEN 'FIREFIGHTER ID REVOK EACCESS'

                ELSE at.tasktype

            END AS 'TASK_TYPE',

            CASE

                WHEN at.status = '1' THEN 'NEW'

                WHEN at.status = '2' THEN 'IN PROGRESS'

                WHEN at.status = '3' THEN 'COMPLETE'

                WHEN at.status = '4' THEN 'DISCONTINUED'

                WHEN at.status = '5' THEN 'PENDING CREATE'

                WHEN at.status = '6' THEN 'PENDING Provisioning'

                WHEN at.status = '7' THEN 'Provisioning FAILED'

                WHEN at.status = '8' THEN 'ERROR'

                WHEN at.status = '9' THEN 'NO ACTION REQUIRED'

                ELSE at.status

            END AS 'TASK_STATUS',

            et.ENTITLEMENTNAME AS 'ENTITLEMENT_TYPE',

            ev.entitlement_value AS 'ENTITLEMENT_NAME',

            at.STARTDATE AS 'CREATE_DATE',

            u.FIRSTNAME AS 'FIRST_NAME',

            u.LASTNAME AS 'LAST_NAME',

            u.email AS 'EMAIL',

            u.username AS 'USER_LOGIN',

            SUBSTRING_INDEX(ar.jbpmprocessinstanceid, '.', - 1) AS 'RequestID',

            CASE

                WHEN ra.status = '1' THEN 'REQUEST IS PENDING'

                WHEN ra.status = '2' THEN 'Request is APPROVED'

                WHEN ra.status = '3' THEN 'Request is approved and task created'

                WHEN ra.status = '4' THEN 'Request is rejected'

                WHEN ra.status = '5' THEN 'Request is completed'

                WHEN ra.status = '6' THEN 'Revoke task is created after end endate'

                WHEN ra.status = '7' THEN 'Task duration is expired'

                WHEN ra.status = '6' THEN 'Request is discontinued'

                ELSE ra.status

            END AS 'REQUEST_ACTION'

    FROM

        ars_requests ar

    LEFT JOIN request_access ra ON ar.requestkey = ra.requestkey

    LEFT JOIN access_approvers aa ON aa.request_access_key = ra.request_accesskey

    LEFT JOIN arstasks at ON at.requestaccesskey = ra.request_accesskey

    LEFT JOIN entitlement_values ev ON ra.accesskey = ev.ENTITLEMENT_VALUEKEY

    LEFT JOIN entitlement_types et ON ev.ENTITLEMENTTYPEKEY = et.ENTITLEMENTTYPEKEY

    LEFT JOIN users u ON u.userkey = ra.userkey

    WHERE

        ar.requestkey IN (SELECT 

                requestkey

            FROM

                arstasks

            WHERE

                status = 1

                    AND endpoint = (SELECT 

                        endpointkey

                    FROM

                        endpoints

                    WHERE

                        endpointname = 'EPMGMPLAN'))

            AND ra.accesskey IN (SELECT 

                ENTITLEMENT_VALUEKEY

            FROM

                entitlement_values

            WHERE

                ENTITLEMENTTYPEKEY IN (SELECT 

                        ENTITLEMENTTYPEKEY

                    FROM

                        entitlement_types

                    WHERE

                        endpointkey = (SELECT 

                                endpointkey

                            FROM

                                endpoints

                            WHERE

                                endpointname = 'EPMGMPLAN'))) UNION SELECT DISTINCT

        at.taskkey AS 'SAVIYNT_TASK_ID',

            CASE

                WHEN at.tasktype = '1' THEN 'ADD'

                WHEN at.tasktype = '2' THEN 'Remove Access'

                WHEN at.tasktype = '3' THEN 'NEWACCOUNT'

                WHEN at.tasktype = '4' THEN 'Role Request'

                WHEN at.tasktype = '5' THEN 'CHANGEPASSWORD'

                WHEN at.tasktype = '6' THEN 'ENABLE ACCOUNT'

                WHEN at.tasktype = '7' THEN 'PROPOSED ACCOUNT OWNERS'

                WHEN at.tasktype = '8' THEN 'DELETE ACCOUNT'

                WHEN at.tasktype = '9' THEN 'UPDATE USER'

                WHEN at.tasktype = '12' THEN 'UPDATE ACCOUNT'

                WHEN at.tasktype = '13' THEN 'PROPOSED Entitlement OWNERS'

                WHEN at.tasktype = '14' THEN 'DISABLE ACCOUNT'

                WHEN at.tasktype = '23' THEN 'MODIFY PRIVILEGE'

                WHEN at.tasktype = '24' THEN 'CREATE ENTITLEMENT'

                WHEN at.tasktype = '27' THEN 'UPDATE ENTITLEMENT'

                WHEN at.tasktype = '28' THEN 'DELETE ENTITLEMENT'

                WHEN at.tasktype = '25' THEN 'UPDATE ENTITLEMENT ACCESS ADD'

                WHEN at.tasktype = '26' THEN 'UPDATE ENTITLEMENT ACCESS REMOVE'

                WHEN at.tasktype = '29' THEN 'FIREFIGHTER ID GRANT ACCESS'

                WHEN at.tasktype = '30' THEN 'FIREFIGHTER ID REVOK EACCESS'

                ELSE at.tasktype

            END AS 'TASK_TYPE',

            CASE

                WHEN at.status = '1' THEN 'NEW'

                WHEN at.status = '2' THEN 'IN PROGRESS'

                WHEN at.status = '3' THEN 'COMPLETE'

                WHEN at.status = '4' THEN 'DISCONTINUED'

                WHEN at.status = '5' THEN 'PENDING CREATE'

                WHEN at.status = '6' THEN 'PENDING Provisioning'

                WHEN at.status = '7' THEN 'Provisioning FAILED'

                WHEN at.status = '8' THEN 'ERROR'

                WHEN at.status = '9' THEN 'NO ACTION REQUIRED'

                ELSE at.status

            END AS 'TASK_STATUS',

            et.ENTITLEMENTNAME AS 'ENTITLEMENT_TYPE',

            ev.entitlement_value AS 'ENTITLEMENT_NAME',

            at.STARTDATE AS 'CREATE_DATE',

            u.FIRSTNAME AS 'FIRST_NAME',

            u.LASTNAME AS 'LAST_NAME',

            u.email AS 'EMAIL',

            u.username AS 'USER_LOGIN',

            SUBSTRING_INDEX(ar.jbpmprocessinstanceid, '.', - 1) AS 'RequestID',

            CASE

                WHEN ra.status = '1' THEN 'DEFAULT'

                WHEN ra.status = '2' THEN 'Request is APPROVED'

                WHEN ra.status = '3' THEN 'Request is approved and task created'

                WHEN ra.status = '4' THEN 'Request is rejected'

                WHEN ra.status = '5' THEN 'Request is completed'

                WHEN ra.status = '6' THEN 'Revoke task is created after end endate'

                WHEN ra.status = '7' THEN 'Task duration is expired'

                WHEN ra.status = '6' THEN 'Request is discontinued'

                ELSE ra.status

            END AS 'REQUEST_ACTION'

    FROM

        ars_requests ar

    LEFT JOIN request_access ra ON ar.requestkey = ra.requestkey

    LEFT JOIN access_approvers aa ON aa.request_access_key = ra.request_accesskey

    LEFT JOIN arstasks at ON at.requestaccesskey = ra.request_accesskey

    LEFT JOIN entitlement_values ev ON at.ENTITLEMENT_VALUEKEY = ev.ENTITLEMENT_VALUEKEY

    LEFT JOIN entitlement_types et ON ev.ENTITLEMENTTYPEKEY = et.ENTITLEMENTTYPEKEY

    LEFT JOIN users u ON u.userkey = ra.userkey

    WHERE

        ar.requestkey IN (SELECT 

                requestkey

            FROM

                arstasks

            WHERE

                status = 1

                    AND endpoint = (SELECT 

                        endpointkey

                    FROM

                        endpoints

                    WHERE

                        endpointname = 'EPMGMPLAN'))

            AND (at.entitlement_valuekey IN (SELECT 

                ENTITLEMENT_VALUEKEY

            FROM

                entitlement_values

            WHERE

                ENTITLEMENTTYPEKEY IN (SELECT 

                        ENTITLEMENTTYPEKEY

                    FROM

                        entitlement_types

                    WHERE

                        endpointkey = (SELECT 

                                endpointkey

                            FROM

                                endpoints

                            WHERE

                                endpointname = 'EPMGMPLAN')))

            OR at.entitlement_valuekey IS NULL)) t

WHERE

    t.request_action != 'DEFAULT'

ORDER BY RequestID

 

Note - Execute this report during periods of low system utilization to minimize disruption to other running processes

Version history
Last update:
‎09/06/2023 06:22 AM
Updated by:
Contributors