I am trying to import users for SAV4SAV REST endpoint using a REST connection. My import user json is as follows:
{
"call": [{
"connection": "acctAuth",
"url": "https://access-qa.<<companyname>>.com/ECM/api/v5/user?q=accountExpired:0&fields=username,customproperty18&sort=username&order=desc&offset=0&max=500",
"httpMethod": "GET",
"httpContentType": "application/json",
"httpHeaders": {
"Authorization": "${access_token}"
},
"colsToPropsMap": {
"username": "username~#~char",
"customproperty18": "customproperty18~#~char"
}
}]
}
As part of the user import, I have preprocessor configured to get a list of users who have an account on another endpoint and matches CP18. These are the users for whom CP18 needs to be cleared during import.
{
"ADDITIONALTABLES": {
"USERS": "SELECT U.USERNAME, IFNULL(U.CUSTOMPROPERTY18,'') FROM USERS U INNER JOIN USER_ACCOUNTS UA ON U.USERKEY = UA.USERKEY INNER JOIN ACCOUNTS A ON UA.ACCOUNTKEY = A.ACCOUNTKEY WHERE A.ENDPOINTKEY IN (SELECT ENDPOINTKEY FROM ENDPOINTS WHERE ENDPOINTNAME = 'UCADMIN') AND A.STATUS NOT IN ('SUSPENDED FROM IMPORT SERVICE') AND U.CUSTOMPROPERTY18 = A.NAME AND U.USERNAME IN (SELECT NAME FROM ACCOUNTS WHERE ENDPOINTKEY IN (SELECT ENDPOINTKEY FROM ENDPOINTS WHERE ENDPOINTNAME = 'UCADMIN-PRE-AUTHORIZATION') AND STATUS NOT IN ('SUSPENDED FROM IMPORT SERVICE'))"
},
"COMPUTEDCOLUMNS": ["CUSTOMPROPERTY18"],
"TABLEINDEXES": {
"CURRENTUSERS": [
"USERNAME"
],
"NEWUSERDATA": [
"USERNAME"
]
},
"PREPROCESSQUERIES": [
"DELETE NU FROM NEWUSERDATA NU WHERE NU.CUSTOMPROPERTY18 IS NULL OR NU.CUSTOMPROPERTY18 = ''",
"UPDATE NEWUSERDATA NU INNER JOIN CURRENTUSERS CU ON NU.USERNAME = CU.USERNAME SET NU.CUSTOMPROPERTY18 = ''"
]
}
However, the job ends with the below error:
The logs show the below error:
Any inputs on what could be wrong with the configurations?
Does every user have CP18 set. when user's customproperty18 is null API does not return column causing error.
