We are delighted to share our new EIC Delivery Methodology for efficiently managing Saviynt Implementations and delivering quick time to value. CLICK HERE.

User Account Correlation rule

igaravi
Regular Contributor
Regular Contributor

For correlating accounts (from DB Import) to Users,  I am using advanced config due to transforms. Tried a few options but only a few accounts are getting correlated. The first rule with "admin." prefix is working and correlated but the next rules are not processed. I tried escaping backslash (\\) but that didn't work either.

Option 1

lower(substring_index(users.email,'@',1))=lower(replace(replace(replace(accounts.name,'admin.',''), 'US\',''),'da.',''))
 
Option 2
lower(substring_index(users.email,'@',1))=lower(replace(replace(replace(accounts.name,'admin.',''), 'US\\',''),'da.',''))

Option 3

lower(substring_index(users.email,'@',1))=lower(substring_index(accounts.name,'admin.',-1))#lower(substring_index(users.email,'@',1))=lower(substring_index(accounts.name,'US\da.',-1))#lower(substring_index(users.email,'@',1))=lower(substring_index(accounts.name,'US\',-1))#lower(substring_index(users.email,'@',1))=lower(accounts.name)
13 REPLIES 13

igaravi
Regular Contributor
Regular Contributor

I simplified the rule as below, but still no luck. Example account names like US\admin.John.Doe or US\da.John.Doe or US\john.doe and email of the form John.Doe@company.com

lower(substring_index(users.email,'@',1))=lower(replace(replace((substring_index(accounts.name,'\\',-1)),'admin.',''), 'da.',''))

Ishan
Saviynt Employee
Saviynt Employee

Hi @igaravi , if you check the condition as per the example you provided, the left value wont be equal to right value as you left value will be john.doe and right value will be usjohn.doe.

Use the following condition and check. You need to replace "us" in the string too.

lower(substring_index(users.email,'@',1))=lower(replace(replace(replace((substring_index(accounts.name,'\\',-1)),'US\admin.',''), 'US\da.',''),'US',''))

Ishan Kamat
Technical Architect, Professional Services
SaviyntLogo.png

igaravi
Regular Contributor
Regular Contributor

So the substring_index -1 function pulls the value to the right of \, so US will be left out. Its coming out well when I run it in data analyzer but is not work as part of the correlation processing.

Ishan
Saviynt Employee
Saviynt Employee

I don't see the US part left out in Data Analyzer with the query your posted:

Screenshot 2023-02-17 at 2.02.12 PM.png

Ishan Kamat
Technical Architect, Professional Services
SaviyntLogo.png

igaravi
Regular Contributor
Regular Contributor

hmm...interesting. Its not working properly in data analyzer when we pass a constant string. But its working as expected when we query the value from the table like accounts.name. On the other hand, when i use | pipe delimiter with a constant its working well in data analyzer.

igaravi_0-1676674160798.png

 

Ishan
Saviynt Employee
Saviynt Employee

Yes this is happening because of the \ escaping.

In your original correlation query can you try escaping it twice as in add the '\' 4 times instead of 2 like this and check:

lower(substring_index(users.email,'@',1))=lower(replace(replace((substring_index(accounts.name,'\\\\',-1)),'admin.',''), 'da.',''))

Ishan Kamat
Technical Architect, Professional Services
SaviyntLogo.png

igaravi
Regular Contributor
Regular Contributor

I just ran as you suggested, and the 4 slashes are just transforming the original value to lower case without splitting the values.

Try below

lower(SUBSTRING_INDEX(users.email, "@", 1))=lower(replace(SUBSTRING_INDEX(accounts.name, ".", -2),'US',''))


Regards,
Saathvik
If this reply answered your question, please Accept As Solution and give Kudos to help others facing similar issue.

igaravi
Regular Contributor
Regular Contributor

I pushed this logic to source database and populating the account CP28 with right format on DB Import. New correlation rule has been defined as below but its very weird that no correlation is happening at all now.

lower(substring_index(users.email,'@',1))=accounts.customproperty28

From data analyzer, I can see values in correct format like john.doe=john.doe. Tried using trim but did not work.

igaravi
Regular Contributor
Regular Contributor

This endpoint has multiple accounts per User, and I see only one getting correlated.

igaravi
Regular Contributor
Regular Contributor

is there a way we can force run the correlation rule after the import like an utility job?

Correlation happens as part account creation. If you need correlate existing accounts then you may need do csv upload 


Regards,
Saathvik
If this reply answered your question, please Accept As Solution and give Kudos to help others facing similar issue.

igaravi
Regular Contributor
Regular Contributor

how to escape back slash is analytics report? The below works in data analyzer but getting only top 100 rows, but the same query does not work in Analytics report

select a.name, u.username
from accounts a inner join users u
on concat(u.firstname,'.',u.lastname)=substring_index(a.customproperty28,'\\',-1) where a.endpointkey=187 and u.email not like 'noemail%' and u.statuskey=1