Click HERE to see how Saviynt Intelligence is transforming the industry. |
11/10/2023 02:02 AM
Hi,
We have a use case as mentioned below:
Problem statement:
When we try to import the new users, user creation is failing because of the error, "missing username". We expect that username and systemusername be generated because we have them defined at the global config, however, it is not working as expected. This scenario is working fine when we create the users via API.
Please find the systemusername rule and MODIFYUSERJSON (pre-processor) query we use:
Systemusername rule:
CASE WHEN (companyname='XXXX' or orgunitid=XXXXXXXX) and length(lastname)>=5 then replace(concat(substring(lastname,1,5) , substring(firstname,1,2)),"ñ","n") END ###
CASE WHEN (companyname='XXXX' or orgunitid=XXXXXXXX) and length(lastname)<5 then replace(concat(substring(lastname,1,5) , substring(firstname,1,7-LENGTH(replace(LASTNAME,"ñ","n")))),"ñ","n") END ###
CASE WHEN (companyname='XXXX' or orgunitid=XXXXXXXX) and length(lastname)>=5 then replace(concat(substring(lastname,1,5) , substring(firstname,1,1),1),"ñ","n") END ###
CASE WHEN (companyname='XXXX' or orgunitid=XXXXXXXX) and length(lastname)<5 then replace(concat(substring(lastname,1,5) , substring(firstname,1,7-LENGTH(replace(LASTNAME,"ñ","n"))),1),"ñ","n") END ###
CASE WHEN (companyname='XXXX' or orgunitid=XXXXXXXX) and length(lastname)>=5 then replace(concat(substring(lastname,1,5) , substring(firstname,1,1),2),"ñ","n") END ###
CASE WHEN (companyname='XXXX' or orgunitid=XXXXXXXX) and length(lastname)<5 then replace(concat(substring(lastname,1,5) , substring(firstname,1,7-LENGTH(replace(LASTNAME,"ñ","n"))),2),"ñ","n") END ###
CASE WHEN (companyname='XXXX' or orgunitid=XXXXXXXX) and length(lastname)>=5 then replace(concat(substring(lastname,1,5) , substring(firstname,1,1),3),"ñ","n") END ###
CASE WHEN (companyname='XXXX' or orgunitid=XXXXXXXX) and length(lastname)<5 then replace(concat(substring(lastname,1,5) , substring(firstname,1,7-LENGTH(replace(LASTNAME,"ñ","n"))),3),"ñ","n") END ###
CASE WHEN (companyname='XXXX' or orgunitid=XXXXXXXX) and length(lastname)>=5 then replace(concat(substring(lastname,1,5) , substring(firstname,1,1),4),"ñ","n") END ###
CASE WHEN (companyname='XXXX' or orgunitid=XXXXXXXX) and length(lastname)<5 then replace(concat(substring(lastname,1,5) , substring(firstname,1,7-LENGTH(replace(LASTNAME,"ñ","n"))),4),"ñ","n") END ###
CASE WHEN (companyname='XXXX' or orgunitid=XXXXXXXX) and length(lastname)>=5 then replace(concat(substring(lastname,1,5) , substring(firstname,1,1),5),"ñ","n") END ###
CASE WHEN (companyname='XXXX' or orgunitid=XXXXXXXX) and length(lastname)<5 then replace(concat(substring(lastname,1,5) , substring(firstname,1,7-LENGTH(replace(LASTNAME,"ñ","n"))),5),"ñ","n") END ###
CASE WHEN (companyname='XXXX' or orgunitid=XXXXXXXX) and length(lastname)>=5 then replace(concat(substring(lastname,1,5) , substring(firstname,1,1),6),"ñ","n") END ###
CASE WHEN (companyname='XXXX' or orgunitid=XXXXXXXX) and length(lastname)<5 then replace(concat(substring(lastname,1,5) , substring(firstname,1,7-LENGTH(replace(LASTNAME,"ñ","n"))),6),"ñ","n") END ###
CASE WHEN (companyname='XXXX' or orgunitid=XXXXXXXX) and length(lastname)>=5 then replace(concat(substring(lastname,1,5) , substring(firstname,1,1),7),"ñ","n") END ###
CASE WHEN (companyname='XXXX' or orgunitid=XXXXXXXX) and length(lastname)<5 then replace(concat(substring(lastname,1,5) , substring(firstname,1,7-LENGTH(replace(LASTNAME,"ñ","n"))),7),"ñ","n") END ###
CASE WHEN (companyname='XXXX' or orgunitid=XXXXXXXX) and length(lastname)>=5 then replace(concat(substring(lastname,1,5) , substring(firstname,1,1),8),"ñ","n") END ###
CASE WHEN (companyname='XXXX' or orgunitid=XXXXXXXX) and length(lastname)<5 then replace(concat(substring(lastname,1,5) , substring(firstname,1,7-LENGTH(replace(LASTNAME,"ñ","n"))),8),"ñ","n") END ###
CASE WHEN (companyname='XXXX' or orgunitid=XXXXXXXX) and length(lastname)>=5 then replace(concat(substring(lastname,1,5) , substring(firstname,1,1),9),"ñ","n") END ###
CASE WHEN (companyname='XXXX' or orgunitid=XXXXXXXX) and length(lastname)<5 then replace(concat(substring(lastname,1,5) , substring(firstname,1,7-LENGTH(replace(LASTNAME,"ñ","n"))),9),"ñ","n") END ###
CASE WHEN customproperty10 = '' OR customproperty10 IS NULL then CONCAT( "S", RIGHT( CONCAT('00000000',userkey), 9), 'tst') else concat(customproperty10,'tst') END
Username rule:
MODIFYUSERJSON: (We are importing just one user for testing)
{
"ADDITIONALTABLES": {
"USERS": "SELECT USERKEY,username,systemusername,employeeid, customproperty1 FROM USERS"
},
"COMPUTEDCOLUMNS": [
"email",
"manager",
"userSource",
"customproperty1",
"customproperty2",
"customproperty3",
"statuskey"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET manager = (select u.username from currentusers u where u.employeeid = manager LIMIT 1)",
"UPDATE NEWUSERDATA SET userSource = '4'",
"UPDATE NEWUSERDATA new SET email = REPLACE(email,'@','.tst@')",
"UPDATE NEWUSERDATA new, CURRENTUSERS u SET new.customproperty1 = CONCAT_WS(',', 'Staff', u.customproperty1) WHERE new.employeeid = u.employeeid",
"UPDATE NEWUSERDATA new, CURRENTUSERS u SET new.customproperty1 = u.customproperty1 WHERE new.employeeid = u.employeeid AND FIND_IN_SET('Staff', u.customproperty1) > 0",
"UPDATE NEWUSERDATA SET customproperty1 = 'Staff' WHERE customproperty1 IS NULL OR customproperty1 = ''",
"UPDATE NEWUSERDATA SET customproperty2 = CASE WHEN employeetype IN ('XX','Y') THEN 'ABC' ELSE 'AB' END ",
"UPDATE NEWUSERDATA SET customproperty3 = customproperty2",
"UPDATE NEWUSERDATA SET statuskey=1 WHERE ((statuskey=0) and (DATEDIFF(CURDATE(), customproperty16) BETWEEN -14 AND 0))",
"DELETE FROM NEWUSERDATA where statuskey = 0 and username IN (SELECT username FROM (SELECT username FROM NEWUSERDATA GROUP BY username HAVING COUNT(*) > 1 and SUM(statuskey) = 1) tmp)",
"DELETE FROM NEWUSERDATA where (username, enddate) NOT IN (SELECT username, enddate FROM (SELECT username, MAX(enddate) AS enddate FROM NEWUSERDATA GROUP BY username HAVING COUNT(*) > 1 and SUM(statuskey) = 0) tmp1) and username IN (SELECT username FROM (SELECT username FROM NEWUSERDATA GROUP BY username HAVING COUNT(*) > 1 and SUM(statuskey) = 0) tmp2)",
"DELETE FROM NEWUSERDATA where employeeid NOT IN ('00320012')"
]
}
We have also tried to simulate the systemusername generation from the pre-processor but that did not work for us. Query mentioned below:
{
"ADDITIONALTABLES": {
"USERS": "SELECT USERKEY,username,employeeid,firstname,lastname,customproperty1 FROM USERS"
},
"COMPUTEDCOLUMNS": [
"username",
"systemusername",
"email",
"manager",
"userSource",
"customproperty1",
"customproperty2",
"customproperty3",
"statuskey"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET SYSTEMUSERNAME = (CASE WHEN (companyname='XXXX' or orgunitid=XXXXXXXX) and length(lastname)>=5 then replace(concat(substring(lastname,1,5),substring(firstname,1,2)),'ñ','n') WHEN (companyname='XXXX' or orgunitid=XXXXXXXX) and length(lastname)<5 then replace(concat(substring(lastname,1,5),substring(firstname,1,7-LENGTH(replace(LASTNAME,'ñ','n')))),'ñ','n') WHEN (companyname='XXXX' or orgunitid=XXXXXXXX) and length(lastname)>=5 then replace(concat(substring(lastname,1,5),substring(firstname,1,1),1),'ñ','n') WHEN (companyname='XXXX' or orgunitid=XXXXXXXX) and length(lastname)<5 then replace(concat(substring(lastname,1,5),substring(firstname,1,7-LENGTH(replace(LASTNAME,'ñ','n'))),1),'ñ','n') WHEN (companyname='XXXX' or orgunitid=XXXXXXXX) and length(lastname)>=5 then replace(concat(substring(lastname,1,5),substring(firstname,1,1),2),'ñ','n') WHEN (companyname='XXXX' or orgunitid=XXXXXXXX) and length(lastname)<5 then replace(concat(substring(lastname,1,5),substring(firstname,1,7-LENGTH(replace(LASTNAME,'ñ','n'))),2),'ñ','n') WHEN (companyname='XXXX' or orgunitid=XXXXXXXX) and length(lastname)>=5 then replace(concat(substring(lastname,1,5),substring(firstname,1,1),3),'ñ','n') WHEN (companyname='XXXX' or orgunitid=XXXXXXXX) and length(lastname)<5 then replace(concat(substring(lastname,1,5),substring(firstname,1,7-LENGTH(replace(LASTNAME,'ñ','n'))),3),'ñ','n') WHEN (companyname='XXXX' or orgunitid=XXXXXXXX) and length(lastname)>=5 then replace(concat(substring(lastname,1,5),substring(firstname,1,1),4),'ñ','n') WHEN (companyname='XXXX' or orgunitid=XXXXXXXX) and length(lastname)<5 then replace(concat(substring(lastname,1,5),substring(firstname,1,7-LENGTH(replace(LASTNAME,'ñ','n'))),4),'ñ','n') WHEN (companyname='XXXX' or orgunitid=XXXXXXXX) and length(lastname)>=5 then replace(concat(substring(lastname,1,5),substring(firstname,1,1),5),'ñ','n') WHEN (companyname='XXXX' or orgunitid=XXXXXXXX) and length(lastname)<5 then replace(concat(substring(lastname,1,5),substring(firstname,1,7-LENGTH(replace(LASTNAME,'ñ','n'))),5),'ñ','n') WHEN (companyname='XXXX' or orgunitid=XXXXXXXX) and length(lastname)>=5 then replace(concat(substring(lastname,1,5),substring(firstname,1,1),6),'ñ','n') WHEN (companyname='XXXX' or orgunitid=XXXXXXXX) and length(lastname)<5 then replace(concat(substring(lastname,1,5),substring(firstname,1,7-LENGTH(replace(LASTNAME,'ñ','n'))),6),'ñ','n') WHEN (companyname='XXXX' or orgunitid=XXXXXXXX) and length(lastname)>=5 then replace(concat(substring(lastname,1,5),substring(firstname,1,1),7),'ñ','n') WHEN (companyname='XXXX' or orgunitid=XXXXXXXX) and length(lastname)<5 then replace(concat(substring(lastname,1,5),substring(firstname,1,7-LENGTH(replace(LASTNAME,'ñ','n'))),7),'ñ','n') WHEN (companyname='XXXX' or orgunitid=XXXXXXXX) and length(lastname)>=5 then replace(concat(substring(lastname,1,5),substring(firstname,1,1),8),'ñ','n') WHEN (companyname='XXXX' or orgunitid=XXXXXXXX) and length(lastname)<5 then replace(concat(substring(lastname,1,5),substring(firstname,1,7-LENGTH(replace(LASTNAME,'ñ','n'))),8),'ñ','n') WHEN (companyname='XXXX' or orgunitid=XXXXXXXX) and length(lastname)>=5 then replace(concat(substring(lastname,1,5),substring(firstname,1,1),9),'ñ','n') WHEN (companyname='XXXX' or orgunitid=XXXXXXXX) and length(lastname)<5 then replace(concat(substring(lastname,1,5),substring(firstname,1,7-LENGTH(replace(LASTNAME,'ñ','n'))),9),'ñ','n') WHEN (customproperty10 is NULL OR customproperty10='') then CONCAT( 'S', RIGHT( CONCAT('00000000',userkey), 9), 'tst') else concat(customproperty10,'tst') END)",
"UPDATE NEWUSERDATA SET USERNAME = SYSTEMUSERNAME",
"UPDATE NEWUSERDATA SET userSource = '4'",
"UPDATE NEWUSERDATA new SET email = REPLACE(email,'@','.tst@')",
"UPDATE NEWUSERDATA new, CURRENTUSERS u SET new.customproperty1 = CONCAT_WS(',', 'Staff', u.customproperty1) WHERE new.employeeid = u.employeeid",
"UPDATE NEWUSERDATA new, CURRENTUSERS u SET new.customproperty1 = u.customproperty1 WHERE new.employeeid = u.employeeid AND FIND_IN_SET('Staff', u.customproperty1) > 0",
"UPDATE NEWUSERDATA SET customproperty1 = 'Staff' WHERE customproperty1 IS NULL OR customproperty1 = ''",
"UPDATE NEWUSERDATA SET customproperty2 = CASE WHEN employeetype IN ('XX','YY') THEN 'ABC' ELSE 'YYY' END ",
"UPDATE NEWUSERDATA SET customproperty3 = customproperty2",
"UPDATE NEWUSERDATA SET statuskey=1 WHERE ((statuskey=0) and (DATEDIFF(CURDATE(), customproperty16) BETWEEN -14 AND 0))"
]
}
Could you please let me know what could be the issue or how to achieve the usecase?
Thanks!
Solved! Go to Solution.
11/10/2023 08:39 AM
@Bharadwaj : Rules mentioned in global config will only trigger if user is created through Saviynt UI or API. Same will not trigger in case of import.
If you need to generate the same through import you need to implement the same logic in inline preprocessor which you have already tried as per your comment but can you tell me what is the behaviour or issue you saw with second json you shared?
Also can you modify the update statement you have used to assign username to same as systemusername instead of directly assigning systemusername
Also I believe you are still testing the approach, If you are trying to productionalize this then you may have to change your update statements such way that generate the systemusername and username for only new users whereas for existing users you need to map value for current user state.
11/24/2023 02:07 AM - edited 11/24/2023 03:04 AM
Hi @Saathvik ,
I am not sure if the first statement is correct, " Rules mentioned in global config will only trigger if user is created through Saviynt UI or API. Same will not trigger in case of import." , because it worked for us.
We have changed the query based on the new improvements, but data is not getting updated. Job status shows successful, but we do not see any data getting updated.
Pre-Processor:
{
"ADDITIONALTABLES": {
"USERS": "SELECT USERKEY,username,employeeid,firstname,lastname,orgunitid,companyname,customproperty1,customproperty10,customproperty23 FROM USERS"
},
"COMPUTEDCOLUMNS": [
"username",
"systemusername",
"email",
"manager",
"userSource",
"orgunitid",
"customproperty10",
"customproperty1",
"customproperty2",
"customproperty3",
"customproperty23",
"statuskey"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA N LEFT JOIN CURRENTUSERS CU ON N.USERNAME=CU.USERNAME SET N.USERNAME = (CASE WHEN (N.username = NULL or N.username='') THEN (CASE WHEN (N.companyname='XXXX' or N.orgunitid=50002926) and length(N.lastname)>=5 THEN replace(concat(substring(N.lastname,1,5),substring(N.firstname,1,2)),'ñ','n') WHEN (N.companyname='XXXX' or N.orgunitid=50002926) and length(N.lastname)<5 THEN replace(concat(substring(N.lastname,1,5),substring(N.firstname,1,7-LENGTH(replace(N.lastname,'ñ','n')))),'ñ','n') WHEN (N.companyname='XXXX' or N.orgunitid=50002926) and length(N.lastname)>=5 THEN replace(concat(substring(N.lastname,1,5),substring(N.firstname,1,1),1),'ñ','n') WHEN (N.companyname='XXXX' or N.orgunitid=50002926) and length(N.lastname)<5 THEN replace(concat(substring(N.lastname,1,5),substring(N.firstname,1,7-LENGTH(replace(N.lastname,'ñ','n'))),1),'ñ','n') WHEN (N.companyname='XXXX' or N.orgunitid=50002926) and length(N.lastname)>=5 THEN replace(concat(substring(N.lastname,1,5),substring(N.firstname,1,1),2),'ñ','n') WHEN (N.companyname='XXXX' or N.orgunitid=50002926) and length(N.lastname)<5 THEN replace(concat(substring(N.lastname,1,5),substring(N.firstname,1,7-LENGTH(replace(N.lastname,'ñ','n'))),2),'ñ','n') WHEN (N.companyname='XXXX' or N.orgunitid=50002926) and length(N.lastname)>=5 THEN replace(concat(substring(N.lastname,1,5),substring(N.firstname,1,1),3),'ñ','n') WHEN (N.companyname='XXXX' or N.orgunitid=50002926) and length(N.lastname)<5 THEN replace(concat(substring(N.lastname,1,5),substring(N.firstname,1,7-LENGTH(replace(N.lastname,'ñ','n'))),3),'ñ','n') WHEN (N.companyname='XXXX' or N.orgunitid=50002926) and length(N.lastname)>=5 THEN replace(concat(substring(N.lastname,1,5),substring(N.firstname,1,1),4),'ñ','n') WHEN (N.companyname='XXXX' or N.orgunitid=50002926) and length(N.lastname)<5 THEN replace(concat(substring(N.lastname,1,5),substring(N.firstname,1,7-LENGTH(replace(N.lastname,'ñ','n'))),4),'ñ','n') WHEN (N.companyname='XXXX' or N.orgunitid=50002926) and length(N.lastname)>=5 THEN replace(concat(substring(N.lastname,1,5),substring(N.firstname,1,1),5),'ñ','n') WHEN (N.companyname='XXXX' or N.orgunitid=50002926) and length(N.lastname)<5 THEN replace(concat(substring(N.lastname,1,5),substring(N.firstname,1,7-LENGTH(replace(N.lastname,'ñ','n'))),5),'ñ','n') WHEN (N.companyname='XXXX' or N.orgunitid=50002926) and length(N.lastname)>=5 THEN replace(concat(substring(N.lastname,1,5),substring(N.firstname,1,1),6),'ñ','n') WHEN (N.companyname='XXXX' or N.orgunitid=50002926) and length(N.lastname)<5 THEN replace(concat(substring(N.lastname,1,5),substring(N.firstname,1,7-LENGTH(replace(N.lastname,'ñ','n'))),6),'ñ','n') WHEN (N.companyname='XXXX' or N.orgunitid=50002926) and length(N.lastname)>=5 THEN replace(concat(substring(N.lastname,1,5),substring(N.firstname,1,1),7),'ñ','n') WHEN (N.companyname='XXXX' or N.orgunitid=50002926) and length(N.lastname)<5 THEN replace(concat(substring(N.lastname,1,5),substring(N.firstname,1,7-LENGTH(replace(N.lastname,'ñ','n'))),7),'ñ','n') WHEN (N.companyname='XXXX' or N.orgunitid=50002926) and length(N.lastname)>=5 THEN replace(concat(substring(N.lastname,1,5),substring(N.firstname,1,1),8),'ñ','n') WHEN (N.companyname='XXXX' or N.orgunitid=50002926) and length(N.lastname)<5 THEN replace(concat(substring(N.lastname,1,5),substring(N.firstname,1,7-LENGTH(replace(N.lastname,'ñ','n'))),8),'ñ','n') WHEN (N.companyname='XXXX' or N.orgunitid=50002926) and length(N.lastname)>=5 THEN replace(concat(substring(N.lastname,1,5),substring(N.firstname,1,1),9),'ñ','n') WHEN (N.companyname='XXXX' or N.orgunitid=50002926) and length(N.lastname)<5 THEN replace(concat(substring(N.lastname,1,5),substring(N.firstname,1,7-LENGTH(replace(N.lastname,'ñ','n'))),9),'ñ','n') WHEN (N.customproperty10 is NULL OR N.customproperty10='') THEN CONCAT( 'S', RIGHT( CONCAT('00000000',CU.userkey), 9), 'tst') else concat(N.customproperty10,'tst') END) else N.username END)",
"UPDATE NEWUSERDATA SET SYSTEMUSERNAME = USERNAME",
"UPDATE NEWUSERDATA SET userSource = '4'",
"UPDATE NEWUSERDATA SET customproperty23= 'internal'",
"UPDATE NEWUSERDATA new SET email = REPLACE(email,'@','.tst@')",
"UPDATE NEWUSERDATA new, CURRENTUSERS u SET new.customproperty1 = 'XXXX Staff' WHERE (new.customproperty1 IS NULL OR new.customproperty1 = '')",
"UPDATE NEWUSERDATA new, CURRENTUSERS u SET new.customproperty2 = CASE WHEN new.employeetype IN ('10','11','20','40') THEN 'GSM' ELSE 'GNSM' END ",
"UPDATE NEWUSERDATA new, CURRENTUSERS u SET new.customproperty3 = new.customproperty2",
"UPDATE NEWUSERDATA new, CURRENTUSERS u SET new.statuskey=1 WHERE ((new.statuskey=0) and (DATEDIFF(CURDATE(), new.customproperty16) BETWEEN -14 AND 0))",
"DELETE FROM NEWUSERDATA where statuskey = 0 and username IN (SELECT username FROM (SELECT username FROM NEWUSERDATA GROUP BY username HAVING COUNT(*) > 1 and SUM(statuskey) = 1) tmp)",
"DELETE FROM NEWUSERDATA where (username, enddate) NOT IN (SELECT username, enddate FROM (SELECT username, MAX(enddate) AS enddate FROM NEWUSERDATA GROUP BY username HAVING COUNT(*) > 1 and SUM(statuskey) = 0) tmp1) and username IN (SELECT username FROM (SELECT username FROM NEWUSERDATA GROUP BY username HAVING COUNT(*) > 1 and SUM(statuskey) = 0) tmp2)",
"DELETE FROM NEWUSERDATA where username !='GRIGOGE'"
]
}
Job Logs:
Does not show how many users got updated, inserted, etc.
In the logs, we see an error message that "Not Importing user - either username attribute not found or if vendor is there then user doesnt have access to import the vendor-[null, , Alicia, ARROYO (interim), 2016-07-01, 2018-09-28, XXXX, 50002208, 00901036, 92, Personnel Administration Section, RS.1.3, 50002980, 00901036, null, 2016-07-01, 01, Interim, Interim, 00000000, 2018-09-28, 0, null, 4, XXXX Staff, GNSM, GNSM, internal]", hence not updating. However, our pre-processsor query should handle the scenario.
Could you please let us know how can this be fixed?
Thanks!
11/24/2023 03:28 AM - edited 11/24/2023 03:37 AM
Further update is that we could actually see in the logs that the user GRIGOGE is updated, but we do not see the change reflected. Also, we copied the same query that is in Production, but we do not see any updates rolling over to the user.
Log trace for reference:
11/28/2023 06:41 AM - edited 11/28/2023 06:43 AM
Hi,
Below query worked for us:
{
"ADDITIONALTABLES": {
"USERS": "SELECT USERKEY,username,employeeid,firstname,lastname,orgunitid,companyname,customproperty1,customproperty10,customproperty23 FROM USERS"
},
"COMPUTEDCOLUMNS": [
"username",
"systemusername",
"email",
"userSource",
"orgunitid",
"customproperty10",
"customproperty1",
"customproperty2",
"customproperty3",
"customproperty23",
"statuskey"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA N,CURRENTUSERS u SET N.username = (CASE WHEN (N.username is NULL or N.username='') THEN (CASE WHEN (N.companyname='XXXX' or N.orgunitid=0000) and length(N.lastname)>=5 THEN replace(concat(substring(N.lastname,1,5),substring(N.firstname,1,2)),'ñ','n') WHEN (N.companyname='XXXX' or N.orgunitid=0000) and length(N.lastname)<5 THEN replace(concat(substring(N.lastname,1,5),substring(N.firstname,1,7-LENGTH(replace(N.lastname,'ñ','n')))),'ñ','n') WHEN (N.companyname='XXXX' or N.orgunitid=0000) and length(N.lastname)>=5 THEN replace(concat(substring(N.lastname,1,5),substring(N.firstname,1,1),1),'ñ','n') WHEN (N.companyname='XXXX' or N.orgunitid=0000) and length(N.lastname)<5 THEN replace(concat(substring(N.lastname,1,5),substring(N.firstname,1,7-LENGTH(replace(N.lastname,'ñ','n'))),1),'ñ','n') WHEN (N.companyname='XXXX' or N.orgunitid=0000) and length(N.lastname)>=5 THEN replace(concat(substring(N.lastname,1,5),substring(N.firstname,1,1),2),'ñ','n') WHEN (N.companyname='XXXX' or N.orgunitid=0000) and length(N.lastname)<5 THEN replace(concat(substring(N.lastname,1,5),substring(N.firstname,1,7-LENGTH(replace(N.lastname,'ñ','n'))),2),'ñ','n') WHEN (N.companyname='XXXX' or N.orgunitid=0000) and length(N.lastname)>=5 THEN replace(concat(substring(N.lastname,1,5),substring(N.firstname,1,1),3),'ñ','n') WHEN (N.companyname='XXXX' or N.orgunitid=0000) and length(N.lastname)<5 THEN replace(concat(substring(N.lastname,1,5),substring(N.firstname,1,7-LENGTH(replace(N.lastname,'ñ','n'))),3),'ñ','n') WHEN (N.companyname='XXXX' or N.orgunitid=0000) and length(N.lastname)>=5 THEN replace(concat(substring(N.lastname,1,5),substring(N.firstname,1,1),4),'ñ','n') WHEN (N.companyname='XXXX' or N.orgunitid=0000) and length(N.lastname)<5 THEN replace(concat(substring(N.lastname,1,5),substring(N.firstname,1,7-LENGTH(replace(N.lastname,'ñ','n'))),4),'ñ','n') WHEN (N.companyname='XXXX' or N.orgunitid=0000) and length(N.lastname)>=5 THEN replace(concat(substring(N.lastname,1,5),substring(N.firstname,1,1),5),'ñ','n') WHEN (N.companyname='XXXX' or N.orgunitid=0000) and length(N.lastname)<5 THEN replace(concat(substring(N.lastname,1,5),substring(N.firstname,1,7-LENGTH(replace(N.lastname,'ñ','n'))),5),'ñ','n') WHEN (N.companyname='XXXX' or N.orgunitid=0000) and length(N.lastname)>=5 THEN replace(concat(substring(N.lastname,1,5),substring(N.firstname,1,1),6),'ñ','n') WHEN (N.companyname='XXXX' or N.orgunitid=0000) and length(N.lastname)<5 THEN replace(concat(substring(N.lastname,1,5),substring(N.firstname,1,7-LENGTH(replace(N.lastname,'ñ','n'))),6),'ñ','n') WHEN (N.companyname='XXXX' or N.orgunitid=0000) and length(N.lastname)>=5 THEN replace(concat(substring(N.lastname,1,5),substring(N.firstname,1,1),7),'ñ','n') WHEN (N.companyname='XXXX' or N.orgunitid=0000) and length(N.lastname)<5 THEN replace(concat(substring(N.lastname,1,5),substring(N.firstname,1,7-LENGTH(replace(N.lastname,'ñ','n'))),7),'ñ','n') WHEN (N.companyname='XXXX' or N.orgunitid=0000) and length(N.lastname)>=5 THEN replace(concat(substring(N.lastname,1,5),substring(N.firstname,1,1),8),'ñ','n') WHEN (N.companyname='XXXX' or N.orgunitid=0000) and length(N.lastname)<5 THEN replace(concat(substring(N.lastname,1,5),substring(N.firstname,1,7-LENGTH(replace(N.lastname,'ñ','n'))),8),'ñ','n') WHEN (N.companyname='XXXX' or N.orgunitid=0000) and length(N.lastname)>=5 THEN replace(concat(substring(N.lastname,1,5),substring(N.firstname,1,1),9),'ñ','n') WHEN (N.companyname='XXXX' or N.orgunitid=0000) and length(N.lastname)<5 THEN replace(concat(substring(N.lastname,1,5),substring(N.firstname,1,7-LENGTH(replace(N.lastname,'ñ','n'))),9),'ñ','n') WHEN (N.customproperty10 is NULL OR N.customproperty10='') THEN CONCAT( 'S', RIGHT( CONCAT('00000000',u.userkey), 9), 'tst') else concat(N.customproperty10,'tst') END) else N.username END)",
"UPDATE NEWUSERDATA SET SYSTEMUSERNAME = username",
"UPDATE NEWUSERDATA SET userSource = '4'",
"UPDATE NEWUSERDATA SET customproperty23 = 'internal'",
"UPDATE NEWUSERDATA new SET email = REPLACE(email,'@','.tst@')",
"UPDATE NEWUSERDATA new, CURRENTUSERS u SET new.customproperty1 = CONCAT_WS(',', 'XXXX Staff', u.customproperty1) WHERE new.employeeid = u.employeeid",
"UPDATE NEWUSERDATA new, CURRENTUSERS u SET new.customproperty1 = u.customproperty1 WHERE new.employeeid = u.employeeid AND FIND_IN_SET('XXXX Staff', u.customproperty1) > 0",
"UPDATE NEWUSERDATA SET customproperty1 = 'XXXX Staff' WHERE customproperty1 IS NULL OR customproperty1 = ''",
"UPDATE NEWUSERDATA SET customproperty3 = customproperty2",
"UPDATE NEWUSERDATA new, CURRENTUSERS u SET new.statuskey=1 WHERE ((new.statuskey=0) and (DATEDIFF(CURDATE(), new.customproperty16) BETWEEN -14 AND 0))",
"DELETE FROM NEWUSERDATA where statuskey = 0 and username IN (SELECT username FROM (SELECT username FROM NEWUSERDATA GROUP BY username HAVING COUNT(*) > 1 and SUM(statuskey) = 1) tmp)",
"DELETE FROM NEWUSERDATA where (username, enddate) NOT IN (SELECT username, enddate FROM (SELECT username, MAX(enddate) AS enddate FROM NEWUSERDATA GROUP BY username HAVING COUNT(*) > 1 and SUM(statuskey) = 0) tmp1) and username IN (SELECT username FROM (SELECT username FROM NEWUSERDATA GROUP BY username HAVING COUNT(*) > 1 and SUM(statuskey) = 0) tmp2)"
]
}
Thanks!