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.
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
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.
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.
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.
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:
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.
From data analyzer, I can see values in correct format like john.doe=john.doe. Tried using trim but did not work.
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