User Account Correlation - Multiple Email on user identity

[kushalpatadia]

Hi Team,

We have a requirement that there can be multiple email aliases which a user identity can have and they can be stored in one of the user custom property's. During user account correlation we need to consider all of these emails to check the correlation with account.

We have considered users Customproperty60 since it is of a longtext type.

We are storing email address of account at accounts customproperty1. 

Example:

Consider below multiple email values for user are stored in Customproperty60 in a comma separated manner:

richard.smith@company1.com,rich.smith@company2.com,rcsmith@company3.com

If account customproperty1 has value richard.smith@company1.com, we want it to be correlated to above user.

We are using below advanced config in user account correlation rule: (Correlation Rule Type = SQL)

SUBSTRING_INDEX(SUBSTRING_INDEX('users.customproperty60', ',', 1), ',', -1) = accounts.customproperty1

The substring_index function on users CP60 is fetching the right email but correlation is not working in Saviynt.

Kindly advise on how we can achieve this correlation.

NOTE:

Since user can have multiple email aliases, we would need to add OR conditions in user account correlation rule in future and the logic would look something like below. We have currently not added the OR condition since even the one to one correlation is not working.

SUBSTRING_INDEX(SUBSTRING_INDEX('users.customproperty60', ',', 1), ',', -1)=accounts.customproperty1 OR SUBSTRING_INDEX(SUBSTRING_INDEX('users.customproperty60', ',', 2), ',', -1)=accounts.customproperty1 OR SUBSTRING_INDEX(SUBSTRING_INDEX('users.customproperty60', ',', 3), ',', -1)=accounts.customproperty1.

Reference Forums Post:

https://forums.saviynt.com/t5/identity-governance/how-to-map-multivalued-attributes-in-user-account-...

Thanks,

Kushal

[NM · Accepted solution]

@kushalpatadia try this s

SUBSTRING_INDEX(SUBSTRING_INDEX(users.customproperty60, ',', 1), ',', -1) = accounts.customproperty1

[naveenss · Accepted solution]

@kushalpatadia below query is working for me

SUBSTRING_INDEX(SUBSTRING_INDEX(users.customproperty60, ',', 1), ',', -1) = accounts.customproperty1

[kushalpatadia]

Hi @NM @naveenss ,

This worked.

SUBSTRING_INDEX(SUBSTRING_INDEX(users.customproperty60, ',', 1), ',', -1) = accounts.customproperty1

Really appreciate your help on this.

Best Regards,

Kushal