Hello,
I am trying to extend the end date for the enterprise role. I am able to update the new end date, but the role removal is still getting triggered after the old end date has been met, even though the end date has now been updated.
Below Image shows the existing end date
below image show the new updated end date
Even though the date has been updated, the role removal is still getting triggered when the old end date is met.
Can someone please help.
Below query iam using
Query1
select
CAST("2033-11-01" AS DATETIME) AS role_user_account__ENDDATE,
rua.id AS role_user_account__PRIMARYKEY,
CAST("2033-11-01" AS DATETIME) AS account_entitlements1__ENDDATE,
ae1.ACCENTKEY AS account_entitlements1__PRIMARYKEY
from role_user_account rua,roles r, accounts a,account_entitlements1 ae1,entitlement_values ev,users u
where
r.ROLEKEY = rua.ROLEKEY
and a.ACCOUNTKEY = rua.ACCOUNTKEY
and a.ACCOUNTKEY = ae1.ACCOUNTKEY
and ev.ENTITLEMENT_VALUEKEY = ae1.ENTITLEMENT_VALUEKEY
and u.USERKEY = rua.USERKEY
and rua.ENDDATE IS NOT NULL
and r.STATUS = 1 and a.STATUS IN ('1', 'Active', 'Manually Provisioned')
and u.USERKEY = '18111' and r.ROLEKEY = '832';
Query2
SELECT
CAST('2033-11-01' AS DATETIME) AS "role_user_account__ENDDATE",
rua.id AS "role_user_account__PRIMARYKEY",
CAST('2033-11-01' AS DATETIME) AS "account_entitlements1__ENDDATE",
ae1.ACCENTKEY AS "account_entitlements1__PRIMARYKEY"
FROM roles role
INNER JOIN role_entitlements role_ent ON role.rolekey = role_ent.rolekey
INNER JOIN entitlement_values parent ON role_ent.entitlement_valuekey = parent.entitlement_valuekey
INNER JOIN entitlements2 ev2 ON parent.entitlement_valuekey = ev2.entitlement_value1key
INNER JOIN entitlement_values child ON ev2.entitlement_value2key = child.entitlement_valuekey
INNER JOIN entitlement_types type ON parent.entitlementtypekey = type.entitlementtypekey
INNER JOIN role_user_account rua ON role.rolekey = rua.ROLEKEY
INNER JOIN accounts a ON rua.ACCOUNTKEY = a.ACCOUNTKEY
INNER JOIN account_entitlements1 ae1 ON a.ACCOUNTKEY = ae1.ACCOUNTKEY
INNER JOIN users u ON rua.USERKEY = u.USERKEY
WHERE
u.USERKEY = '18975'
AND role.rolekey = '1213';
"I attempted to run the Enhanced query Job with the below query, but it appears to be running for an extended period, possibly getting stuck.
SELECT
CAST('2033-11-01' AS DATETIME) AS "role_user_account__ENDDATE",
rua.id AS "role_user_account__PRIMARYKEY",
CAST('2033-11-01' AS DATETIME) AS "account_entitlements1__ENDDATE",
ae1.ACCENTKEY AS "account_entitlements1__PRIMARYKEY",
CAST('2033-11-01' AS DATETIME) AS "arstasks__ENDDATE",
arstasks.TASKKEY AS "arstasks__PRIMARYKEY",
CAST('2033-11-01' AS DATETIME) AS "request_access__ENDDATE",
request_access.ACCESSKEY AS "request_access__PRIMARYKEY"
FROM roles role
INNER JOIN role_entitlements role_ent ON role.rolekey = role_ent.rolekey
INNER JOIN entitlement_values parent ON role_ent.entitlement_valuekey = parent.entitlement_valuekey
INNER JOIN entitlements2 ev2 ON parent.entitlement_valuekey = ev2.entitlement_value1key
INNER JOIN entitlement_values child ON ev2.entitlement_value2key = child.entitlement_valuekey
INNER JOIN entitlement_types type ON parent.entitlementtypekey = type.entitlementtypekey
INNER JOIN role_user_account rua ON role.rolekey = rua.ROLEKEY
INNER JOIN accounts a ON rua.ACCOUNTKEY = a.ACCOUNTKEY
INNER JOIN account_entitlements1 ae1 ON a.ACCOUNTKEY = ae1.ACCOUNTKEY
INNER JOIN users u ON rua.USERKEY = u.USERKEY
LEFT JOIN arstasks ON u.USERKEY = arstasks.USERKEY
LEFT JOIN request_access ON u.USERKEY = request_access.USERKEY
WHERE
u.USERKEY = '18974'
AND role.rolekey = '1213';
I am attempting to update the end date through ARS after enabling the end date option in global configuration. However, it appears that this is not working, as I don't see the new end date being updated. Is there any other way to extend the end date for the enterprise roles?
Iam using version 23.9
After submitting the 'Update Access End Date' request, it was created, and after approvals, the task was generated. However, after the job completed, the task also got completed, but the end date is not reflecting.
SELECT
rua.id AS "role_user_account",
arstasks.TASKKEY AS "arstasks",
rua.enddate AS "roleuseraccountenddate",
arstasks.enddate AS "arstasks_enddate",
request_access.enddate AS "requestaccessenddate"
FROM roles role
INNER JOIN role_entitlements role_ent ON role.rolekey = role_ent.rolekey
INNER JOIN entitlement_values parent ON role_ent.entitlement_valuekey = parent.entitlement_valuekey
INNER JOIN entitlements2 ev2 ON parent.entitlement_valuekey = ev2.entitlement_value1key
INNER JOIN entitlement_values child ON ev2.entitlement_value2key = child.entitlement_valuekey
INNER JOIN entitlement_types type ON parent.entitlementtypekey = type.entitlementtypekey
INNER JOIN role_user_account rua ON role.rolekey = rua.ROLEKEY
INNER JOIN accounts a ON rua.ACCOUNTKEY = a.ACCOUNTKEY
INNER JOIN account_entitlements1 ae1 ON a.ACCOUNTKEY = ae1.ACCOUNTKEY
INNER JOIN users u ON rua.USERKEY = u.USERKEY
LEFT JOIN arstasks ON u.USERKEY = arstasks.USERKEY
LEFT JOIN request_access ON u.USERKEY = request_access.USERKEY
WHERE
u.USERKEY = '18974'
AND role.rolekey = '1213';
I think it is not properly updating in account_entitlements1
SELECT
u.username,
u.firstname,
u.lastname,
a.name AS 'Account Name',
r.role_name AS 'Role Name',
r.description AS 'Description',
rua.enddate AS 'Role End Date',
ev.ENTITLEMENT_VALUE AS 'Entitlement',
ae1.ENDDATE AS 'Entitlement End Date'
FROM
users u
JOIN
role_user_account rua ON u.USERKEY = rua.USERKEY
JOIN
roles r ON r.ROLEKEY = rua.ROLEKEY
JOIN
accounts a ON a.ACCOUNTKEY = rua.ACCOUNTKEY
JOIN
account_entitlements1 ae1 ON a.ACCOUNTKEY = ae1.ACCOUNTKEY
JOIN
entitlement_values ev ON ev.ENTITLEMENT_VALUEKEY = ae1.ENTITLEMENT_VALUEKEY
WHERE
rua.ENDDATE IS NOT NULL
AND r.STATUS = 1
AND u.STATUSKEY = 1
AND a.STATUS IN ('1', 'Active', 'Manually Provisioned')
AND u.USERKEY = '18333'
AND r.ROLEKEY = '1163'
ORDER BY
u.username;
