Unable to update UserCustomproperty based on SAV4SAV REST connection

[KME]

Hi Team,

We have a requirement where we need to populate the Exception status for all users in one of the customproperty if the users are part of some AD Groups. I have created an analytics with the query to select Exception Status and trying to call the analytics in an UserImport JSON in SAV4SAV rest connection, but still the User CustomProperty is not getting populated.
=================================================================
Here is the Analytics Query I Used :
SELECT EV.ENTITLEMENT_VALUE,U.USERNAME, U.FIRSTNAME, U.LASTNAME, U.DEPARTMENTNAME AS 'DEPARTMENT', E.ENDPOINTNAME AS 'ENDPOINT', A.NAME AS 'ACCOUNTNAME',"LEAVER EXCEPTON USER" AS CUSTOMPROPERTY8 FROM ACCOUNTS A, USER_ACCOUNTS UA, USERS U, ENDPOINTS E, ENTITLEMENT_VALUES EV, ACCOUNT_ENTITLEMENTS1 AE1 WHERE A.ACCOUNTKEY = UA.ACCOUNTKEY AND U.USERKEY = UA.USERKEY AND A.ENDPOINTKEY = E.ENDPOINTKEY AND AE1.ACCOUNTKEY = A.ACCOUNTKEY AND AE1.ENTITLEMENT_VALUEKEY = EV.ENTITLEMENT_VALUEKEY AND A.STATUS ='2' AND U.STATUSKEY = 1 AND EV.ENTITLEMENT_VALUEKEY ='21'
=================================================================
Here is user import json I used:
{
"connection": "userAuth",
"url": "https://xxxxxxxxxxx.saviyntcloud.com/ECM/api/v5/FetchRuntimeControlsDataV2",
"httpMethod": "POST",
"httpContentType": "application/json",
"httpParams": "{\"analyticsid\":\"1528\"}",
"httpHeaders": {
"Authorization": "${access_token}",
"Accept": "application/json"
},
"colsToPropsMap": {
"username": "USERNAME~#~char",
"customproperty8": "CUSTOMPROPERTY8~#~char"
},
"userResponsePath": "results",
"pagination": {
"offset": {
"offsetParam": "offset",
"batchParam": "max",
"batchSize": 500,
"totalCountPath": "completeResponseMap.totalcount"
}
}
}
=====================================================
Could you please tell me, where is the issue as I am unable to troubleshoot it for a long time

[NM]

Hi @KME , could you share the response which you are getting from API.

[KME]

Hi @NM ,

Please find the response from API.

[NM]

@KME Can you share few more records, mask sensitive valu

[KME]

@NM -- Please find the below SS for reference:

[rushikeshvartak]

Try changing alias of columns in SQL query 

[NM]

Hi @KME , as per the postman screenshot there is no response from the endpoint that is why CP8 is not mapped.

[KME]

@rushikeshvartak Have provided alias for every column and tried - still no luck.

@NM - We are getting data while we run the report from UI but in postman , we are getting only count not with results.
Attaching the postman SS for reference:

Could you please share the snippet/solution how we can get the results

Thanks in advance!!

[rushikeshvartak]

Change offset to 0

[KME]

@rushikeshvartak  -- Post setting to 0 in postman, we are getting results.

Could you please help me if there is any changes to be made in userimport json to import customproperty8 into users table.

Here is user import json I used:
{
"connection": "userAuth",
"url": "https://xxxxxxxxxxx.saviyntcloud.com/ECM/api/v5/FetchRuntimeControlsDataV2",
"httpMethod": "POST",
"httpContentType": "application/json",
"httpParams": "{\"analyticsid\":\"1528\"}",
"httpHeaders": {
"Authorization": "${access_token}",
"Accept": "application/json"
},
"colsToPropsMap": {
"username": "USERNAME~#~char",
"customproperty8": "CUSTOMPROPERTY8~#~char"
},
"userResponsePath": "results",
"pagination": {
"offset": {
"offsetParam": "offset",
"batchParam": "max",
"batchSize": 500,
"totalCountPath": "completeResponseMap.totalcount"
}
}
}

Analytic Query:
SELECT EV.ENTITLEMENT_VALUE AS 'ENTITLEMENT',U.USERNAME AS 'USERNAME', U.FIRSTNAME AS 'FIRSTNAME', U.LASTNAME AS 'LASTNAME', U.DEPARTMENTNAME AS 'DEPARTMENT', E.ENDPOINTNAME AS 'ENDPOINT', A.NAME AS 'ACCOUNTNAME',"LEAVER EXCEPTON USER" AS 'CUSTOMPROPERTY8' FROM ACCOUNTS A, USER_ACCOUNTS UA, USERS U, ENDPOINTS E, ENTITLEMENT_VALUES EV, ACCOUNT_ENTITLEMENTS1 AE1 WHERE A.ACCOUNTKEY = UA.ACCOUNTKEY AND U.USERKEY = UA.USERKEY AND A.ENDPOINTKEY = E.ENDPOINTKEY AND AE1.ACCOUNTKEY = A.ACCOUNTKEY AND AE1.ENTITLEMENT_VALUEKEY = EV.ENTITLEMENT_VALUEKEY AND A.STATUS ='2' AND U.STATUSKEY = 1 AND EV.ENTITLEMENT_VALUEKEY = 21

[rushikeshvartak]

{
"connection": "userAuth",
"url": "https://xxxxxxxxxxx.saviyntcloud.com/ECM/api/v5/FetchRuntimeControlsDataV2",
"httpMethod": "POST",
"httpContentType": "application/json",
"httpParams": "{\"analyticsid\":\"1528\"}",
"httpHeaders": {
"Authorization": "${access_token}",
"Accept": "application/json"
},
"colsToPropsMap": {
"username": "USER_USERNAME~#~char",
"customproperty8": "ACCOUNT_CUSTOMPROPERTY8~#~char"
},
"userResponsePath": "results",
"pagination": {
"offset": {
"offsetParam": "offset",
"batchParam": "max",
"batchSize": 500,
"totalCountPath": "completeResponseMap.totalcount"
}
}
}

Analytic Query:
SELECT EV.ENTITLEMENT_VALUE AS 'ENTITLEMENT',U.USERNAME AS 'USER_USERNAME', U.FIRSTNAME AS 'FIRSTNAME', U.LASTNAME AS 'LASTNAME', U.DEPARTMENTNAME AS 'DEPARTMENT', E.ENDPOINTNAME AS 'ENDPOINT', A.NAME AS 'ACCOUNTNAME',"LEAVER EXCEPTON USER" AS 'ACCOUNT_CUSTOMPROPERTY8' FROM ACCOUNTS A, USER_ACCOUNTS UA, USERS U, ENDPOINTS E, ENTITLEMENT_VALUES EV, ACCOUNT_ENTITLEMENTS1 AE1 WHERE A.ACCOUNTKEY = UA.ACCOUNTKEY AND U.USERKEY = UA.USERKEY AND A.ENDPOINTKEY = E.ENDPOINTKEY AND AE1.ACCOUNTKEY = A.ACCOUNTKEY AND AE1.ENTITLEMENT_VALUEKEY = EV.ENTITLEMENT_VALUEKEY AND A.STATUS ='2' AND U.STATUSKEY = 1 AND EV.ENTITLEMENT_VALUEKEY = 21

Validate your report is runtime report ?

[gauravchandok]

Hi Rushikesh,

Do we have any documentation which explains this update saviynt user via importuserjson of sav4sav?

If we have multiple anayltics to update multiple Customproperty do we need to have multiple connections or multiple calls like https://forums.saviynt.com/t5/identity-governance/updateuserjson-rest-update-user-attributes-based-o...

Thanks

[rushikeshvartak]

Multiple calls

[KME · Accepted solution]

Thanks for all the inputs. Issue is resolved. The Actual issue was that there was incorrect details  in connection Json. Later got fixed.