Saviynt unveils its cutting-edge Intelligence Suite products to revolutionize Identity Security!
Click HERE to see how Saviynt Intelligence is transforming the industry.
Saviynt Copilot Icon

Query on preprocessor Additionaltable execution

rameshm
New Contributor III
New Contributor III

Hello Experts,

Can you please help me to understand, how many times the ADDITIONALTABLES gets executed during a single import job. Does it executes again and again per record?

In case any customProperty is getting updated and if we are having same customproperty used as part of ADDITIONALTABLES, would the currentUsers table will have updated value?

Thanks in advance.

Regards,

Ramesh

5 REPLIES 5

itinjic
Regular Contributor
Regular Contributor

During a single import job, the ADDITIONALTABLES section is executed only once. It is used to prepare the dataset on which the preprocessor queries will be run. The result of the queries is stored in temporary tables, which are then used in the preprocessor queries.

In the case of updating a customProperty that is also used in the ADDITIONALTABLES section, the value in the currentUsers table will not be automatically updated. The ADDITIONALTABLES section is executed before the preprocessor queries, so any updates made during the import job will not be reflected in the temporary tables.

If you want to update the value in the currentUsers table, you will need to include a preprocessor query that updates the customProperty in the currentUsers table based on the updated value in the imported data.

Sapere aude

rushikeshvartak
All-Star
All-Star
  • It creates temporary tables and select query is executed once but update query is ran per user

Regards,
Rushikesh Vartak
If this helped you move forward, click 'Kudos'. If it solved your query, select 'Accept As Solution'.

rameshm
New Contributor III
New Contributor III

Could you please help me with some update statement preprocessor query to update "enddate" in CURRENTUSERS table, any sample would be helpful.

Refer samples https://forums.saviynt.com/t5/tag/Modifyuserdatajson/tg-p 


Regards,
Rushikesh Vartak
If this helped you move forward, click 'Kudos'. If it solved your query, select 'Accept As Solution'.

PremMahadikar
All-Star
All-Star

Hi @rameshm ,

Try below:

{ 
"ADDITIONALTABLES" :{ 
"USERS" : "SELECT username, enddate FROM USERS" 
},
"COMPUTEDCOLUMNS" : [ 
"enddate"
],
"PREPROCESSQUERIES" :[
"UPDATE CURRENTUSERS LEFT JOIN NEWUSERDATA ON CURRENTUSERS.USERNAME=NEWUSERDATA.USERNAME SET NEWUSERDATA.enddate = CASE WHEN Date(NEWUSERDATA.startdate) > Date(CURRENTUSERS.enddate) THEN NULL ELSE CURRENTUSERS.enddate END"
]
}

Note: The above script works when you have username, startdateenddate columns as mandatory with/without additional columns.

 

If this helps, please select Accept As Solution and hit Kudos