and more in a single search tool across platforms. Read the announcement here. |
on 06/30/2023 07:11 AM
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.
Any specific reason why this kind of behaviour is observed?