Announcing the SAVIYNT KNOWLEDGE EXCHANGE unifying the Saviynt forums, documentation, training, and more in a single search tool across platforms. Click HERE to read the Announcement.

Capture timestamp when a Saviynt user is inactivated from user import

krecpond
New Contributor III
New Contributor III

I have a requirement to store the timestamp on CP59 when Saviynt users get inactivated from an HR import process. I am trying to accomplish this using the MODIFYUSERDATA JSON as part of the UI upload.

I have the below MODIFYUSERDATAJSON as part of the file import via UI:

{
"ADDITIONALTABLES": {
"USERS": "SELECT USERNAME, CUSTOMPROPERTY59, STATUSKEY FROM USERS"
},
"COMPUTEDCOLUMNS": [
"CUSTOMPROPERTY59"
],
"TABLEINDEXES": {
"CURRENTUSERS": [
"USERNAME"
]
},
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA NU INNER JOIN CURRENTUSERS CU ON NU.USERNAME = CU.USERNAME SET NU.CUSTOMPROPERTY59 = NOW() WHERE CU.STATUSKEY=1 AND NU.STATUSKEY=0"
]
}

Sample test data is as follows:

krecpond_0-1665201603199.png

I am expecting CP59 of EVIJ00001 to be updated with timestamp based on the logic and my understanding of the temporary tables - NEWUSERDATA and CURRENTUSERS and this works as expected.

The next time I import, EVIJ00092 is uploaded with statuskey=0 and EVIJ00001 remains statuskey=0. But this time when the user file is imported via the UI, the inline preprocessor is wiping out the timestamp from CP59 on EVIJ00001 and updating the CP59 with current timestamp for EVIJ00092.

Can someone please tell me what is incorrect in the inline preprocessor logic?

 

1 REPLY 1

puneetkhullar
Saviynt Employee
Saviynt Employee

Hi @krecpond 

Please use the following example that will help you to resolve this issue:

 

"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON CURRENTUSERS.USERNAME = NEWUSERDATA.USERNAME SET NEWUSERDATA.CUSTOMPROPERTY59 = CASE WHEN ((NEWUSERDATA.STATUSKEY = '0' AND (CURRENTUSERS.STATUSKEY = '1' AND CURRENTUSERS.STATUSKEY IS NOT NULL))) THEN NOW() WHEN (NEWUSERDATA.STATUSKEY = '0' AND CURRENTUSERS.STATUSKEY = '0') THEN CURRENTUSERS.CUSTOMPROPERTY59 END"