Importing Proxy Email Addresses from ADSI to User_Attributes table Issue

[rakesh_iam]

Dear All,

We have configured the ADSI connector and have imported the proxy email addresses to a account custom property attribute. Post that, we have created a Sav4Sav connection to execute an analytics and promote attributes from AD to relevant user object. The process is also used to import proxy email address from the custom property (account attribute) to User_attributes table. 

We are seeing a strange behaviour where the proxy email address update happens only for the last few users processed by the Sav4Sav connection. Please find the screenshot of the log below,

As we can see, the 20+ users processed towards the end of the job run got their proxy email addresses imported as expected. But for the others, we could see the userattribute is updated as null.

{
"ADDITIONALTABLES": {
"USERS": "SELECT USERNAME, customproperty60, customproperty50, customproperty55 FROM USERS",
"USER_ATTRIBUTES": "SELECT USERKEY, DATA, ATTRIBUTENAME FROM USER_ATTRIBUTES"
},
"COMPUTEDCOLUMNS": [
"customproperty60",
"customproperty61",
"customproperty62",
"USERATTRIBUTE_EMAIL"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET USERATTRIBUTE_EMAIL=case when (NEWUSERDATA.USERATTRIBUTE_EMAIL is not null and NEWUSERDATA.USERATTRIBUTE_EMAIL != '') then REPLACE(REPLACE(NEWUSERDATA.USERATTRIBUTE_EMAIL, 'smtp:', ''), 'SMTP:', '') else '' end"
]
}

Kindly let us know if you have faced these issues before and any suggestion would be really helpful

We have followed the KB article below,

https://forums.saviynt.com/t5/saviynt-knowledge-base/achieve-email-uniqueness-check-during-email-gen...

Regards,

Rakesh

[This message has been edited by moderator to merge reply comment]

[Darshanjain]

Hi @rakesh_iam 

You need to put or condition instead of And condition in this query and see if it helps-

(NEWUSERDATA.USERATTRIBUTE_EMAIL is not null and NEWUSERDATA.USERATTRIBUTE_EMAIL != '') 

Also it will process for all users if data is being fetched in that job for all users then it will process the data in user attributes ( remove the case condition as well and try if its still a issue).

Thanks

Darshan

[rakesh_iam]

Hi @Darshanjain ,

Thanks for your response.

The condition should be 'add' and not 'or' in the query,

(NEWUSERDATA.USERATTRIBUTE_EMAIL is not null and NEWUSERDATA.USERATTRIBUTE_EMAIL != '') 

as we only need to process the data if the value is both not null and not empty.

Nevertheless, I have removed the case statement and the modify user data json looks like the below,

UPDATE NEWUSERDATA SET USERATTRIBUTE_EMAIL= REPLACE(REPLACE(NEWUSERDATA.USERATTRIBUTE_EMAIL, 'smtp:', ''), 'SMTP:', ''

But the issue still persist. The job is updating the proxy email address only for the last few records.

We have checked the following,

• We are importing the users through Sav4Sav connection by executing the analytics. Maximum page size that can be provided is 500.
• We have checked both the analytics output and logs and could confirm that all users are read when the job runs.
• We updated the analytics query to execute for sample users (not working before). With the sample run, the job is able to update the email address which confirms the fact that the configuration is working as expected and the issue occurs only when we run the job for all users.

Kindly let me know for any further information.

Regards,

Rakesh

[Darshanjain]

Hi @rakesh_iam 

Okay got it, can you send the user import json, make sure the value you have mapped to userattribute_email to the value and those values are to be populated for those users where they are missing.

Thanks

Darshan

[rakesh_iam]

Hi @Darshanjain ,

Thanks again!

Please find the user import json below,

{
"connection": "userAuth",
"url": "https://removed.saviyntcloud.com/ECM/api/v5/fetchRuntimeControlsDataV2",
"httpMethod": "POST",
"httpHeaders": {
"Authorization": "${access_token}",
"contentType": "application/json"
},
"httpContentType": "application/json",
"httpParams": "{\"analyticsname\":\"bgl-promote-active-directory-attributes-to-user-object\"}",
"colsToPropsMap": {
"username": "username~#~char",
"customproperty50": "account_status~#~char",
"customproperty18": "customproperty6~#~char",
"USERATTRIBUTE_EMAIL": "customproperty54~#~char",
"customproperty60": "customproperty54~#~char",
"customproperty55": "customproperty4~#~char"
},

"userResponsePath": "results",
"pagination": {
"offset": {
"offsetParam": "offset",
"batchParam": "max",
"batchSize": 500,
"totalCountPath": "completeResponseMap.totalcount"
}
}
}

The customproperty54 account attribute is where proxy email address is stored and the same is passed to USER_ATTRIBUTE and also to another attribute within user object

The proxy email address is stored in account and analytics is used to fetch the data to import the same to user table 'customproperty60'. We could see the value is imported successfully to customproperty60 but not to user_attribute table. In total, there are 5462 users returned in analytics but only 40+ users have proxy email address updated in user_attribute table whereas all of them have their customproperty60 populated.

Sample user:

Account data:

User data:

For users where the update has worked we would see as shown below,

Kindly let me know for any further information.

Thanks,

Rakesh

[This message has been edited by moderator to mask sensitive information]

[Darshanjain]

Hi @rakesh_iam 

Its a strange case where its not updating for some users, can you once try this as anyways you are using the cp54 value to cp60, so use the same cp60 value to update userattribute_email and see if you are able to update all users or not.

UPDATE NEWUSERDATA SET USERATTRIBUTE_EMAIL= REPLACE(REPLACE(NEWUSERDATA.CUSTOMPROPERTY60, 'smtp:', ''), 'SMTP:', ''

Thanks

Darshan

[rakesh_iam]

Hi @Darshanjain ,

I tried the above query but it is still not working. I could see the data is only updated for users that have processed as part of the last page (towards the end of the job), I believe it is to do with how and when the data is persisted in USER_ATTRIBUTE table.

Regards,

Rakesh

[Darshanjain]

Hi @rakesh_iam 

Okay, can you raise a support ticket on this, we may need to check this in detail for the records and logs.

Mention the above link in the FD ticket and attach all the details.

Thanks

Darshan

[rakesh_iam]

Hi @Darshanjain ,

Thanks. We have created a support ticket.

Regards,

Rakesh

[09]

@rakesh_iam were you able to resolve this issue?

[Darshanjain]

Hi @09 

we have a internal jira created and our team is working on it. this issue is only for Rest connector.

Are you also facing the issue? if yes please let me know which connector you are using.

Thanks

Darshan

[09]

yes, we are using REST connector. 

Is it working with AD connector?

[Darshanjain]

Yes in Ad and ADSI its working,

which version are you using? you can try in lower instance for the same and see the behavior even for rest connector as well. 

Thanks

Darshan