The custom query job type removed in Saviynt 23.8 version. How the updates can be done for Accountco

[kumar99]

I have to update the Accountconfig attribute values using the below update query. In Version 23.8 the custom query Job has been removed. Please let us know the option of updating the accountconfig.

I have to update this "accountconfig" for 970+ accounts.

update accounts set ACCOUNTCONFIG='{"defaultrequestabletimeforidinsecs":"86400","defaultrequestabletimeinsecs":"86400","maxrequestabletimeinsecs":"172800","diffbetweenrequests":"","authenticationType":"","maxrequestabletimeforidinsecs":"172800","Saviynt-Status":{"pamState":"ENABLED","pamType":"CREDENTIALLESS","errorDetails":"","justInTime":""}}' where name like 'SAV_CPAM_%';

[BrandonLucas_BF]

Enhanced query execution job. Go here for more info:

Job Categories for Flat Job Control Panel (saviyntcloud.com)

[kumar99]

@Brandon_Lucas -> When i try to execute a simple select query like below.

select name from accounts where endpointkey=80 and name='ASDEFH';, I get error as per below screen shot.

Also, the simple update query is not working.

Please provide us the solution as this is important for us. We are in 23.8 version.

[BrandonLucas_BF]

Your format is incorrect. Check the documentation. Needs to be similar to this:

SELECT '{"defaultrequestabletimeforidinsecs":"86400","defaultrequestabletimeinsecs":"86400","maxrequestabletimeinsecs":"172800","diffbetweenrequests":"","authenticationType":"","maxrequestabletimeforidinsecs":"172800","Saviynt-Status":{"pamState":"ENABLED","pamType":"CREDENTIALLESS","errorDetails":"","justInTime":""}}' as ACCOUNTCONFIG,acc.accountkey as ACCOUNTS__PRIMARYKEY FROM ACCOUNTS acc WHERE acc.name like 'SAV_CPAM_%'

[kumar99]

@BrandonLucas_BF -> I tried with the simple query and still it is not working as expected.

I got the below error when i run the job Enhanced query execution job. even for simple query i am unable to update any account. The "provided Table and Column name not in format".

[BrandonLucas_BF]

Can you paste exactly what is in your job?

This is the syntax - taken from Frequently Asked Questions (saviyntcloud.com)

To update records:

SELECT
<VALUE_TO_BE_INSERTED> AS <DESTINATION_TABLE_NAME>__<COLUMN_NAME1>, <VALUE_TO_BE_INSERTED> AS <DESTINATION_TABLE_NAME >__<COLUMN_NAME2>, <DESTINATION_TABLE.PRIMARYCOLUMN> AS <DESTINATION_TABLE_NAME>__PRIMARYKEY
FROM <SOURCE_TABLE_NAME>
JOIN <DESTINATION_TABLE_NAME> ON <SOURCE_TABLE_NAME>. COL1 = <DESTINATION_TABLE_NAME>.COL2
WHERE <any condition>;

Where,

• <SOURCE_TABLE_NAME>: Specify the source table here

• <VALUE_TO_BE_INSERTED>: Specify the SOURCE_TABLE.COLUMN or a literal value

• <DESTINATION_TABLE_NAME: Specify the destination table here

• <DESTINATION_TABLE_NAME>__PRIMARYKEY: This is mandatory for any update operation

Note

Add double underscores (__) in the alias between the destination table and the column.

SELECT
<VALUE_TO_BE_INSERTED> AS <DESTINATION_TABLE_NAME>__<COLUMN_NAME1>, <VALUE_TO_BE_INSERTED> AS <DESTINATION_TABLE_NAME >__<COLUMN_NAME2>, <DESTINATION_TABLE.PRIMARYCOLUMN> AS <DESTINATION_TABLE_NAME>__PRIMARYKEY
FROM <SOURCE_TABLE_NAME>
JOIN <DESTINATION_TABLE_NAME> ON <SOURCE_TABLE_NAME>. COL1 = <DESTINATION_TABLE_NAME>.COL2
WHERE <any condition>;

[BrandonLucas_BF · Accepted solution]

Also wanted to include this from the above references. Have you checked the documentation? These are good resources: