User Correlation Rule configuration

[nitishdas]

Hi Team,

We have a new requirement for Active directory admin accounts where we need to modify the user correlation rule using sql queries according to the accounts that will be created directly in AD. There are some examples of accounts such as a_usernameL2, a_usernameL3, a_usernameX, a_username. According to these examples I have written the below logic and tested in data analyser but not sure whether it will work in the correlation rule. Could anyone suggest if this looks fine or there can be any modifications. I tried testing in the data analyser and it is returning the accounts by removing the characters a_, L2, L3 and X.

SELECT
CASE
WHEN a.name LIKE "%X%" THEN LEFT(RIGHT(a.name,length(a.name)-2),length(a.name)-3)
WHEN a.name LIKE "%L2%" THEN LEFT(RIGHT(a.name,length(a.name)-2),length(a.name)-4)
WHEN a.name LIKE "%L3%" THEN LEFT(RIGHT(a.name,length(a.name)-2),length(a.name)-4)
ELSE SUBSTRING(a.name,3,20)
END AS 'Account Name'
FROM ACCOUNTS a where a.accountkey=XXXXX

[NM]

Hi @nitishdas , is that the expected output? If so yes then it will work.

[rushikeshvartak]

SELECT
CASE
WHEN a.name LIKE "%X%" THEN SUBSTRING(a.name, 3, LEN(a.name) - 3) -- Adjusted length calculation
WHEN a.name LIKE "%L2%" THEN SUBSTRING(a.name, 3, LEN(a.name) - 4) -- Adjusted length calculation
WHEN a.name LIKE "%L3%" THEN SUBSTRING(a.name, 3, LEN(a.name) - 4) -- Adjusted length calculation
ELSE SUBSTRING(a.name, 3, LEN(a.name) - 2) -- Adjusted length calculation for other cases
END AS 'Account Name'
FROM ACCOUNTS a
WHERE a.accountkey=XXXXX