CUSTOMPROPERTY converting to null from Sav4Sav REST Connection

[ReshamDas]

Hi,

We are currently implementing user termination flow (eg.:- Disable user identity on termination date, Move organization value to archive after n days from termination date). To implement these use cases, we use a Saviynt-For-Saviynt (Sav4Sav) REST connection. The connection details with JSONs for IMPORTUSERJSON and MODIFYUSERDATAJSON, and the job configuration details for the same have been attached to this forum post (PFA Leaver Sav4Sav Expected Code.txt).

We have a use case where we need to check if the employment status of a certain user is 'Terminated' or not, as only 'Terminated' user needs to be disabled via the Sav4Sav connection. This employment status is stored as CUSTOMPROPERTY4 in our environment.

Now we are facing an issue that when we use this filter customproperty4='Terminated' inside the MODIFYUSERDATAJSON PREPROCESSQUERIES section along with the customproperty4 mapping (i.e. "customproperty4": "customproperty4~#~char") inside the IMPORTUSERJSON colsToPropsMap section, after running the job, we see that the existing customproperty4 value is nullified. This is causing impact - 1) customproperty4 (Employment Status) value for all users under the purview of IMPORTUSERJSON httpParams userQuery filter gets vanished, 2) any further logic in the MODIFYUSERDATAJSON PREPROCESSQUERIES section that involves the existing customproperty4 value is impacted. Snapshots have been provided in this reference on the attached Honda Sav4Sav Issue.pdf file.

During internal troubleshooting, it was found that the same issue occurs if we use any other customproperty attribute (eg.: customproperty5) in place of customproperty4 in the MODIFYUSERDATAJSON PREPROCESSQUERIES section. However, it was also found that if we use a default Saviynt attribute (eg.:- street) instead of any customproperty, with the same values ('Terminated') and logics implemented in the MODIFYUSERDATAJSON PREPROCESSQUERIES section, the street value does not vanish/nullify, and hence the required result can be attained. PFA Leaver Sav4Sav Working Code.txt that implements the same requirement successfully with Employment Status value mapped to the street attribute.

However, since we cannot put the employment status value into street or any other default attribute as per the business design, and it can only be implemented in the customproperty4, please suggest a solution to this problem.

[Saathvik]

@ReshamDas : Please use responsefields param to define what attributes you want as output. If you don't use this then response will have only mandatory fields(username, email, statuskey, firstname, lastname, employeeid) and other default attributes whose values are not null.

So use responsefields param and define all attributes you want as response and see if that fixes your issue

[ReshamDas]

Hi @Saathvik,

Would you please share a sample code with the responsefields param used?

Also, we are expecting statuskey field on the output, which is a default field and not a custom property. The customproperty4 that we are using is required for the MODIFYUSERDATAJSON PREPROCESSQUERIES query filter.

[Saathvik]

@ReshamDas : 

You need to pass below format in httpparams section along with filter criteria

"responsefields":["username","statuskey","firstname"]

In your columns to props map you are mapping CP4 , Which is not coming in you API response hence it is replacing the CP4 values with NULL

	"colsToPropsMap": {
		"username": "username~#~char",
		"statuskey": "statuskey~#~bigint",
		"termDate": "termDate~#~date",
		"employeeType": "employeeType~#~char",
		"street": "street~#~char"
	}

[ReshamDas]

Hi @Saathvik,

Thank you for your response.

As per your recommendation, we added the CUSTOMPROPERTY4 attribute in responsefields param within the httpparams section in importuserjson as follows:-

{
"connection": "userAuth",
"url": "https://sample-dev.saviyntcloud.com/ECM/api/v5/getUser",
"httpMethod": "POST",
"httpHeaders": {
"contentType": "application/x-www-form-urlencoded",
"Authorization": "${access_token}"
},
"httpParams": {
"userQuery": "username in ('REST_Test_30','REST_Test_31','REST_Test_32','REST_Test_34','REST_Test_35','REST_Test_37','REST_Test_38','REST_Test_39') AND employeeType IN ('Associate','Contractor','Consultant','Student','Business Partner','Supplier','Non-Human')",
"responsefields":["username","statuskey","customer","customproperty4","employeeType","endDate"]
},
"colsToPropsMap": {
"username": "username~#~char",
"statuskey": "statuskey~#~bigint",
"endDate": "endDate~#~datetime",
"employeeType": "employeeType~#~char",
"customer": "customer~#~bigint",
"customproperty4": "customproperty4~#~char"
},
"errorCode": "400",
"errorCodePath": "errorCode",
"userResponsePath": "userdetails",
"pagination": {
"offset": {
"offsetParam": "offset",
"batchParam": "max",
"batchSize": 500,
"totalCountPath": 99999
}
}
}

Here's the modifyuserdatajson:-

{

"ADDITIONALTABLES": {

"USERS": "SELECT USERKEY, USERNAME, CUSTOMER, STATUSKEY, EMPLOYEETYPE, ENDDATE, CUSTOMPROPERTY4 FROM USERS"

},

"TABLEINDEXES": {

"CURRENTUSERS": [

"USERNAME"

]

},

"COMPUTEDCOLUMNS": [

"statuskey",

"customer"

],

"PREPROCESSQUERIES": [

"UPDATE NEWUSERDATA SET statuskey = CASE WHEN NEWUSERDATA.endDate IS NOT NULL and NEWUSERDATA.endDate <= now() and NEWUSERDATA.employeeType in ('Associate','Contractor','Consultant','Student') and NEWUSERDATA.customproperty4 = 'Terminated' and NEWUSERDATA.statuskey <> 0 THEN 0 WHEN NEWUSERDATA.endDate IS NOT NULL and NEWUSERDATA.endDate <= now() and NEWUSERDATA.employeeType in ('Business Partner','Supplier','Non-Human') and NEWUSERDATA.statuskey <> 0 THEN 0 ELSE NEWUSERDATA.statuskey END",

"UPDATE NEWUSERDATA SET customer = CASE WHEN NEWUSERDATA.employeeType in ('Associate','Contractor','Consultant','Student') and NEWUSERDATA.customproperty4 = 'Terminated' and NEWUSERDATA.statuskey = 0 and Date_add(NEWUSERDATA.endDate, INTERVAL 60 day) <= now() THEN 'Sample-Archive-Workforce' WHEN NEWUSERDATA.employeeType in ('Business Partner') and Date_add(NEWUSERDATA.endDate, INTERVAL 60 day) <= now() THEN 'Sample-Archive-BusinessPartner' WHEN NEWUSERDATA.employeeType in ('Supplier') and NEWUSERDATA.customproperty4 = 'Terminated' and NEWUSERDATA.statuskey = 0 and Date_add(NEWUSERDATA.endDate, INTERVAL 60 day) <= now() THEN 'Sample-Archive-Supplier' WHEN NEWUSERDATA.employeeType in ('Non-Human') and Date_add(NEWUSERDATA.endDate, INTERVAL 60 day) <= now() THEN 'Sample-Archive-NonHuman' ELSE NEWUSERDATA.customer END"

]

}

However, after running this connection through userimport job, we found that the import job ends up in 'Success' status however inside the job log, it returns the following information:-

Success

false

Records Missing Username

[null, 1, null, Associate, Sample-NA-Workforce, null],[null, 0, null, Business Partner, Sample-NA-BusinessPartner, null],[null, 0, null, Supplier, Sample-NA-Supplier, null],[null, 0, null, Non-Human, Sample-NA-NonHuman, null],[null, 0, null, Associate, Sample-NA-Workforce, null],[null, 0, null, Business Partner, Sample-Archive-BusinessPartner, null],[null, 0, null, Supplier, Sample-NA-Supplier, null],[null, 0, null, Non-Human, Sample-Archive-NonHuman, null]

Please find attached screenshot of the job log, and logs extracted during the job run.

Kindly suggest.

[ReshamDas]

Hi @Saathvik 

Would you kindly look into this matter and suggest?

[This message has been edited by a moderator to remove @ mention to other forum member who is not yet part of this conversation]

[Saathvik]

@ReshamDas : Looking at your job log error it looks like you are getting null values for username, customer and enddate. Please look into your data and run again. Also please try to run the same API in POSTMAN and check if you are getting expected output

[ReshamDas]

Hi @Saathvik,

The data looks fine, as all the test users for this POC have their username, customer and enddate populated correctly. As I mentioned, the Sav4Sav connection is working absolutely fine when we use a default attribute (eg. street) to store the employment status value 'Terminated' in the condition that also includes username, customer and enddate in the condition to evaludate statuskey and customer attribute values in the modifyuserdatajson.

However, whenever we use the actual custom attribute customproperty4 to store the employment status value 'Terminated', and run the SAV4SAV connection user import job, it blanks out the customproperty4 attribute itself, jeopardizing the later calculations for statuskey and customer attributes!

PFA a simplified version of the code, that is supposed to evaluate the statuskey, customer attribute values for user REST_Test_30 based on enddate, employeetype, customproperty4 (not working, however working perfectly on the other attached code when default attribute street is used instead), current statuskey attribute values.

In this regard, I have come across a similar forum post that also states that a customproperty getting blanked out from a Sav4Sav connection. Kindly look into the matter urgently as the JML implementation is our environment is stuck due to this issue.

[rushikeshvartak]

use leaveStatus column

[ReshamDas]

Hi @rushikeshvartak ,

Use of leaveStatus column is not possible as per the design, the relevant data requires to be held in customProperty4 instead.

[rushikeshvartak]

Since cp4 is not working you need to change design

[ReshamDas · Accepted solution]

With help of Saviynt Engineer Prateek Rohela, we were able to get a solution to this matter:-

1. The custom attribute values are not coming from the NEWUSERDATA auth source in this particular case, hence we had to join the CURRENTUSERS table: UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.USERNAME = CURRENTUSERS.USERNAME; thereafter, call the CUSTOMPROPERTY4 from CURRENTUSERS tempTable in the MODIFYUSERDATAJSON preprocessor query.
2. After above change, this time the logic was evaluating fine based on the CUSTOMPROPERTY4 value, updating the STATUSKEY and CUSTOMER attributes as expected. Still, we saw that the CUSTOMPROPERTY4 value was getting nullified.
3. Hence, we added one more line in the preprocessor query to update the CUSTOMPROPERTY4 of the user based on CUSTOMPROPERTY4 from CURRENTUSERS tempTable:- UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.USERNAME = CURRENTUSERS.USERNAME SET NEWUSERDATA.CUSTOMPROPERTY4 = CURRENTUSERS.CUSTOMPROPERTY4; on this time, both the logic for CUSTOMER and STATUSKEY was evaluated correctly, and CUSTOMPROPERTY4 also did not disappear.

PFA the updated MODIFYUSERDATAJSON, which is working fine.