Manager Attribute Getting Blanked Out - Pre-processor Query

[aarthianand93]

Hi Team, 

We are trying to populate manager attribute based on the customdate. If the customdate is future dated it must retain the old manager value which is CU.MANAGER. However, when we perform a csv import through schema import via SAV File, it is blanking out the manager attribute. 

Query: UPDATE NEWUSERDATA NU
LEFT JOIN CURRENTUSERS CU ON NU.USERNAME = CU.USERNAME
LEFT JOIN (
SELECT USERNAME, STR_TO_DATE(CUSTOMPROPERTY13, '%m-%d-%Y') AS CustomDate
FROM NEWUSERDATA
) AS DV ON NU.USERNAME = DV.USERNAME
SET
NU.MANAGER = CASE
WHEN DV.CustomDate IS NOT NULL AND DV.CustomDate = CURDATE() THEN NU.MANAGER
WHEN DV.CustomDate < CURDATE() THEN NU.MANAGER

ELSE CU.MANAGER (failing condition)
END

Please assist on this. 

Thank you.

Regards,

Aarthi Anand

[rushikeshvartak]

UPDATE NEWUSERDATA NU
LEFT JOIN CURRENTUSERS CU ON NU.USERNAME = CU.USERNAME
LEFT JOIN (
SELECT USERNAME, STR_TO_DATE(CUSTOMPROPERTY13, '%m-%d-%Y') AS CustomDate
FROM NEWUSERDATA
) AS DV ON NU.USERNAME = DV.USERNAME
SET NU.MANAGER = CASE
WHEN DV.CustomDate IS NULL THEN NU.MANAGER
WHEN DV.CustomDate = CURDATE() THEN NU.MANAGER
WHEN DV.CustomDate < CURDATE() THEN NU.MANAGER
ELSE CU.MANAGER
END;

[aarthianand93]

Hi @rushikeshvartak,

I have tried this as well. However, the manager attribute is getting blanked out again. If it is NU.MANAGER then it is taking from the CSV during import. But if we mention CU.MANAGER it is blanking out. Attached the SAV File for you reference. 

Regards,

Aarthi Anand

[NM]

Hi @aarthianand93 , try setting owner instead of manager once.

UPDATE NEWUSERDATA NU

LEFT JOIN CURRENTUSERS CU ON NU.USERNAME = CU.USERNAME

LEFT JOIN (

SELECT USERNAME, STR_TO_DATE(CUSTOMPROPERTY13, '%m-%d-%Y') AS CustomDate

FROM NEWUSERDATA

) AS DV ON NU.USERNAME = DV.USERNAME

SET NU.owner = CASE

WHEN DV.CustomDate IS NULL THEN NU.owner

WHEN DV.CustomDate = CURDATE() THEN NU.owner

WHEN DV.CustomDate < CURDATE() THEN NU.owner

ELSE CU.owner

END;

[aarthianand93]

Hi @NM,

Thank you very much. This usecase is working. However, we are refraining from using owner. Is there any reason why this is not working for manager? Can we accommodate manager attribute here by making any other changes? Please let me know. 

Regards,

Aarthi Anand

[aarthianand93]

Hi @NM,

I just tested this out it is working for future date but past date is failing. So it has to work for past date, current date and future date in customdate attribute. Please check the SAV File I have provided for reference. Please let us know how this can be resolved.  Thank you. 

Regards,

Aarthi Anand

[NM]

Hi @aarthianand93 , what error do you see for past date??

Does it not update?

[aarthianand93]

Hi @NM,

It is making the existing manager value as blank when past date is imported in csv. 

expected result: 

if it is past date/currentdate in customdate it must make the manager value as new manager from CSV, 

if it is future date in customdate it must retain existing manager value and no change must be made.

Regards,

Aarthi

[rushikeshvartak]

UPDATE NEWUSERDATA NU
LEFT JOIN CURRENTUSERS CU ON NU.USERNAME = CU.USERNAME
LEFT JOIN (
SELECT USERNAME, STR_TO_DATE(CUSTOMPROPERTY13, '%m-%d-%Y') AS CustomDate
FROM NEWUSERDATA
) AS DV ON NU.USERNAME = DV.USERNAME
SET NU.owner = CASE
WHEN DV.CustomDate IS NULL THEN NU.owner
WHEN DV.CustomDate <= CURDATE() THEN CU.owner
ELSE NU.owner
END;

[aarthianand93]

Hi @rushikeshvartak,

I tried this above query. Again the manager is blanked out. 

Regards,

Aarthi Anand

[NM]

UPDATE NEWUSERDATA NU

LEFT JOIN CURRENTUSERS CU ON NU.USERNAME = CU.USERNAME

LEFT JOIN (

SELECT USERNAME, STR_TO_DATE(CUSTOMPROPERTY13, '%m-%d-%Y') AS CustomDate

FROM NEWUSERDATA

) AS DV ON NU.USERNAME = DV.USERNAME

SET NU.owner = CASE

WHEN DV.CustomDate IS NULL THEN (select nu1.username from NEWUSERDATA NU1 WHERE nu.manager=NU1.id )

ELSE CU.owner

END;

[NM]

@aarthianand93 can you just try the above query and provide me with the observation.

[aarthianand93]

Hi @NM, 

It is giving an error for the NU1.id in the WHERE clause. 

Thank you. 

Note: We have many queries in the SAV File for different attributes like title, departmentname, companyname etc. And for all other attributes the usecases are working correctly, except the manager attribute. 

Regards,

Aarthi Anand

[NM]

@aarthianand93 try 

UPDATE NEWUSERDATA NU

LEFT JOIN CURRENTUSERS CU ON NU.USERNAME = CU.USERNAME

LEFT JOIN (

SELECT USERNAME, STR_TO_DATE(CUSTOMPROPERTY13, '%m-%d-%Y') AS CustomDate

FROM NEWUSERDATA

) AS DV ON NU.USERNAME = DV.USERNAME

SET NU.owner = CASE

WHEN DV.CustomDate IS NULL THEN (select CU1.username from CURRENTUSERS CU1 WHERE nu.manager=CU1.id )

ELSE CU.owner

END;

[aarthianand93]

Hi @NM,

It is giving same error for the NU1.userkey in the WHERE clause. 

Regards,
Aarthi

[NM]

I updated the previous query try that. @aarthianand93 

[aarthianand93]

Hi @NM,

Same error for CU1.id.

Please let me know what is the expected result for this query. 

[NM]

@aarthianand93 instead of id try userkey ..

we are trying to get username of the new manager

and also, in computed column add userkey so it gets pulled into the query

[aarthianand93]

Hi @NM,

For your query, if we pass manager value with custom date as past date, current date, or future date all the updates are flowing through, which shouldn't happen.

[NM]

@aarthianand93 , yes because i remove some of the condition to check if it is working fine.

if it works .. we can add your original condition and just have the query in then.

[aarthianand93]

Hi @rushikeshvartak @NM, 

Please let us know if there is any way we can change the query for manager attribute instead of owner for future date. We have to know if this is feasible to respond to client.  Thank you. 

Regards,

Aarthi Anand

[aarthianand93]

Hi Team,

I see one other person faced same issue as mentioned in the forum post: https://forums.saviynt.com/t5/identity-governance/preprocessor-in-sav-file-manager-value-disappearin...

However, it is unresolved. I also tried those queries but still manager attribute is blanked out. 

Please keep me posted if you find any fix for this. Thank you. 

Regards,

Aarthi Anand

[NM]

@aarthianand93 is it working when you use owner field??

[aarthianand93]

Hi @NM, 

I just validated again but is not working it is blanking out the manager attribute.

Regards,

Aarthi Anand

[NM]

@aarthianand93 share updated value which you used recently.

[aarthianand93]

@NM,

Attached is the SAV File I have used. 

[rushikeshvartak]

Can you share logs 

[aarthianand93]

@rushikeshvartak,

Attached are the logs for your reference. 

[This message has been edited by moderator to mask sensitive information]

[rushikeshvartak]

There is SQL error. Not sure if its related to current issue. 

[aarthianand93]

Hi @rushikeshvartak,

We have tested same query for multiple attributes, every other attribute is working, except manager. I validated the SQL query many times. In the other forum post linked here as well they have used a similar query. Please help on the error so we can mitigate it. 

Thank you. 

Regards,

Aarthi Anand

[rushikeshvartak]

Convert Update query to select query and validate from data analyzer 

[aarthianand93]

@rushikeshvartak 

If we validate the SQL, it is found valid, but data analyzer is saying there is a syntax error. 

I made significant changes to the query to mitigate the error but it still persists. Please suggest how we change this query. Attached screenshots of the error. 

[NM]

@aarthianand93 data analyzer doesn't support update query change it to select query.

[aarthianand93]

@NM, 

If I give UPDATE it is giving ERROR: operation not allowed. So, I have given SELECT statement only. 

[rushikeshvartak]

• share data analyzer screenshot

[aarthianand93]

@rushikeshvartak 

Attached is the screenshot for your reference. 

[rushikeshvartak]

It will be users table and not currentusers/ new users 

[aarthianand93]

@rushikeshvartak,

I executed with USERS Table but still the error persists. Please let me know if there is any other changes I can make. If it is a product limitation please clarify on that as well. Screenshot attached. 

[rushikeshvartak]

NEWUSERS data is still there in query

[aarthianand93]

Hi @rushikeshvartak,

UPDATE NEWUSERDATA NU LEFT JOIN CURRENTUSERS - please let me know which should be updated to USERS. 

Also, we are using UPDATE NEWUSERDATA for other queries as well. And they are working fine. Below department query works fine when using NEWUSERDATA table. 

Why this manager query is failing specifically? Please let us know. 

Department Query:

NU.DEPARTMENTNAME=CASE WHEN DV.CustomDate IS NOT NULL AND DV.CustomDate=CURDATE() THEN NU.DEPARTMENTNAME WHEN DV.CustomDate<CURDATE() THEN NU.DEPARTMENTNAME ELSE CU.DEPARTMENTNAME END

[aarthianand93]

Hi Team,

We found this query to be a working one where the manager attribute is not wiped out. 

UPDATE NEWUSERDATA NU LEFT JOIN CURRENTUSERS CU ON NU.USERNAME=CU.USERNAME LEFT JOIN (SELECT USERNAME, STR_TO_DATE(CUSTOMPROPERTY13, '%m-%d-%Y') AS CustomDate FROM NEWUSERDATA) AS DV ON NU.USERNAME=DV.USERNAME SET NU.MANAGER=CASE WHEN DV.CustomDate IS NOT NULL AND DV.CustomDate=CURDATE() THEN NU.MANAGER WHEN DV.CustomDate<CURDATE() THEN NU.MANAGER WHEN DV.CustomDate>CURDATE() THEN CU.OWNER END

Thank you. 

Regards,

Aarthi Anand