Error in Creating Enhanced Query Execution Job

[ibrar1]

Hello,

When we try to create a job of the type Enhanced Query Execution to update attributes on a user, it gives an error: 

This is the current setup of the job: 

This is the SQL provided to the job:

"update arstasks set status =4,provisioningcomments=null where ENDPOINT= (select endpointkey from endpoints where endpointname = 'ActiveDirectory') AND STATUS = 8

CASE
WHEN at.status = '1' THEN 'NEW'
WHEN at.status = '2' THEN 'IN PROGRESS'
WHEN at.status = '3' THEN 'COMPLETE'
WHEN at.status = '4' THEN 'DISCONTINUED'
WHEN at.status = '5' THEN 'PENDING CREATE'
WHEN at.status = '6' THEN 'PENDING Provisioning'
WHEN at.status = '7' THEN 'Provisioning FAILED'
WHEN at.status = '8' THEN 'ERROR'
WHEN at.status = '9' THEN 'NO ACTION REQUIRED' "

This is also happening to other jobs that were all of the type "Execute Extension Queries **DEPRECATED** (CUSTOMQUERYJOB) ". When we copy the SQL queries from any jobs taken from this type and try to run it as a Enhanced Query Execution job, we get SQL errors similar to the one shown above, even though the query worked fine with the deprecated job type in the past. 

[This message has been edited by moderator to disable URL hyperlink]

[NM]

Hi @ibrar1 with enhanced query you can't use update keyword you have to stick to select and refbuild the query using select statement.

Query working in the past will still work without any restriction but new oneswon't.

https://docs.saviyntcloud.com/bundle/EIC-Admin-v23x/page/Content/Chapter10-Job-Control-Panel/Job-Cat...

[Amit_Malik]

Hi @ibrar1 ,

1) User table is restricted for use from enhanced queries. You can use Saviynt API instead - ECM/api/v5/updateUser

2) We have to write select query now for updates and inserts

Please read this doc for more details 

https://docs.saviyntcloud.com/bundle/EIC-Admin-v23x/page/Content/Chapter10-Job-Control-Panel/Job-Cat...

Restrictions:

You cannot perform any insert or update operation on the following tables:

• Users

• configuration

• p_history

• userlogin_Access

• userloginAccess

• qrtz_blob_triggers

• qrtz_calendars

• qrtz_cron_triggers

• qrtz_fired_triggers

• qrtz_job_details

• qrtz_locks

• qrtz_paused_trigger_grps

• qrtz_scheduler_state

• qrtz_simple_triggers

• qrtz_simprop_triggers

• qrtz_triggers

[rushikeshvartak]

• @ibrar1  Use below query [Make sure you add limit else it will impact all records]

SELECT at.taskkey              AS arstasks__primarykey,
      4               AS arstasks__status,
       at.provisioningcomments AS arstasks__provisioningcomments
FROM   arstasks AS at
       JOIN endpoints AS ep
         ON at.endpoint = ep.endpointkey
WHERE  ep.endpointname = 'ActiveDirectory'
       AND at.status = 8 limit 1; 

[stalluri]

@ibrar1 
Test with few and by putting in the task key.

SELECT 4 AS arstasks__status,
       at.taskkey AS arstasks__primarykey,
       at.provisioningcomments AS arstasks__provisioningcomments
FROM arstasks AS at
    JOIN endpoints AS ep
        ON at.endpoint = ep.endpointkey
WHERE ep.endpointname = 'ActiveDirectory'
      AND at.status = 8
      AND at.taskkey in ( XXXXXXXX );

If you what to update all the data present in the ARSTASK table  go ahead and remove  AND at.taskkey in ( XXXXXXXX ); from the above query.
OR 
If you want to do it for task in last 30days.

SELECT 4 AS arstasks__status,
       at.taskkey AS arstasks__primarykey,
       at.provisioningcomments AS arstasks__provisioningcomments
FROM arstasks AS at
    JOIN endpoints AS ep
        ON at.endpoint = ep.endpointkey
WHERE ep.endpointname = 'ActiveDirectory'
      AND at.status = 8
      AND at.UPDATEDATE >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);