Click HERE to see how Saviynt Intelligence is transforming the industry. |
10/10/2024 10:11 PM
Hi team,
When someone gets terminated from certification, it generates a comment like this:
Users are being terminated through Certification -#####31579#####[Test_UM_Employee_V22 - 00569565 (FirstName LastName)] on -Thu Oct 10 09:31:20 UTC 2024#####Reason: User Terminated and Certification Locked (UM Campaign)
The client needs us to extract the username of the certifying user from this string - however whenever i try to use charindex it says invalid:
For example i tried to extract everything between the square brackets:
select u.username as 'USERNAME', u.employeeid as 'USEREMPLOYEEID', a.username as 'MANAGER', u.firstname as 'FIRSTNAME', u.lastname as 'LASTNAME', a.firstname as 'MFIRSTNAME', a.lastname as 'MLASTNAME', u.termdate as 'TERMDATE', SUBSTRING(u.comments, CHARINDEX('[', u.comments) + 1, CHARINDEX(']', u.comments) - CHARINDEX('[', u.comments) - 1) as 'UCOMMENTS' from users u, users a where u.manager = a.userkey and u.statuskey = 0 and u.comments like '%Certification%' and u.termdate >= NOW() - INTERVAL 1 DAY
but its not working. Is there a regex we can use in this case?
Or alternatively, some other user property that should store who has triggered the termination. The updateuser attribute is storing admin userkey.
Thanks in advance
Solved! Go to Solution.
10/10/2024 10:36 PM
SELECT U.USERNAME AS
'USERNAME',
U.EMPLOYEEID AS
'USEREMPLOYEEID',
A.USERNAME AS
'MANAGER',
U.FIRSTNAME AS
'FIRSTNAME',
U.LASTNAME AS
'LASTNAME',
A.FIRSTNAME AS
'MFIRSTNAME',
A.LASTNAME AS
'MLASTNAME',
U.TERMDATE AS
'TERMDATE',
SUBSTRING_INDEX(SUBSTRING_INDEX(U.COMMENTS, '[', -1), ']', 1) AS
'UCOMMENTS'
FROM USERS U
JOIN USERS A
ON U.MANAGER = A.USERKEY
WHERE U.STATUSKEY = 0
AND U.COMMENTS LIKE '%Certification%'
AND U.TERMDATE >= NOW() - INTERVAL 1 DAY;
10/10/2024 10:57 PM
Thank you so much, this did the trick!