Inquiry about custormize Account Correlation Rule.

[moosam2]

Hi, i want to make correlation rule for below type of account but it's not working. could you please help me on this case?

Slice the account name : same with systemusername.

or Substitute "_" to "@", and slice to get email address from account name

Account name form: abc_domain.com#EXT#@domain.onmicrosoft.com

purpose : user.systemusername = abc  or  user.email = abc@domain.com (substitution _ -> @)

What i tried (Failed)

concat(users.systemUserName,'_domain.com','#EXT#@domain.onmicrosoft.com')=(accounts.name)

(users.systemUserName)=TRIM(TRAILING '_domain.com#EXT#@domain.onmicrosoft.com' FROM accounts.name)

[SumathiSomala]

@moosam2 Could you please try with below correlation rule( advanced query )

LEFT(accounts.name, LOCATE('_', accounts.name)-1)=users.systemUserName

 Before that test the below query in data analyzer first for one of the users and if its displaying the result as expected, you can use it in Advanced Config in User Account Correlation rule at the Endpoint level.

select name,LEFT(name, LOCATE('_', name)-1)from accounts where accountkey=904

If this reply answered your question, please Accept it As Solution to help others who may have a similar problem.

[SumathiSomala]

@moosam2 is your issue resolved?

If above reply answered your question, please Accept it As Solution to help others who may have a similar problem.

[SumathiSomala]

@moosam2 Please click on accept as solution button and close the thread if your issue is resolved. 

Thank you. 

[rushikeshvartak · Accepted solution]

Can you test if below query works

select name,LEFT(name, LOCATE('_', name)-1) data from accounts where name=‘1gajaj’ union

select username name,systemusername data from users where username=‘abcd’

update account name & username in query for 1 sample user and see if its matching

[moosam2]

Thank you! i've got solution from your advice!

[gauravchandok]

@moosam2  Can you please share your updated SQL query mentioned in correlation rule..