username and systemusername not picking up from global config.

[Bharadwaj]

Hi,

We have a use case as mentioned below:

• SAP HR is the Auth source.
• All the new joiners coming from SAP will not have "username" and "systemusername" generated from source and we will generate it in Saviynt using the rules set in Global config. So, the users will have blank or null values against username.
• For existing users, we get the username and hence we use it in the mapping information (Importuserjson).
• Username is same as Systemusername.
• Systemusername has many conditions, as shown below.
• We have a pre-processor query setup at the connector level.
• Since we generate both username and systemusername in Saviynt, we have defined "check rules" as "Yes" and "Generate Systemusername" as "Yes" at the job level. 

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!

[sk]

@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.

[Bharadwaj]

Hi @sk , 

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!

[Bharadwaj]

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: 

2023-11-24T12:16:12+01:00-ecm-worker-services.ImportSAvDataUserService-quartzScheduler_Worker-1-9fc2v-DEBUG-record# 151 -- row ::: 28 :: 28

2023-11-24T12:16:12+01:00-ecm-worker-services.ImportSAvDataUserService-quartzScheduler_Worker-1-9fc2v-DEBUG-Proceed to Import for records - > [GRIGOGE, GEOGRIGXXXX@XXXXX, SDFFF, FDFDD, 2020-01-01, 9999-12-31, XXXX, 50001375, 00319260, 10, IT Development, Security & Operations Se, RS.2.2, 50001378, 00319260, IT Business Partner / IT Administrator, 2009-04-01, 02, Scrum Master, Scrum Master, DESERJtst, 9999-12-31, 1, GRIGOGE, 3, XXXX Staff,DIAS Team,Management Board, GSM, GSM, internal] : true

2023-11-24T12:16:12+01:00-ecm-worker-services.ImportSAvDataUserService-quartzScheduler_Worker-1-9fc2v-DEBUG-user not found in map.. fetching..

2023-11-24T12:16:12+01:00-ecm-worker-services.ImportSAvDataUserService-quartzScheduler_Worker-1-9fc2v-DEBUG-Executing query select userkey as userkey from users where username = :reconvalue , reconvalue=GRIGOGE

2023-11-24T12:16:12+01:00-ecm-worker-services.ImportSAvDataUserService-quartzScheduler_Worker-1-9fc2v-DEBUG-found userkey = 27873 for GRIGOGE

2023-11-24T12:16:12+01:00-ecm-worker-services.ImportSAvDataUserService-quartzScheduler_Worker-1-9fc2v-DEBUG-Records updated: 1634

2023-11-24T12:16:12+01:00-ecm-worker-services.ImportSAvDataUserService-quartzScheduler_Worker-1-9fc2v-DEBUG-Processing query..

2023-11-24T12:16:12+01:00-ecm-worker-services.ImportSAvDataUserService-quartzScheduler_Worker-1-9fc2v-DEBUG-Records updated: 26

2023-11-24T12:16:12+01:00-ecm-worker-services.ImportSAvDataUserService-quartzScheduler_Worker-1-9fc2v-DEBUG-Processing query..

2023-11-24T12:16:12+01:00-ecm-worker-services.ImportSAvDataUserService-quartzScheduler_Worker-1-9fc2v-DEBUG-Records updated: 1660

2023-11-24T12:16:12+01:00-ecm-worker-services.ImportSAvDataUserService-quartzScheduler_Worker-1-9fc2v-DEBUG-Processing query..

2023-11-24T12:16:12+01:00-ecm-worker-services.ImportSAvDataUserService-quartzScheduler_Worker-1-9fc2v-DEBUG-Records updated: 1660

2023-11-24T12:16:12+01:00-ecm-worker-services.ImportSAvDataUserService-quartzScheduler_Worker-1-9fc2v-DEBUG-Processing query..

2023-11-24T12:16:12+01:00-ecm-worker-services.ImportSAvDataUserService-quartzScheduler_Worker-1-9fc2v-DEBUG-Records updated: 1

2023-11-24T12:16:12+01:00-ecm-worker-services.ImportSAvDataUserService-quartzScheduler_Worker-1-9fc2v-DEBUG-Processing query..

2023-11-24T12:16:12+01:00-ecm-worker-services.ImportSAvDataUserService-quartzScheduler_Worker-1-9fc2v-DEBUG-Records updated: 13

2023-11-24T12:16:12+01:00-ecm-worker-services.ImportSAvDataUserService-quartzScheduler_Worker-1-9fc2v-DEBUG-Processing query..

2023-11-24T12:16:12+01:00-ecm-worker-services.ImportSAvDataUserService-quartzScheduler_Worker-1-9fc2v-DEBUG-Records updated: 5

2023-11-24T12:16:12+01:00-ecm-worker-services.ImportSAvDataUserService-quartzScheduler_Worker-1-9fc2v-DEBUG-Processing query..

2023-11-24T12:16:12+01:00-ecm-worker-services.ImportSAvDataUserService-quartzScheduler_Worker-1-9fc2v-DEBUG-Records updated: 1491

2023-11-24T12:16:12+01:00-ecm-worker-services.ImportSAvDataUserService-quartzScheduler_Worker-1-9fc2v-DEBUG-Executing query to fetch the updated data list: SELECT USERNAME,EMAIL,FIRSTNAME,LASTNAME,STARTDATE,ENDDATE,COMPANYNAME,ORGUNITID,EMPLOYEEID,EMPLOYEETYPE,DEPARTMENTNAME,DEPARTMENTNUMBER,JOBCODE,CUSTOMPROPERTY10,CUSTOMPROPERTY15,CUSTOMPROPERTY16,CUSTOMPROPERTY17,JOBCODEDESC,JOBDESCRIPTION,MANAGER,TERMDATE,STATUSKEY,SYSTEMUSERNAME,USERSOURCE,CUSTOMPROPERTY1,CUSTOMPROPERTY2,CUSTOMPROPERTY3,CUSTOMPROPERTY23 FROM TEMPNEWUSERS_750591

2023-11-24T12:16:12+01:00-ecm-worker-services.ImportSAvDataUserService-quartzScheduler_Worker-1-9fc2v-DEBUG-Records fetched: 151

2023-11-24T12:16:12+01:00-ecm-worker-services.ImportSAvDataUserService-quartzScheduler_Worker-1-9fc2v-DEBUG-Start dropTempTables

2023-11-24T12:16:12+01:00-ecm-worker-services.ImportSAvDataUserService-quartzScheduler_Worker-1-9fc2v-DEBUG-Dropping table TEMPNEWUSERS_750591

2023-11-24T12:16:12+01:00-ecm-worker-services.ImportSAvDataUserService-quartzScheduler_Worker-1-9fc2v-DEBUG-Executing Qry: DROP TABLE IF EXISTS ssminlp.TEMPNEWUSERS_750591

2023-11-24T12:16:12+01:00-ecm-worker-services.ImportSAvDataUserService-quartzScheduler_Worker-1-9fc2v-DEBUG-Time taken to drop temp table TEMPNEWUSERS_750591 = 15 ms

2023-11-24T12:16:12+01:00-ecm-worker-services.ImportSAvDataUserService-quartzScheduler_Worker-1-9fc2v-DEBUG-Dropping table TEMPUSERS_750591

2023-11-24T12:16:12+01:00-ecm-worker-services.ImportSAvDataUserService-quartzScheduler_Worker-1-9fc2v-DEBUG-Executing Qry: DROP TABLE IF EXISTS ssminlp.TEMPUSERS_750591

2023-11-24T12:16:12+01:00-ecm-worker-services.ImportSAvDataUserService-quartzScheduler_Worker-1-9fc2v-DEBUG-Time taken to drop temp table TEMPUSERS_750591 = 16 ms

2023-11-24T12:16:12+01:00-ecm-worker-services.ImportSAvDataUserService-quartzScheduler_Worker-1-9fc2v-DEBUG-End dropTempTables

2023-11-24T12:16:12+01:00-ecm-worker-services.ImportSAvDataUserService-quartzScheduler_Worker-1-9fc2v-DEBUG-Total time taken to preprocess data = 85118 ms

ERROS IN LOGS:

2023-11-24T12:34:02+01:00-ecm-changeaction.UserChangeActionService-http-nio-8080-exec-4-l5j2h-DEBUG-Update Fields for User History Tabs : []

2023-11-24T12:34:01+01:00-ecm-security.LoginService-http-nio-8080-exec-41-vspqh-WARN-Error parsing lockout threshold as a number: null, setting lockout threshold to 0

2023-11-24T12:34:02+01:00-ecm--null-vspqh--java.lang.NumberFormatException: null at java.lang.Long.parseLong(Long.java:552) at java.lang.Long.valueOf(Long.java:803) at com.saviynt.utility.security.LoginService.getLockoutThresholdCount(LoginService.groovy:172) at com.saviynt.utility.security.LoginService.incrementFailedTriesAndLockUserAccountIfNecessary(LoginService.groovy:112) at com.saviynt.webservice.SaviyntRestAuthenticationFilter.doFilter(SaviyntRestAuthenticationFilter.groovy:129) at grails.plugin.springsecurity.web.authentication.logout.MutableLogoutFilter.doFilter(MutableLogoutFilter.java:62) at grails.plugin.springsecurity.web.SecurityRequestHolderFilter.doFilter(SecurityRequestHolderFilter.java:59) at com.mrhaki.grails.plugin.xframeoptions.web.XFrameOptionsFilter.doFilterInternal(XFrameOptionsFilter.java:69) at com.brandseye.cors.CorsFilter.doFilter(CorsFilter.java:82) at java.lang.Thread.run(Thread.java:750)

2023-11-24T12:34:01+01:00-ecm-changeaction.UserChangeActionService-http-nio-8080-exec-41-vspqh-DEBUG-Update Fields for User History Tabs : []

2023-11-24T12:34:01+01:00-ecm-changeaction.UserChangeActionService-http-nio-8080-exec-41-vspqh-DEBUG-inlineeval = false

2023-11-24T12:34:00+01:00-ecm-security.LoginService-http-nio-8080-exec-32-vspqh-WARN-Error parsing lockout threshold as a number: null, setting lockout threshold to 0

2023-11-24T12:34:00+01:00-ecm--null-vspqh--java.lang.NumberFormatException: null at java.lang.Long.parseLong(Long.java:552) at java.lang.Long.valueOf(Long.java:803) at com.saviynt.utility.security.LoginService.getLockoutThresholdCount(LoginService.groovy:172) at com.saviynt.utility.security.LoginService.incrementFailedTriesAndLockUserAccountIfNecessary(LoginService.groovy:112) at com.saviynt.webservice.SaviyntRestAuthenticationFilter.doFilter(SaviyntRestAuthenticationFilter.groovy:129) at grails.plugin.springsecurity.web.authentication.logout.MutableLogoutFilter.doFilter(MutableLogoutFilter.java:62) at grails.plugin.springsecurity.web.SecurityRequestHolderFilter.doFilter(SecurityRequestHolderFilter.java:59) at com.mrhaki.grails.plugin.xframeoptions.web.XFrameOptionsFilter.doFilterInternal(XFrameOptionsFilter.java:69) at com.brandseye.cors.CorsFilter.doFilter(CorsFilter.java:82) at java.lang.Thread.run(Thread.java:750)

2023-11-24T12:34:00+01:00-ecm-changeaction.UserChangeActionService-http-nio-8080-exec-32-vspqh-DEBUG-Update Fields for User History Tabs : []

2023-11-24T12:34:00+01:00-ecm-changeaction.UserChangeActionService-http-nio-8080-exec-32-vspqh-DEBUG-inlineeval = false

Could it be that the connector is not able to update EIC Saviynt DB?

Thanks!

[Bharadwaj · Accepted solution]

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!