and more in a single search tool across platforms. Read the announcement here. |
02/17/2023 09:54 AM
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
Option 3
02/17/2023 11:36 AM
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.',''))
02/17/2023 12:55 PM
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',''))
02/17/2023 01:56 PM
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.
02/17/2023 02:03 PM
02/17/2023 02:49 PM
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.
02/17/2023 03:08 PM
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.',''))
02/17/2023 03:18 PM
I just ran as you suggested, and the 4 slashes are just transforming the original value to lower case without splitting the values.
02/17/2023 03:54 PM - edited 02/17/2023 04:19 PM
Try below
lower(SUBSTRING_INDEX(users.email, "@", 1))=lower(replace(SUBSTRING_INDEX(accounts.name, ".", -2),'US',''))
02/22/2023 11:22 AM
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.
02/22/2023 11:45 AM
This endpoint has multiple accounts per User, and I see only one getting correlated.
02/22/2023 04:38 PM
is there a way we can force run the correlation rule after the import like an utility job?
02/22/2023 04:46 PM
Correlation happens as part account creation. If you need correlate existing accounts then you may need do csv upload
03/03/2023 07:12 AM
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