modifyuser json query issue

[sampath18]

Hi All,

I have requirement to clear an attribute value of user automatically when certain condition is met.

So i wanted to user importuserjson in sav4sav connection and remove  cp28 attribute value when a condition is met. So wrote following preprocessor query. But when the query is executed by the job, it is removing the cp28 attribute even though condition is not true.  can anyone tell me where it is going wrong.

{
"ADDITIONALTABLES": {
"USERS": "select username, userkey,customproperty28 from users where username like 'BAN-%'",
"ACCOUNTS": "select name,status,accountkey,endpointkey,accountid from accounts where endpointkey ='1'",
"USER_ACCOUNTS": "select ua.userkey, ua.accountkey from USER_ACCOUNTS ua inner join ACCOUNTS a on ua.accountkey = a.ACCOUNTKEY where a.endpointkey=1"
},
"COMPUTEDCOLUMNS" : ["customproperty28"],
"PREPROCESSQUERIES":
[
"UPDATE NEWUSERDATA nu left join CURRENTUSERS cu on nu.username = cu.username inner join currentuser_accounts ua on cu.userkey = ua.userkey inner join currentaccounts a on ua.accountkey = a.accountkey set nu.customproperty28 = '' where a.endpointkey ='1' and a.name = cu.customproperty28 and a.status <> '1'"
]
} 

[DixshantValecha]

Hi @sampath18,

We are checking on your request and we will keep you posted.

[rushikeshvartak]

UPDATE NEWUSERDATA nu
LEFT JOIN CURRENTUSERS cu ON nu.username = cu.username
INNER JOIN CURRENTUSER_ACCOUNTS ua ON cu.userkey = ua.userkey
INNER JOIN CURRENTACCOUNTS a ON ua.accountkey = a.accountkey
SET nu.customproperty28 = ''
WHERE a.endpointkey = '1' AND (a.name IS NULL OR a.name = cu.customproperty28) AND a.status <> '1'

[sampath18]

Hi Rishi,

Even with above query the result is same. I am not getting thing that why it is clearing cp28 value of user profile even though condition is not met. Also a.name is null is what i see difference in the modified query and rest all looks same.

Thanks
Sampath

[rushikeshvartak]

share current json and avoid using like keyword

[DixshantValecha]

Hi @sampath18,

Kindly share the json for further triage as mentioned by @rushikeshvartak .

[sampath18]

Hi @rushikeshvartak  & @DixshantValecha  I have already shared above the pre-processor JSON.

Here i am sharing the JSON again.

Modify user json

{
"ADDITIONALTABLES": {
"USERS": "select username, userkey,customproperty28 from users where username like 'BAN-%'",
"ACCOUNTS": "select name,status,accountkey,endpointkey,accountid from accounts where endpointkey ='1'",
"USER_ACCOUNTS": "select ua.userkey, ua.accountkey from USER_ACCOUNTS ua inner join ACCOUNTS a on ua.accountkey = a.ACCOUNTKEY where a.endpointkey=1"
},
"COMPUTEDCOLUMNS" : ["customproperty28"],
"PREPROCESSQUERIES":
[
"UPDATE NEWUSERDATA nu left join CURRENTUSERS cu on nu.username = cu.username inner join currentuser_accounts ua on cu.userkey = ua.userkey inner join currentaccounts a on ua.accountkey = a.accountkey set nu.customproperty28 = '' where a.endpointkey ='1' and a.name = cu.customproperty28 and a.status <> '1'"
]
}

Import User json from sav4sav connetcion

{
"connection": "userAuth",
"url": "https://b*********-dev-support.saviyntcloud.com/ECM/api/v5/getUser",
"httpMethod": "POST",
"httpHeaders": {
"Authorization": "${access_token}",
"contentType": "application/json"
},
"httpParams": "{\"filtercriteria\":{\"username\" :\"BAN-%\"},\"responsefields\":[\"username\",\"customproperty28\"],\"max\":\"1000\"}",
"httpContentType": "application/json",
"colsToPropsMap": {
"username": "username~#~char"
},
"userResponsePath": "userlist",
"pagination": {
"offset": {
"offsetParam": "offset",
"batchParam": "max",
"batchSize": 1000,
"totalCountPath": "completeResponseMap.total"
}
}
}

Thanks
Sampath

[DixshantValecha]

Hi @sampath18,

It appears that you’re encountering an issue with your preprocessor query for the importuserjson in the sav4sav connection. 

Your preprocessor query is designed to clear the cp28 attribute value for users when a specific condition is met. However, you’ve noticed that even when the condition is not true, the attribute is still being removed.

Now, let’s troubleshoot the issue:

1. Check the Condition: Verify that the condition in your query is correctly evaluating whether the attribute should be cleared. Double-check the logic and ensure it aligns with your requirements.
2. Data Inspection: Inspect the data in your tables. Are there any unexpected values or discrepancies that might cause the condition to evaluate differently than expected?
3. Logging and Debugging: Could you please share the logs(error logs snapshot),For the same.

[ReshamDas]

@sampath18 even we are facing similar issue with Sav4Sav while using any custom attribute in the condition, in this forum post. Did you find any solution?

[angela]

Hi, I am also experiencing this issue. Has a solution been located?