Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.
This website uses cookies. By clicking Accept, you consent to the use of cookies. Click Here to learn more about how we use cookies.
Accept
Reject
Help
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
No ratings

How to find missing revoke tasks as part of the revocations for a user manager campaign

prasannta
Saviynt Employee
Saviynt Employee

on ‎06/30/2023 07:11 AM

Use Case

You might have seen an issue where revoke tasks would not get created for the revocations in a user manager campaign or sometimes these reports are just required for audit purposes to validate the tasks created against the revocations. 

Pre-requisites

None

Applicable Version(s)


All

Solution


Identify the tasks that were not created and create them manually to remediate the impact and check the logs so as to why were the tasks not created. You can do that by checking the timestamp of a particular revocation. In order to find the missing revoke tasks, you can use the below query: 

 

select tfinal.* from (SELECT

    t4.*, t6.TASKKEY, t6.STATUS, t6.TASKDATE, t6.ENDPOINTNAME

FROM

    (SELECT DISTINCT

        t.campaign_name,

            t.start_date,

            t.end_date,

            t.CERT_NAME,

            t.ACCOUNTKEY,

            NAME,

            'BASE ACCOUNT' AS ENTITLEMENT_VALUEKEY,

            'BASE ACCOUNT' AS ENTITLEMENT_VALUE

    FROM

        (SELECT DISTINCT

        campaign_name,

            campaign.start_date,

            campaign.end_date,

            CERT_NAME,

            certification_account.ACCOUNTKEY,

            certification_account.ENDPOINTKEY,

            certification_account.NAME

    FROM

        campaign, certification, certification_user_account_status, certification_account

    WHERE

        campaign.id = certification.CAMPAIGNKEY

            AND certification.CERTKEY = certification_user_account_status.CERTKEY

            AND certification_user_account_status.CERT_ACCOUNTKEY = certification_account.CERT_ACCOUNTKEY

            AND certification.STATUS IN (3 , 8 , 10)

            AND certification_user_account_status.CERTIFIED IN (0 , 3)

            AND campaign_name = 'User access review for ROLE_ILM_CERTIFICATION_ADMIN') t) t4

        LEFT JOIN

    (SELECT DISTINCT

        t.campaign_name,

            t.start_date,

            t.end_date,

            t.CERT_NAME,

            t.ACCOUNTKEY,

            NAME,

            'BASE ACCOUNT' AS ENTITLEMENT_VALUE,

            arstasks.TASKKEY,

            arstasks.STATUS,

            arstasks.TASKDATE,

            endpoints.ENDPOINTNAME

    FROM

        (SELECT DISTINCT

        campaign_name,

            campaign.start_date,

            campaign.end_date,

            CERT_NAME,

            certification_account.ACCOUNTKEY,

            certification_account.ENDPOINTKEY,

            certification_account.NAME

    FROM

        campaign, certification, certification_user_account_status, certification_account

    WHERE

        campaign.id = certification.CAMPAIGNKEY

            AND certification.CERTKEY = certification_user_account_status.CERTKEY

            AND certification_user_account_status.CERT_ACCOUNTKEY = certification_account.CERT_ACCOUNTKEY

            AND certification.STATUS IN (3 , 8 , 10)

            AND certification_user_account_status.CERTIFIED IN (0 , 3)

            AND campaign_name = 'User access review for ROLE_ILM_CERTIFICATION_ADMIN') t

    LEFT JOIN arstasks ON t.accountkey = arstasks.accountkey

    LEFT JOIN endpoints ON t.endpointkey = endpoints.ENDPOINTKEY

    WHERE

        arstasks.TASKTYPE = 2

            AND (arstasks.TASKDATE <= DATE_ADD(t.end_date, INTERVAL 10 DAY))

            AND (arstasks.TASKDATE >= t.start_date)

            AND arstasks.ENTITLEMENT_VALUEKEY IS NULL

            AND arstasks.SOURCE LIKE '%CERTIFICATION%') t6 

            ON (t4.CERT_NAME = t6.CERT_NAME

        AND t4.accountkey = t6.accountkey)

UNION SELECT

    t7.campaign_name,

    t7.start_date,

    t7.end_date,

    t7.CERT_NAME,

    t7.ACCOUNTKEY,

    t7.NAME,

    t7.ENTITLEMENT_VALUEKEY,

    t7.ENTITLEMENT_VALUE,

    t8.TASKKEY,

    t8.STATUS,

    t8.TASKDATE,

    t7.ENDPOINTNAME

FROM

    (SELECT DISTINCT

        campaign_name,

            campaign.start_date,

            campaign.end_date,

            CERT_NAME,

            certification_account.ACCOUNTKEY,

            certification_account.ENDPOINTKEY,

            endpoints.ENDPOINTNAME,

            certification_account.NAME,

            certification_entitlement_value.ENTITLEMENT_VALUEKEY,

            certification_entitlement_value.ENTITLEMENT_VALUE

    FROM

        campaign, certification, certification_account_entitlement1_status, certification_entitlement_value, certification_account, endpoints

    WHERE

        campaign.id = certification.CAMPAIGNKEY

            AND certification.CERTKEY = certification_account_entitlement1_status.CERTKEY

            AND certification_account_entitlement1_status.CERT_ACCOUNTKEY = certification_account.CERT_ACCOUNTKEY

            AND certification_account_entitlement1_status.CERT_ENTITLEMENT_VALUEKEY = certification_entitlement_value.CERT_ENTITLEMENT_VALUEKEY

            AND certification.STATUS IN (3 , 8 , 10)

            AND certification_account_entitlement1_status.CERTIFIED IN (0 , 3)

            AND campaign_name = 'User access review for ROLE_ILM_CERTIFICATION_ADMIN'

            AND certification_account.ENDPOINTKEY = endpoints.ENDPOINTKEY) t7

        LEFT JOIN

    (SELECT DISTINCT

        t.campaign_name,

            t.start_date,

            t.end_date,

            t.CERT_NAME,

            t.ACCOUNTKEY,

            NAME,

            ENTITLEMENT_VALUE,

            arstasks.TASKKEY,

            arstasks.STATUS,

            arstasks.TASKDATE,

            endpoints.ENDPOINTNAME

    FROM

        (SELECT DISTINCT

        campaign_name,

            campaign.start_date,

            campaign.end_date,

            CERT_NAME,

            certification_account.ACCOUNTKEY,

            certification_account.ENDPOINTKEY,

            certification_account.NAME,

            certification_entitlement_value.ENTITLEMENT_VALUEKEY,

            certification_entitlement_value.ENTITLEMENT_VALUE

    FROM

        campaign, certification, certification_account_entitlement1_status, certification_entitlement_value, certification_account

    WHERE

        campaign.id = certification.CAMPAIGNKEY

            AND certification.CERTKEY = certification_account_entitlement1_status.CERTKEY

            AND certification_account_entitlement1_status.CERT_ACCOUNTKEY = certification_account.CERT_ACCOUNTKEY

            AND certification_account_entitlement1_status.CERT_ENTITLEMENT_VALUEKEY = certification_entitlement_value.CERT_ENTITLEMENT_VALUEKEY

            AND certification.STATUS IN (3 , 8 , 10)

            AND certification_account_entitlement1_status.CERTIFIED IN (0 , 3)

            AND campaign_name = 'User access review for ROLE_ILM_CERTIFICATION_ADMIN') t

    LEFT JOIN arstasks ON (t.accountkey = arstasks.accountkey

        AND t.entitlement_valuekey = arstasks.ENTITLEMENT_VALUEKEY)

    LEFT JOIN endpoints ON t.endpointkey = endpoints.ENDPOINTKEY

    WHERE

        arstasks.TASKTYPE = 2

            AND (arstasks.TASKDATE <= DATE_ADD(t.end_date, INTERVAL 10 DAY))

            AND (arstasks.TASKDATE >= t.start_date)

            AND arstasks.SOURCE LIKE '%CERTIFICATION%') t8 ON (t7.CERT_NAME = t8.CERT_NAME

        AND t7.accountkey = t8.accountkey

        AND t7.ENTITLEMENT_VALUE = t8.ENTITLEMENT_VALUE)

UNION SELECT

    t1.campaign_name,

    t1.start_date,

    t1.end_date,

    t1.CERT_NAME,

    t1.ACCOUNTKEY,

    t1.NAME,

    t1.ENTITLEMENT_VALUEKEY,

    t1.ENTITLEMENT_VALUE,

    t2.TASKKEY,

    t2.STATUS,

    t2.TASKDATE,

    t1.ENDPOINTNAME

FROM

    (SELECT

        campaign.campaign_name,

            campaign.start_date,

            campaign.end_date,

            certification.CERT_NAME,

            certification_account.ACCOUNTKEY,

            NAME,

            'TERMINATED USER' AS 'ENTITLEMENT_VALUEKEY',

            'TERMINATED USER' AS 'ENTITLEMENT_VALUE',

            endpoints.ENDPOINTNAME

    FROM

        certification_user_status, endpoints, certification_user_account_status, certification_account, certification, campaign

    WHERE

        certification_user_status.certified = 4

            AND certification_user_account_status.CERT_USERKEY = certification_user_status.CERT_USERKEY

            AND certification_account.CERT_ACCOUNTKEY = certification_user_account_status.CERT_ACCOUNTKEY

            AND certification.CERTKEY = certification_user_status.CERTKEY

            AND certification.CAMPAIGNKEY = campaign.ID

            AND campaign.campaign_name = 'User access review for ROLE_ILM_CERTIFICATION_ADMIN'

            AND certification_account.ENDPOINTKEY = endpoints.ENDPOINTKEY) t1

        LEFT JOIN

    (SELECT DISTINCT

        t.*, arstasks.TASKKEY, arstasks.STATUS, arstasks.TASKDATE

    FROM

        (SELECT

        campaign.campaign_name,

            campaign.start_date,

            campaign.end_date,

            certification.CERT_NAME,

            certification_account.ACCOUNTKEY,

            certification_account.ENDPOINTKEY,

            NAME,

            'TERMINATED USER' AS 'ENTITLEMENT_VALUE'

    FROM

        certification_user_status, certification_user_account_status, certification_account, certification, campaign

    WHERE

        certification_user_status.certified = 4

            AND certification_user_account_status.CERT_USERKEY = certification_user_status.CERT_USERKEY

            AND certification_account.CERT_ACCOUNTKEY = certification_user_account_status.CERT_ACCOUNTKEY

            AND certification.CERTKEY = certification_user_status.CERTKEY

            AND certification.CAMPAIGNKEY = campaign.ID

            AND campaign.campaign_name = 'User access review for ROLE_ILM_CERTIFICATION_ADMIN') t

    INNER JOIN arstasks ON (t.accountkey = arstasks.accountkey)

    WHERE

        arstasks.TASKTYPE = 2

            AND (arstasks.TASKDATE <= DATE_ADD(t.end_date, INTERVAL 10 DAY))

            AND (arstasks.TASKDATE >= t.start_date)

            AND arstasks.ENTITLEMENT_VALUEKEY IS NULL

            AND arstasks.SOURCE LIKE '%CERTIFICATION%') t2 ON (t1.ACCOUNTKEY = t2.ACCOUNTKEY))tfinal where campaign_name ='<<Campaign Name>>';

 

You can edit the campaign name in the last line of the query. It will give you a list of all the revocations in a particular user manager campaign along with their task details. Please note that wherever the taskkey is null, that means that the corresponding revoke task was not created.

Note - since this is a costly query, you should run it when the system is Not heavily used, so that it does not impact any other process.


References

0 Kudos
Comments
Manu269
All-Star
All-Star
‎07/05/2023 07:43 AM

Any specific reason why this kind of behaviour is observed?

Version history
Last update:
‎06/30/2023 07:11 AM
Updated by:
Saviynt Employee
Contributors
Copyright © 2024 Khoros, LLC