How to write User Account Correlation Rule - SQL & Template Method

[sudeshjaiswal]

Use Case

Under the Endpoint configurations, when you click on the User Account Correlation Rule -> Advanced Config. 

There is a specific Attribute called Correlation Rule Type: This can be SQL or TEMPLATE.

Understanding about two methods,

1) SQL - When it is mentioned as SQL, the mentioned Advanced Query will be directly fit into a SQL WHERE clause and the Correlation will be performed.

2) TEMPLATE - When it is mentioned as TEMPLATE, the mentioned Advanced Query will be evaluated as a groovy expression template and all the users to account will be put into this template and correlated with the corresponding user.

This Following document tells about how this feature is used in the Product.

Pre-requisites

Connection/Endpoint

Applicable Version(s)

All

Solution

Refer below example to understand the configuration.

Lets say there is a specific account name like,

-adm-<systemusername>-rEU , -dcs-<systemusername>-rEU, -prj-<systemusername>-rEU, -rot-<systemusername>-rEU, -wrk-<systemusername>-rEU, -lsd-<systemusername>-rEU

We need to correlate all the accounts with the corresponding User's systemusername. So we have created an approach and using the TEMPLATE option,

TEMPLATE###users.systemUserName=(accounts.name.startsWith('-adm-')||accounts.name.startsWith('-dcs-')||accounts.name.startsWith('-prj-')||accounts.name.startsWith('-rot-')||accounts.name.startsWith('-wrk-')||accounts.name.startsWith('-lsd-')||accounts.name.startsWith('-gsm-')||accounts.name.startsWith('-sec-')||accounts.name.startsWith('-tst-'))?accounts.name.tokenize('-')[1].trim():accounts.name

If the account names are in the mentioned prefixes, then, we fetch the systemusername using the Groovy expression and equate it with the user's systemUserName.

This improves the performance of the User Account Correlation if there is a huge condition to correlate as mentioned above.

If you are facing any issues with the performance degradation of User Account Correlation, please change to Template Method instead of SQL Method.

Please Note: This should start with TEMPLATE###.

References

https://docs.saviyntcloud.com/bundle/EIC-Admin-v23x/page/Content/Chapter02-Identity-Repository/Creat... 

[gauravchandok]

@sudeshjaiswal 

I tried using the template for account co relation and was able to make it work for a sample account and username but I get error in logs if i do any string manipulation on the user.username.

See the sample below:

Username: gaurav.chandok@testDomain.com
accountName: gaurav.chandok@xyzabc.com

Here before @ gaurav.chandok is exact match in account name and username so I wrote the below template for corelation and I got below error.

users.username.tokenize('@')[0].trim()=(accounts.name.startsWith('Gaurav.Chandok') || accounts.name.startsWith('gaurav.chandok'))?accounts.name.tokenize('@')[0].trim():accounts.name

Error: "2024-04-22T10:39:03.182+00:00","ecm-worker","hql.PARSER","quartzScheduler_Worker-4-zgj6r","ERROR","<AST>:1:44: unexpected AST node: ["

"2024-04-22T10:39:03.183+00:00","ecm-worker","saviynt.ImportExternalDbService","quartzScheduler_Worker-4-zgj6r","ERROR","Error while associating Users and Accounts: null"

But if I use the below Template it works fine.. here I am doing string manipulation only on Right Hand Side for account name.

users.username=(accounts.name.startsWith('Gaurav.Chandok') || accounts.name.startsWith('gaurav.chandok'))?accounts.name.tokenize('@')[0].trim()+'@testDomain.com':accounts.name

Please let me know if this is a bug or string manipulation is only allowed in account name, also please let me know if we can have more than one rule like in sql we can have multiple rules seperated by #

Thanks

[gauravchandok]

@Rishi: Please help on this query.

[sudeshjaiswal]

Hello @gauravchandok,

Can you please try with the below condition using SQL not the template.

 ((SUBSTRING_INDEX(USERS.username, '@', 1) = SUBSTRING_INDEX(ACCOUNTS.name, '@', 1)) OR (USERS.email like 'gaurav@%' and ACCOUNTS.name like 'gaurav@%'))

Yes, You can use multiple condition, Please refer below example for the mutliple conditon.

concat(substring('Bob3-',1,5),substring(users.firstname,1,2))#concat(substring('Bob3-',1,5),substring(users.firstname,1,3))#concat(substring('Bob3-',1,5),substring(users.firstname,1,4))#concat(substring('Bob3-',1,5),substring(users.firstname,1,5))#concat(substring('Bob3-',1,5),substring(users.firstname,1,6))#concat(substring('Bob3-',1,5),substring(users.firstname,1,7))

Thanks

[gauravchandok]

Hi @sudeshjaiswal 

We have some complex use case thats why we need to use template, currently its working with SQL as we had already implemented what you have suggested above. But their are some use case and data issues due to which its necessary for us to use TEMPLATE instead of SQL query.

1. Kindly suggest if we can have multiple conditions in TEMPLATE or not?
2. Is string manipulation only allowed in account name side?

Thanks