username must be unchangable.

[Roua]

Hello,
We need to ensure that once a username is set, it cannot be changed either through the Saviynt UI or the endpoint system (HCM). The username should remain the same even if the Personnel number (Persno)changes. The username must not be affected by any changes. It should only be possible to fill in the first name if it was initially empty, and after that, no further changes should be allowed.
the original ImportUserJSON:

 "username": "d:Persno~#~char",

Original MODIFYUSERDATAJSON:

{
  "ADDITIONALTABLES": {
    "USERS": "SELECT customproperty2, customproperty11, customproperty9, lastname, firstname, departmentname, employeeType, Orgunitid, customproperty4, country, statuskey, customproperty6, customproperty5, username, startdate, enddate, customproperty3, manager, customer, customproperty14 FROM USERS",
    "CUSTOMER": "SELECT customername from customer"
  },
  "COMPUTEDCOLUMNS": [
    "customproperty2",
    "customproperty11",
    "customproperty9",
    "displayname",
    "Orgunitid",
    "username",
    "statuskey",
    "customproperty6",
    "startdate",
    "enddate",
    "statsukey",
    "manager",
    "customproperty3",
    "customer",
    "customproperty14",
    "customproperty15"
  ],
  "PREPROCESSQUERIES": [ "UPDATE NEWUSERDATA NU SET NU.username = CONCAT((NU.country), NU.username)",
    "UPDATE NEWUSERDATA NU SET NU.manager= CONCAT((NU.country), NU.manager)",
    "UPDATE NEWUSERDATA NU SET NU.statuskey = CASE WHEN NU.customproperty5 IN ('P', 'A') THEN 1 WHEN NU.customproperty5 = 'I' THEN 0 END",
    "UPDATE NEWUSERDATA NU SET NU.CUSTOMPROPERTY6 = CASE WHEN NU.statuskey = 1 AND NU.customproperty5 = 'P' THEN CURDATE() WHEN NU.statuskey = 0 THEN CURDATE() ELSE NU.CUSTOMPROPERTY6 END",
    "UPDATE NEWUSERDATA NU SET NU.startdate = DATE_FORMAT(STR_TO_DATE(NU.startdate, '%Y%m%d'), '%b %d, %Y')",
    "UPDATE NEWUSERDATA NU SET NU.enddate = DATE_FORMAT(STR_TO_DATE(NU.enddate, '%Y%m%d'), '%b %d, %Y')",
    "UPDATE NEWUSERDATA NU SET NU.customproperty3= NU.username",
 ]
}

Proposed Approach:
Ensure that the username is only populated if it is currently empty by checking the existing value before setting a new one.
in ImportUserJSON:

 "username": "IFNULL(d:Persno, '')"

in MODIFYUSERDATAJSON:

 "UPDATE NEWUSERDATA NU SET NU.username = IF(NU.username IS NULL OR NU.username = '', CONCAT((NU.country), NU.username), NU.username)"

Is this approach feasible? Has anyone implemented something similar, or is there a better way to achieve this? any recommendations?

UPDATE: here is another logic to follow: 
by updating MODIFYUSERDATAJSON only:

 "UPDATE NEWUSERDATA NU SET NU.username = CASE WHEN NU.username IS NULL OR NU.username = '' THEN CONCAT((NU.country), NU.username) ELSE NU.username END",

[NM]

Hi @Roua ,

"UPDATE NEWUSERDATA NU left join CURRENTUSER CU SET NU.username = CASE WHEN NU.username IS NULL OR NU.username = '' THEN CONCAT((NU.country), NU.username) ELSE CU.username END",

[Roua]

Thank you!
i am getting the error : Error in Users Import - Error while processing data: Table 'ssminlp.currentuser' doesn't exist

although i have it already in the following: 
{
"ADDITIONALTABLES": {
"USERS": "SELECT customproperty2, customproperty11, customproperty9, lastname, firstname, departmentname, employeeType, Orgunitid, customproperty4, country, statuskey, customproperty6, customproperty5, username, startdate, enddate, customproperty3, manager, customer, customproperty14, customproperty28 FROM USERS",
"CUSTOMER": "SELECT customername from customer",
"CURRENTUSER": "SELECT username FROM USERS"
},

if the currentuser table is not in the database it can't be treated as a temporary table or a concept similar to NEWUSERDATA ? 

[Roua]

We have modified the logic slightly. Now, the username received from HCM is assigned to customproperty28 to save the incoming information. In the MODIFYUSERDATAJSON, we compare the actual username in Saviynt to check if it is empty. If it is empty, it will be updated with the value from customproperty28; if it is not empty, it will remain unchanged.

 "customproperty28": "d:Persno~#~char",

{
  "ADDITIONALTABLES": {
    "USERS": "SELECT customproperty2, customproperty11, customproperty9, lastname, firstname, departmentname, employeeType, Orgunitid, customproperty4, country, statuskey, customproperty6, customproperty5, username, startdate, enddate, customproperty3, manager, customer, customproperty14, customproperty28 FROM USERS",
    "CUSTOMER": "SELECT customername from customer",
"CURRENTUSER": "SELECT username FROM USERS"
  },

"UPDATE NEWUSERDATA NU LEFT JOIN CURRENTUSER CU ON NU.username = CU.username SET NU.username = CASE WHEN NU.username IS NULL OR NU.username = '' THEN CONCAT(NU.country, NU.customproperty28) ELSE CU.username END",

[NM]

Hi @Roua , you don't have to define current users table in additional tables.. users table work it is just you have to access it with the name currentusers

[Roua]

Ok, i tried as you said without defining a table, i still got this error: 
Error in Users Import - Error while processing data: Table 'ssminlp.currentuser' doesn't exist

the current code: 

"UPDATE NEWUSERDATA NU LEFT JOIN CURRENTUSER CU ON NU.username = CU.username SET NU.username = CASE WHEN NU.username IS NULL OR NU.username = '' THEN CONCAT(NU.country, NU.customproperty28) ELSE CU.username END",

[rushikeshvartak]

It should be 

CURRENTUSERS

[NM]

@Roua try CURRENTUSERS

[Roua]

Thank you for addressing this detail! it worked!
but the logic of keeping the original username no matter where it would get changed in this test case we changed it in HCM endpoint, after running the import job, the new username from HCM  was saved to the customproperty28 BUT the original username was also changed in this case it must remain the same since it is not empty.

the logic i am using: 

"UPDATE NEWUSERDATA NU LEFT JOIN CURRENTUSERS CU ON NU.username = CU.username SET NU.username = CASE WHEN NU.username IS NULL OR NU.username = '' THEN CONCAT(NU.country, NU.customproperty28) ELSE CU.username END",
 

 "customproperty28": "d:Persno~#~char",

@NM @rushikeshvartak  

[rushikeshvartak]

Username is primary attribute for update. If you want to change username then use REST connector and update username using sav4sav

[Roua]

can you please elaborate so i can understand it better, We need to implement that the username received from HCM is assigned to customproperty28 to save the incoming information. In the MODIFYUSERDATAJSON, we compare the actual username in Saviynt to check if it is empty. If it is empty, it will be updated with the value from customproperty28; if it is not empty, it will remain unchanged.


before the logic of the customproperty28, i tried to just focus on the username: 

in ImportUserJSON:

 "username": "IFNULL(d:Persno, '')"

in MODIFYUSERDATAJSON:

 "UPDATE NEWUSERDATA NU SET NU.username = IF(NU.username IS NULL OR NU.username = '', CONCAT((NU.country), NU.username), NU.username)"

and it worked by the fact that username didn't get updated to the new one in HCM

[rushikeshvartak]

Refer https://forums.saviynt.com/t5/identity-governance/issue-in-update-user-json/m-p/101088

[Roua]

thank you, i followed the post and did as the following: 

"UPDATE NEWUSERDATA NU LEFT JOIN CURRENTUSERS CU ON NU.username = CU.username SET NU.username = CASE WHEN NU.username IS NULL OR NU.username = '' THEN CONCAT(NU.country, NU.customproperty28) ELSE CU.username END",

with dynamically constructing the SQL update condition to ensure username is only updated when necessary..
but still it didn't work 
do you have an idea ? 

[NM]

Hi @Roua did it not update, what issue do you see?

[Roua]

it updatedthe CP28 , but the logic of keeping the original username no matter where it would get changed in this test case we changed it in HCM endpoint, after running the import job, the new username from HCM  was saved to the customproperty28 BUT the original username was also changed in this case it must remain the same since it is not empty.

let's imagine username in saviynt is = user42
Username in HCM endpoint they changed it to be = user95
(and it is not allowed we dont want the username to be updated)
so when we run the import job WE WANT TO GET:
CP28 = user95
and unsername in saviynt stays the same unchangable = user42

and if the username in saviynt was empty this will allow to get the username from CP28.

the issue here is that it got updated and i got the username also updated to user95 where it should have stayed as user42

[NM]

@Roua is there any other property we can use to join the 2 tables?

Emoloyeeid or any other unique identifier?

[Roua]

thank you @NM 
Yes there is and it is mapped as the following: 
 "customproperty21": "d:Perid~#~char",

[Roua]

do you mean we can do something like this: 

"UPDATE NEWUSERDATA NU LEFT JOIN (SELECT customproperty21, username, customproperty28 FROM USERS) CU ON NU.customproperty21 = CU.customproperty21 SET NU.username = CASE WHEN NU.username IS NULL OR NU.username = '' THEN CONCAT(NU.country, NU.customproperty28) ELSE CU.username END",

do you think it is correct? 

[NM]

UPDATE NEWUSERDATA NU LEFT JOIN CURRENTUSERS CU ON NU.cusrompriperty21 = CU.customproperty21 SET NU.username = CASE WHEN NU.username IS NULL OR NU.username = '' THEN CONCAT(NU.country, NU.customproperty28) ELSE CU.username END

[Roua]

Error in Users Import - Error while processing data: Unknown column 'NU.cusrompriperty21' in 'on clause'
should i try with CU ? 

[rushikeshvartak]

Share full JSON

[NM]

@Roua my bad.. typo it should be NU.customproperty21

[Roua]

@NM  @rushikeshvartak  
i tried to add NU.customproperty21

Failure msg

Error in Users Import - Error while processing data: Unknown column 'NU.cusrompriperty21' in 'on clause'

FULL MODIFYUSERDATAJSON:

{
  "ADDITIONALTABLES": {
    "USERS": "SELECT customproperty2, customproperty11, customproperty9, lastname, firstname, departmentname, employeeType, Orgunitid, customproperty4, country, statuskey, customproperty6, customproperty5, username, startdate, enddate, customproperty3, manager, customer, customproperty14, customproperty28, customproperty21  FROM USERS",
    "CUSTOMER": "SELECT customername from customer"
  },
  "COMPUTEDCOLUMNS": [
    "customproperty2",
    "customproperty11",
    "customproperty9",
    "displayname",
    "Orgunitid",
    "username",
    "statuskey",
    "customproperty6",
    "startdate",
    "enddate",
    "statsukey",
    "manager",
    "customproperty3",
    "customer",
    "customproperty14",
    "customproperty15"
  ],
  "PREPROCESSQUERIES": [
    "UPDATE NEWUSERDATA NU SET NU.customproperty2 = DATE_FORMAT(STR_TO_DATE(NU.customproperty2, '%Y%m%d'), '%d.%m.%Y')",
    "UPDATE NEWUSERDATA NU SET NU.customproperty11 = CASE WHEN NU.customproperty11 = '00000000' THEN NULL ELSE DATE_FORMAT(STR_TO_DATE(NU.customproperty11, '%Y%m%d'), '%d.%m.%Y') END",
    "UPDATE NEWUSERDATA NU SET NU.customproperty9 = CASE WHEN NU.customproperty9 = '00000000' THEN NULL END",
    "UPDATE NEWUSERDATA NU SET NU.displayname = CONCAT(UPPER(NU.lastname), ' ', NU.firstname, ' (', NU.displayname, IF(NU.employeeType = 'E', ' (EXT)', ''), ')')",
    "UPDATE NEWUSERDATA NU SET NU.Orgunitid = CONCAT(NU.customproperty4, '-', NU.Orgunitid, '')",
"UPDATE NEWUSERDATA NU LEFT JOIN CURRENTUSERS CU ON NU.cusrompriperty21 =  NU.customproperty21 SET NU.username = CASE WHEN NU.username IS NULL OR NU.username = '' THEN CONCAT(NU.country, NU.customproperty28) ELSE CU.username END",
    "UPDATE NEWUSERDATA NU SET NU.manager= CONCAT((NU.country), NU.manager)",
    "UPDATE NEWUSERDATA NU SET NU.statuskey = CASE WHEN NU.customproperty5 IN ('P', 'A') THEN 1 WHEN NU.customproperty5 = 'I' THEN 0 END",
    "UPDATE NEWUSERDATA NU SET NU.CUSTOMPROPERTY6 = CASE WHEN NU.statuskey = 1 AND NU.customproperty5 = 'P' THEN CURDATE() WHEN NU.statuskey = 0 THEN CURDATE() ELSE NU.CUSTOMPROPERTY6 END",
    "UPDATE NEWUSERDATA NU SET NU.startdate = DATE_FORMAT(STR_TO_DATE(NU.startdate, '%Y%m%d'), '%b %d, %Y')",
    "UPDATE NEWUSERDATA NU SET NU.enddate = DATE_FORMAT(STR_TO_DATE(NU.enddate, '%Y%m%d'), '%b %d, %Y')",
    "UPDATE NEWUSERDATA NU SET NU.customproperty3= NU.username",
    "UPDATE NEWUSERDATA NU SET NU.customer = (select customername from currentcustomer where currentcustomer.CUSTOMERNAME = NU.Orgunitid)",
    "UPDATE NEWUSERDATA NU SET NU.customproperty14= CASE WHEN NU.customproperty15 = '50404368' THEN 'Poolberater'  WHEN NU.customproperty15 = '50404373' THEN 'Projektberater' WHEN NU.customproperty15 = '50404374' THEN 'Fachberater' ELSE NULL END"

  ]
}

[NM]

Hi @Roua , it should be NU.CUSTOMPROPERTY21, There is a typo in your query

[Roua]

My bad i thought you meant the one with CU. 
Thank you 

[Roua]

hi @NM @rushikeshvartak ,
this still didn't work. and as an update, we said to let it be possible to be changed from the UI 
so basically when we change a username from the UI, when running the import job it must remain as it was from the ui and not get the new values from HCM eventhough the country or the persno might be changed.
and we must do the same with Account Name (customproperty3):

 "UPDATE NEWUSERDATA NU SET NU.customproperty3= NU.username",

should that be made through user update rule? since we need to specify that it is possible to change it from the UI but not from importing from HCM... but how do we specify that it won't get updated ? should i remove the preprocessing logic from MODIFYUSERDATAJSON and just make it in user update rule?