Converting the custom query

[saimeghana]

Hi Team,

We are upgrading the Saviynt Environment from 23.4 to 23.11, according to Saviynt documentation custom query job's will be disabled in higher version.

We are converting the custom query job to other possible options. we are trying to update the Entitlement_values table customproperty in saviynt.

Issue: Tried with analytics, we don't have an option to update in "Allowed Action". 

And also tried with DB connection using the Entitlementvalue Json we are facing issue.

Question: do we have any other way to update the Entitlement_values table customproperty in Saviynt.

It's urget please help us.

Thanks,

Sai Meghana

[rushikeshvartak]

You can update entitlement_Values using enhanced query. And existing custom query will still work.

Please share custom query if you still need help

[saimeghana]

Hi Rushikesh,

Thank you!

yes, Existing custom query will work but we cannot update in higher version.

Below are the example queries, we have 30+ OU's to update.

UPDATE entitlement_values SET customproperty40 = 1,customproperty20 = 'Common' where entitlement_value like '%,OU=Administration,OU=GROUPS,DC=net';

UPDATE entitlement_values SET customproperty40 = 1,customproperty20 = 65 where entitlement_value like '%,OU=Administration,OU=Groups,DC=net';

UPDATE entitlement_values SET customproperty40 = 1,customproperty20 = 18 where entitlement_value like '%,OU=18,OU=GROUPS,DC=net';

Thanks,

Sai Meghana

[rushikeshvartak · Accepted solution]

Use below query

SELECT
entitlement_Valuekey as entitlement_values__primarykey,
CASE
WHEN entitlement_value LIKE '%,OU=Administration,OU=GROUPS,DC=net' THEN 1
WHEN entitlement_value LIKE '%,OU=Administration,OU=Groups,DC=net' THEN 1
WHEN entitlement_value LIKE '%,OU=18,OU=GROUPS,DC=net' THEN 1
ELSE customproperty40
END AS entitlement_values__customproperty40,
CASE
WHEN entitlement_value LIKE '%,OU=Administration,OU=GROUPS,DC=net' THEN 'Common'
WHEN entitlement_value LIKE '%,OU=Administration,OU=Groups,DC=net' THEN 65
WHEN entitlement_value LIKE '%,OU=18,OU=GROUPS,DC=net' THEN 18
ELSE customproperty20
END AS entitlement_values__customproperty20
FROM
entitlement_values
WHERE
entitlement_value LIKE '%,OU=Administration,OU=GROUPS,DC=net'
OR entitlement_value LIKE '%,OU=Administration,OU=Groups,DC=net'
OR entitlement_value LIKE '%,OU=18,OU=GROUPS,DC=net';

[saimeghana]

Hi Rushikesh,

Thank you! This solution is working.

Thanks,

saimeghana