We are delighted to share our new EIC Delivery Methodology for efficiently managing Saviynt Implementations and delivering quick time to value. CLICK HERE.

Enterprise role enddate extend through query

Deepu
New Contributor II
New Contributor II

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

Deepu_1-1702960705225.png

below image show the new  updated end date

Deepu_0-1702960678071.png

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';


16 REPLIES 16

rushikeshvartak
All-Star
All-Star

Update end date in arstasks table & request_Access table


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

"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.

Deepu_0-1704338563634.png

 

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';

You can't have multiple primary key in one table.

Restart server to kill the job.


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

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

Deepu_1-1704340851363.png

Deepu_2-1704341055773.png

 

 

 

Click on Add New Access - Enterprise Role - Search role and then Extend End Date


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

Deepu
New Contributor II
New Contributor II

Deepu_0-1704341511347.png

Deepu_3-1704341870634.png

 

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.

Deepu_2-1704341643272.png

 

 

 

check in role_user_accounts table


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

Deepu
New Contributor II
New Contributor II

Deepu_0-1704342198630.png

 

Deepu_1-1704342327776.png

Not updated

check in request_Access and arstasks table


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

Deepu
New Contributor II
New Contributor II

Deepu_0-1704343445158.png

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';

It should be updated in both tables it seems feature is not working as expected please raise support ticket


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

naveenss
All-Star
All-Star

@Deepu  is the new end date getting updated to the entitlements in the account_entitlements1 table?

 

Regards,
Naveen Sakleshpur
If this reply answered your question, please click the Accept As Solution button to help future users who may have a similar problem.

Deepu
New Contributor II
New Contributor II

I think it is not properly updating in account_entitlements1

Deepu_1-1704729714211.png

 

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;

Deepu_0-1704729639892.png

 



Instead of job run plain single line query


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

Deepu
New Contributor II
New Contributor II

what do you mean?

Select * from role_user_Accounts where accountkey=

select * from account_entitlements1 where accountkey=


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.