How to store the Add access Rest API Response in User Accounts in Saviynt

[Prashant16]

Hi Team,

We need to fetch the response from the "Add Access" REST API and store a specific attribute from this response into a custom property in the Saviynt user account tab.

Could you please assist us with the process to achieve this?

Thank you!

Json 

{
"responseColsToPropsMap": {
"customproperty2": "call.message.user~#~char"
},
"call": [
{
"name": "properties",
"connection": "userAuth",
"url": "https://analyticsadmin.googleapis.com/v1alpha/${entitlementValue.customproperty8}/accessBindings",
"httpMethod": "POST",
"httpParams": "{\"user\":\"${user.email}\",\"roles\":[\"predefinedRoles/${entitlementValue.customproperty11}\"]}",
"httpHeaders": {
"Authorization": "${access_token}"
},
"httpContentType": "application/json",
"successResponses": {
"statusCode": [
202,
200
],
"status": [
"success"
]
}
}
]
}

response

   "name": "properties/2634/accessBindings/AYVX2e6dhXGFw==",

            "user": "pbpppbbgss.eppbbgss22@xyz.com",

            "roles": [

                "predefinedRoles/viewer"

            ]

        },

[stalluri]

@Prashant16   It is not supported. 

Workaround: Use enhanced query and update the value present in the task provisioning comments, trim it, and update the value.

Give this below a try.

Spoiler

{
"accountIdPath": "accountName",
"responseColsToPropsMap": {
"customproperty2": "call.message.user~#~char"
},
"call": [
{
"name": "properties",
"connection": "userAuth",
"url": "https://analyticsadmin.googleapis.com/v1alpha/${entitlementValue.customproperty8}/accessBindings",
"httpMethod": "POST",
"httpParams": "{\"user\":\"${user.email}\",\"roles\":[\"predefinedRoles/${entitlementValue.customproperty11}\"]}",
"httpHeaders": {
"Authorization": "${access_token}"
},
"httpContentType": "application/json",
"successResponses": {
"statusCode": [
202,
200
],
"status": [
"success"
]
}
}
]
}

[rushikeshvartak]

What is current output ? vs what is expected here ?

[Prashant16]

Hi @rushikeshvartak ,

We need to fetch the response from the "Add Access" REST API and store a specific attribute from this response into a custom property in the Saviynt user account tab.at present we are just adding access .

[rushikeshvartak]

Response  columns mapping is only supported in create account json

[Prashant16]

HI @stalluri ,
Can you please help with sample enhanced query  if you have handy ?

[stalluri]

@Prashant16 

SELECT 
 SUBSTRING(
        t.PROVISIONINGCOMMENTS,
        LOCATE('"statusCode":', t.PROVISIONINGCOMMENTS) + 13,
        LOCATE(',', t.PROVISIONINGCOMMENTS, LOCATE('"statusCode":', t.PROVISIONINGCOMMENTS) + 13) 
        - (LOCATE('"statusCode":', t.PROVISIONINGCOMMENTS) + 13)
    ) AS accounts__CustomProperty2,
a.accountkey AS accounts__primarykey
FROM accounts a
JOIN arstasks t ON a.ACCOUNTKEY = t.ACCOUNTKEY
WHERE  t.taskkey = 15555;     //DATE(t.UPADTEUSER) = CURDATE();

Value in MY PROVISIONINGCOMMENTS =({"Role":{"headers":null,"message":{"boolean":{"xmlns":"http://temp.org/","content":"false"}},"statusCode":200,"description":null,"status":"Failed"}}) 

Here’s how it breaks down:

• "statusCode": is exactly 12 characters long.
• Adding 1 to account for the space or directly for the start of the numeric value.

So, LOCATE('"statusCode":', t.PROVISIONINGCOMMENTS) + 13 ensures that the substring extraction starts just after the statusCode key and the colon, right at the numeric value (e.g., 200).

[stalluri]

@Prashant16 
Try mapping it with import if the data is present in the target. 

If not, try this query.

SELECT SUBSTRING( t.PROVISIONINGCOMMENTS, LOCATE('"user":', t.PROVISIONINGCOMMENTS) + 7, LOCATE(',', t.PROVISIONINGCOMMENTS, LOCATE('"user":', t.PROVISIONINGCOMMENTS) + 7) - (LOCATE('"user":', t.PROVISIONINGCOMMENTS) + 7) ) AS accounts__CustomProperty2, a.accountkey AS accounts__primarykey FROM accounts a JOIN arstasks t ON a.ACCOUNTKEY = t.ACCOUNTKEY
WHERE t.status IN (4, 3)
AND t.endpoint = '<endpoinkey>'
AND t.PROVISIONINGCOMMENTS LIKE '%properties%'
AND DATE(t.UPADTEUSER) = CURDATE();

[Prashant16]

Hi @rushikeshvartak and @stalluri ,
I am using below query to get the data can you please let me know if we can use substring in the Enhanced Query
SELECT
u.username AS 'USERNAME',
u.firstname AS 'FIRSTNAME',
SUBSTRING_INDEX(
SUBSTRING_INDEX(
ART.PROVISIONINGCOMMENTS,
'/accessBindings/',
-1
),
'"',
1
) AS 'ACCESS_BINDING_ID'
FROM
ARSTASKS ART
JOIN USERS u ON ART.userkey = u.userkey
JOIN USERS u1 ON u.manager = u1.userkey
JOIN ENDPOINTS ep ON ART.endpoint = ep.endpointkey
JOIN ACCOUNTS a ON ART.accountkey = a.accountkey
WHERE
ART.STATUS IN (4, 3, 😎
AND ep.endpointname = ''
AND ART.PROVISIONINGCOMMENTS LIKE '%properties%'
GROUP BY u.username, u.firstname;

[rushikeshvartak]

• What is the use of mapping responses from add access?
• Why can't you do this mapping in import json instead of additional enhanced query?