Preprocessor unable to populate the secondary manager based on the previous manager

[KumariPinisetti]

Hello Team,

Under ModifyUserJSON, we have written pre processor code. For third-party users, we are using CSV to import user data.

Using Case:

Whenever a user's manager changed, the value of the previous manager has to be saved in the secondary manager field.
In Saviynt EIC, when I tried the constant secondary manager value, it displayed with the right value, but when I tried the following query, it displayed as a blank value.

{
"ADDITIONALTABLES": {
"USERS": "SELECT username,manager,secondaryManager FROM USERS" },
"COMPUTEDCOLUMNS": ["userName","manager","secondaryManager"],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA,CURRENTUSERS SET NEWUSERDATA.secondaryManager=CURRENTUSERS.manager where NEWUSERDATA.userName=CURRENTUSERS.userName and CURRENTUSERS.manager!=NEWUSERDATA.manager"
]}

Thank you in advance,

Kumari

[adriencosson]

Hi @KumariPinisetti ,

Were you able to find any log to troubleshoot ?

A quick note is that you're using "userName" instead of "username" as column name, which are case sensitive. Please try to replace this value and let us know.

[KumariPinisetti]

Hi @adriencosson 

Thank you for your response, I have made the changes to username In logs I'm getting below error.

com.mysql.jdbc.MysqlDataTruncation: Data truncation: Truncated incorrect DOUBLE value:

Here I'm attaching the logs for your reference.

Thanks,

Kumari

[dgandhi]

Can you recheck the query once? There is error with respect to data.

2023-08-21T16:44:28+05:30-ecm----21-Aug-2023 11:14:27.488 WARNING [http-nio-8080-exec-5] groovy.sql.Sql.executeUpdate Failed to execute: UPDATE TEMPNEWUSERS_29488,TEMPUSERS_29488 SET TEMPNEWUSERS_29488.secondaryManager=TEMPUSERS_29488.manager where TEMPNEWUSERS_29488.username=TEMPUSERS_29488.username and TEMPUSERS_29488.manager!=TEMPNEWUSERS_29488.manager because: Data truncation: Truncated incorrect DOUBLE value: 'ashutoshkumar16'
2023-08-21T16:44:27+05:30-ecm-services.ImportSAvDataUserService-http-nio-8080-exec-5-ERROR-Error while processing data:
2023-08-21T16:44:28+05:30-ecm----com.mysql.jdbc.MysqlDataTruncation: Data truncation: Truncated incorrect DOUBLE value: 'testuser'

[KumariPinisetti]

Hey Team,

We are trying to update the user through CSV and use the preprocessor query below to populate the secondary manager, however even if the user has other values updated, the secondary manager value is not updated, and there is no error recorded in the logs.

Here is the query for reference.

{
"ADDITIONALTABLES": {
"USERS": "SELECT username,manager,secondaryManager FROM USERS" },
"COMPUTEDCOLUMNS": ["username","manager","secondaryManager"],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA,CURRENTUSERS SET NEWUSERDATA.secondaryManager=CURRENTUSERS.manager where NEWUSERDATA.username='CURRENTUSERS.username' and CURRENTUSERS.manager!=NEWUSERDATA.manager"
]}

Any Suggestions on this appreciated.

Thanks,

Kumari

[Hemanath]

@KumariPinisetti 

try below sql query

UPDATE NEWUSERDATA,CURRENTUSERS JOIN NEWUSERDATA ND ON ND.USERNAME = CURRENTUSERS.USERNAME SET ND.SECONDARYMANAGER=CURRENTUSERS.MANAGER WHERE ND.SECONDARYMANAGER!=CURRENTUSERS.MANAGER

Thanks,

Hemanath J

[KumariPinisetti]

Hi Hemanath,

I have tried the above SQL query in the preprocessor, In logs I have noticed the "MySQLSyntaxErrorException". I have tried few other changes to the query, still getting the same issue.

Here I'm attaching the logs for your reference. Let me know If I need to perform any other changes to the query.

Thanks,

Kumari

[Hemanath]

@KumariPinisetti  I have updated the query try with the new one

UPDATE NEWUSERDATA,CURRENTUSERS JOIN NEWUSERDATA ND ON ND.USERNAME = CURRENTUSERS.USERNAME SET ND.SECONDARYMANAGER=CURRENTUSERS.MANAGER WHERE ND.SECONDARYMANAGER!=CURRENTUSERS.MANAGER

[KumariPinisetti]

@Hemanath Thankyou for the response.

I have executed the preprocessor using the revised query, and I observed from the logs that it was able to get the userkey for the secondayManager value and that it's been updating the CSV Metadata file. When comparing the recon value, it checks for the username.

Here I attach the marked logs for your reference. 

Even the query being able to retrieve the manager name in dataanalyzer here, I added below  subquery to obtain the username in CSV metadata.

UPDATE NEWUSERDATA,CURRENTUSERS JOIN NEWUSERDATA ND ON ND.USERNAME=CURRENTUSERS.USERNAME SET ND.SECONDARYMANAGER='(SELECT M.USERNAME from CURRENTUSERS CU JOIN CURRENTUSERS M ON M.USERKEY=CU.MANAGER)' WHERE ND.SECONDARYMANAGER!=CURRENTUSERS.MANAGER

Thanks,

Kumari

[ashutoshkumar16]

@Hemanath @dgandhi 

The problem with the use case is the CURRENTUSER.Manager has the value of userkey not the username. so we cannot directly take the CURRENTUSER.Manager value to update the NEWUSERDATA.SecondaryManager.

We have to first get the CURRENTUSER.Manager which is the Userkey value and then get the username of the current User.