sav4Sav(REST) connector and preprocessor

[tpawle]

Hello Team,

We are trying to achieve one use case via the sav4sav(REST) connector to update user custom properties to some values which inturn would trigger some update rules for specific actions to be performed. 

Import JSON being used to update user custom properties via SQL reports which return JSON data for the desired users:

{
"connection": "userAuth",
"successResponses": {
"statusCode": [
200,
201,
202,
203,
204,
205
]
},
"url": "https://removed.saviyntcloud.com/ECM/api/v5/fetchControlDetailsES ",
"httpParams": "{\"analyticsname\":\"SaviyntToSaviynt-Update User cp41 42 43\"}",
"httpMethod": "GETWITHBODY",
"httpHeaders": {
"Authorization": "${access_token}"
},
"httpContentType": "application/json",
"userResponsePath": "result",
"colsToPropsMap": {
"username": "userName~#~char",
"customproperty40": "customproperty40~#~char",
"customproperty41": "customproperty41~#~char",
"customproperty42": "customproperty42~#~char",
"customproperty43": "customproperty43~#~char"
},
"errorCode": "400",
"errorCodePath": "errorCode"
}

Question1: Can we use preprocessor in the sav4sav(REST) connection to achieve the same? if yes, what modifications are required ?

Question2: if this can be achieved all alone in preprocessor, please let us know how can this be achieved? Below is the preprocessor query being used in the same sav4sav(REST) connection.

{
"ADDITIONALTABLES": {
"USERS": "SELECT USERKEY,username,firstname,lastname,enddate,customproperty1,customproperty2,customproperty12,customproperty19 FROM USERS"
},
"COMPUTEDCOLUMNS": [
"customproperty1",
"customproperty2",
"customproperty12",
"customproperty19"
],
"PREPROCESSQUERIES": [
"Update NEWUSERDATA Set NEWUSERDATA.customproperty1 = FN_EIC_REPLACE(NEWUSERDATA.firstname,'REPLACE_DATASET_MAPPING_TEST')",
"Update NEWUSERDATA Set NEWSUSERDATA.customproperty2 = FN_EIC_REPLACE(NEWUSERDATA.lastname,'REPLACE_DATASET_MAPPING_TEST')",
"Update NEWUSERDATA Set NEWSUSERDATA.customproperty41 = 'True' where DATEDIFF(NEWUSERDATA.ENDDATE, DATE_ADD(NOW(), INTERVAL 15 DAY))=0",
"Update NEWUSERDATA Set NEWSUSERDATA.customproperty42 = 'True' where DATEDIFF(NEWUSERDATA.ENDDATE, DATE_ADD(NOW(), INTERVAL 5 DAY))=0",
"Update NEWUSERDATA Set NEWSUSERDATA.customproperty43 = 'True' where DATEDIFF(NEWUSERDATA.ENDDATE, DATE_ADD(NOW(), INTERVAL 0 DAY))=0"
]
}

Please respond back if you need more details on the use case.

[This message has been edited by moderator to mask url]

[nimitdave]

@tpawle , yes preprocessor can be used with a sav4sav import. But make sure the analytics report is having optimized query that fetched only the desired data and then have a preprocessor as per your need.

Also you can use it only with preprocessor if you have all the data to drive the computed columns value logic as per the business requirement.

[tpawle]

Hi @nimitdave ,

Thanks for clarifying. I am using the following preprocessor in the same Sav4Sav(REST) connection to replace characters from the firstname and lastname along with populating the Cp41,42 and 43 for the terminations happening in future 15,5 and 0 Day before actual termination to trigger emails via update rules. Below is the ModifyUserJSON:

{
"COMPUTEDCOLUMNS": [
"customproperty1",
"customproperty2"
],
"PREPROCESSQUERIES": [
"Update NEWUSERDATA Set NEWUSERDATA.customproperty1 = FN_EIC_REPLACE(NEWUSERDATA.firstname,'REPLACE_DATASET_MAPPING_TEST')",
"Update NEWUSERDATA Set NEWSUSERDATA.customproperty2 = FN_EIC_REPLACE(NEWUSERDATA.lastname,'REPLACE_DATASET_MAPPING_TEST')",
"Update NEWUSERDATA Set NEWSUSERDATA.customproperty41 = 'True' where DATEDIFF(NEWUSERDATA.enddate, DATE_ADD(NOW(), INTERVAL 15 DAY))=0",
"Update NEWUSERDATA Set NEWSUSERDATA.customproperty42 = 'True' where DATEDIFF(NEWUSERDATA.enddate, DATE_ADD(NOW(), INTERVAL 5 DAY))=0",
"Update NEWUSERDATA Set NEWSUSERDATA.customproperty43 = 'True' where DATEDIFF(NEWUSERDATA.enddate, DATE_ADD(NOW(), INTERVAL 0 DAY))=0"
]
}

While running the job, the import job is failing provided below is the import user json:

{
"connection": "userAuth",
"successResponses": {
"statusCode": [
200,
201,
202,
203,
204,
205
]
},
"url": "https://<removed>.saviyntcloud.com/ECM/api/v5/fetchControlDetailsES ",
"httpParams": "{\"analyticsname\":\"SaviyntToSaviynt- FirstName_LastName_EndDate\"}",
"httpMethod": "GETWITHBODY",
"httpHeaders": {
"Authorization": "${access_token}"
},
"httpContentType": "application/json",
"userResponsePath": "result",
"colsToPropsMap": {
"username": "userName~#~char",
"enddate": "enddate~#~char",
"firstname": "firstname~#~char",
"lastname": "lastname~#~char"
},
"errorCode": "400",
"errorCodePath": "errorCode"
}

and below is the query in the analytics:

SELECT userRecord.username,userRecord.firstname,userRecord.lastname, userRecord.enddate from users userRecord

Could you please advice if i am missing something.

Thanks,

Tejas

[This message has been edited by moderator to mask URL]