Click HERE to see how Saviynt Intelligence is transforming the industry. |
10/14/2024 03:45 AM
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:
Thanks,
Kushal
Solved! Go to Solution.
10/14/2024 03:50 AM
@kushalpatadia try this s
SUBSTRING_INDEX(SUBSTRING_INDEX(users.customproperty60, ',', 1), ',', -1) = accounts.customproperty1
10/14/2024 03:55 AM - edited 10/14/2024 03:55 AM
@kushalpatadia below query is working for me
SUBSTRING_INDEX(SUBSTRING_INDEX(users.customproperty60, ',', 1), ',', -1) = accounts.customproperty1
10/14/2024 09:53 AM