Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.

PreProcessor in SAV file - Manager Value Disappearing

sab2
Regular Contributor
Regular Contributor

Hi,

We are working on our SAV file for user imports. In our preprocessor, we are trying to set the users attribute 'manager' equal to the users attribute 'secondarymanager' only if the users manager is currently null. If the user in Saviynt does already have a manager then we want to leave it as is with the current value. We are using this line: 

UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.EMPLOYEEID = CURRENTUSERS.EMPLOYEEID SET NEWUSERDATA.MANAGER = case when CURRENTUSERS.MANAGER is not null and CURRENTUSERS.MANAGER != '' then CURRENTUSERS.MANAGER WHEN (CURRENTUSERS.MANAGER IS NULL or CURRENTUSERS.MANAGER = '') THEN NEWUSERDATA.SECONDARYMANAGER end",

In our mapping line we only have firstname, lastname, employeeid, secondarymanager. We do not map manager. However, when we run user import job, the query is only half working. If the users current manager is null, it sets the manager equal to secondarymanager. But if the user does have a manager and we run the query again, it takes out the users manager. Leaving this field as blank in Saviynt. Any thoughts on what is causing it to wipe out the manager value? When we use the same query for firstname (below query) it works as expected. Only seeing issues with the manager attributes.

Working: UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.EMPLOYEEID = CURRENTUSERS.EMPLOYEEID SET NEWUSERDATA.FIRSTNAME = case when CURRENTUSERS.FIRSTNAME is not null and CURRENTUSERS.FIRSTNAME != '' then CURRENTUSERS.FIRSTNAME WHEN (CURRENTUSERS.FIRSTNAME IS NULL or CURRENTUSERS.FIRSTNAME = '') THEN NEWUSERDATA.FIRSTNAME end",

Thank You! 

13 REPLIES 13

Saathvik
All-Star
All-Star

I don't see anything wrong in query but can you try this

UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.EMPLOYEEID = CURRENTUSERS.EMPLOYEEID 
SET NEWUSERDATA.MANAGER = case when CURRENTUSERS.MANAGER is not null then CURRENTUSERS.MANAGER
WHEN (CURRENTUSERS.MANAGER IS NULL or CURRENTUSERS.MANAGER = '') THEN NEWUSERDATA.SECONDARYMANAGER end

 


Regards,
Saathvik
If this reply answered your question, please Accept As Solution and give Kudos to help others facing similar issue.

sab2
Regular Contributor
Regular Contributor

Thank you for the response. That had the same effect. The manager value is still being wiped out instead of left with current manager value.

Can you share the sample of your input file?


Regards,
Saathvik
If this reply answered your question, please Accept As Solution and give Kudos to help others facing similar issue.

Please share full preprocessor json


Regards,
Rushikesh Vartak
If you find this response useful, kindly consider selecting 'Accept As Solution' and clicking on the 'Kudos' button.

sab2
Regular Contributor
Regular Contributor

Thank you. I attached sample of the user input feed.

SAV:

#MODIFYUSERDATAJSON={"ADDITIONALTABLES": {"USERS": "SELECT lastname,systemUserName,firstname, username, CUSTOMPROPERTY31, secondaryManager, MANAGER, LOCATION, PREFEREDFIRSTNAME,employeeid FROM USERS"},"COMPUTEDCOLUMNS": ["firstname","username","secondaryManager", "MANAGER","employeeid"],"PREPROCESSQUERIES": ["UPDATE NEWUSERDATA N JOIN CURRENTUSERS U ON N.EMPLOYEEID=U.EMPLOYEEID SET N.USERNAME=U.USERNAME","UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.EMPLOYEEID = CURRENTUSERS.EMPLOYEEID SET NEWUSERDATA.MANAGER = case when CURRENTUSERS.MANAGER is not null then CURRENTUSERS.MANAGER WHEN (CURRENTUSERS.MANAGER IS NULL or CURRENTUSERS.MANAGER = '') THEN NEWUSERDATA.SECONDARYMANAGER end","UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.EMPLOYEEID = CURRENTUSERS.EMPLOYEEID SET NEWUSERDATA.FIRSTNAME = case when CURRENTUSERS.FIRSTNAME is not null and CURRENTUSERS.FIRSTNAME != '' then CURRENTUSERS.FIRSTNAME WHEN (CURRENTUSERS.FIRSTNAME IS NULL or CURRENTUSERS.FIRSTNAME = '') THEN NEWUSERDATA.FIRSTNAME end"]}
EMPLOYEEID,LASTNAME,FIRSTNAME,MIDDLENAME,CustomProperty29,CustomProperty30,CustomProperty9,CustomProperty8,CustomProperty38,SECONDARYMANAGER,CustomProperty3

mbinsale
Saviynt Employee
Saviynt Employee

Please try the below since you do not want the existing manager entry to modified if it already exists

UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.EMPLOYEEID = CURRENTUSERS.EMPLOYEEID SET NEWUSERDATA.MANAGER = case when (CURRENTUSERS.MANAGER IS NULL or CURRENTUSERS.MANAGER = '') THEN NEWUSERDATA.SECONDARYMANAGER end

sab2
Regular Contributor
Regular Contributor

Hi,

Thank you for the response. I tried this out in the SAV file and it did not work. The manager was still wiped out.

mbinsale
Saviynt Employee
Saviynt Employee

Could you try this please

UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.EMPLOYEEID = CURRENTUSERS.EMPLOYEEID SET NEWUSERDATA.MANAGER = case when (CURRENTUSERS.MANAGER IS NULL or CURRENTUSERS.MANAGER = '') THEN NEWUSERDATA.SECONDARYMANAGER else CURRENTUSERS.MANAGER end

sab2
Regular Contributor
Regular Contributor

Hi, Thank you. I tested this out as well and still no luck. The manager field continues to be wiped out.

mbinsale
Saviynt Employee
Saviynt Employee

This is verified to be working in my local database. Seems to me a data related issue causing this issue. Could you please try this in any other database. This is an extremely straightforward SQL 

timchengappa
Saviynt Employee
Saviynt Employee

Hi @sab2 

Please use the below PREPROCESSQUERIES...

"PREPROCESSQUERIES": ["UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.EMPLOYEEID = CURRENTUSERS.EMPLOYEEID SET NEWUSERDATA.OWNER = case when (CURRENTUSERS.OWNER IS NULL or CURRENTUSERS.OWNER = '') THEN NEWUSERDATA.SECONDARYMANAGER else CURRENTUSERS.OWNER end where NEWUSERDATA.OWNER IS NULL or NEWUSERDATA.OWNER = ''"]}

sab2
Regular Contributor
Regular Contributor

Thank you. This was tested however we are still experiencing the same result. With this query now the manager field and the owner field are being wiped out.

timchengappa
Saviynt Employee
Saviynt Employee

Hello @sab2 

Please use the attached files '.csv' & '.sav' files and let me know if it resolved your issues...