User Import Preprocessor is not applying the condition

[jdoma]

Hello Forum,

We have implemented preprocessor logic where it will update a unique systemusername whenever there is a change in either firstname or lastname and it is generating a unique systemusername for all the users whose firstname/lastname not changed.

We have used where clause as below:

{
"ADDITIONALTABLES":{"USERS": "SELECT USERKEY,USERNAME,SYSTEMUSERNAME,EMAIL,FIRSTNAME,LASTNAME FROM USERS"},
"COMPUTEDCOLUMNS":["systemusername","email"],
"PREPROCESSQUERIES":["UPDATE NEWUSERDATA SET systemusername=<<Logic to generate unique value>> WHERE NEWUSERDATA.FIRSTNAME!=FIRSTNAME OR NEWUSERDATA.LASTNAME!=LASTNAME"]}

I've tried to declare currentusers.firstname!=newuserdata.firstname but it is not identifying currentusers.firstname.

[rushikeshvartak]

update NEWUSERDATA nu left join CURRENTUSERS cu on nu.username = cu.username set nu.systemusername = <<>> WHERE nu.FIRSTNAME!=cu.FIRSTNAME OR nu.LASTNAME!=cu.LASTNAME

[jdoma]

Thank you Rushi for your response. I have tried this option but no luck. I still the user's systemusername update for users whose firstname/lastname not changed. They got a new systemusername.

{"ADDITIONALTABLES":{"USERS": "SELECT USERKEY,USERNAME,SYSTEMUSERNAME,EMAIL,FIRSTNAME,LASTNAME FROM USERS"},
"COMPUTEDCOLUMNS":["systemusername","email"],
"PREPROCESSQUERIES":[
"UPDATE NEWUSERDATA nu LEFT JOIN CURRENTUSERS cu ON nu.USERNAME = cu.USERNAME SET nu.systemusername = <<<<LOGIC>>>> WHERE nu.FIRSTNAME != cu.FIRSTNAME OR nu.LASTNAME != cu.LASTNAME"]}

[rushikeshvartak]

Try with firstname condition 

[sk]

Hey do you generate systemusername for new users as well using preprocessor logic? If not then then try to do inner join instead of left join

UPDATE NEWUSERDATA NU INNER JOIN CURRENTUSERS CU ON NU.USERNAME = CU.USERNAME SET NU.systemusername = <<<<LOGIC>>>> WHERE NU.FIRSTNAME <> CU.FIRSTNAME OR NU.LASTNAME <> CU.LASTNAME

Also another thing I noticed is in COMPUTEDCOLUMNS you have systemusername and email. Are you updating email filed as well as part pre-processor? If not then try to remove email and just keep systemusername

[jdoma]

Thank you for your response. We have tried the INNER join option but still the systemusername is updating for all the users instead of users whose first/lastname changed.

[sk]

1. Can you please share the screenshot of one user whose systemusername is updated though there is no changes in first/lastname?
2. Please share the ImportUserJSON config for respective connection
3. How are you generating systemusername for new users? What is your logic for generating systemusername for new users?

[jdoma]

Hi SK, thank you for the response. We are generating systemusername for new users using SystemUserName generation rule at Global Configurations. 

<dataMapping>
<sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="username">
<![CDATA[
EXEC dbo.usp_IGA_Employees_Dev;
]]>
</sql-query>
<importsettings>
<zeroDayProvisioning>true</zeroDayProvisioning>
<generateEmail>true</generateEmail>
<userNotInFileAction>NOACTION</userNotInFileAction>
<checkRules>true</checkRules>
<buildUserMap>false</buildUserMap>
<generateSystemUsername>true</generateSystemUsername>
<userReconcillationField>username</userReconcillationField>
</importsettings>
<mapper description="This is the mapping field for Saviynt Field name">
<mapfield saviyntproperty="username" sourceproperty="EmployeeCode" type="character"></mapfield>
<mapfield saviyntproperty="employeeid" sourceproperty="EmployeeCode" type="character"></mapfield>
<mapfield saviyntproperty="firstname" sourceproperty="Firstname" type="character"></mapfield>
<mapfield saviyntproperty="displayname" sourceproperty="Fullname" type="character"></mapfield>
<mapfield saviyntproperty="lastname" sourceproperty="Lastname" type="character"></mapfield>
<mapfield saviyntproperty="preferedFirstName" sourceproperty="PreferredName" type="character"></mapfield>
<mapfield saviyntproperty="statuskey" sourceproperty="Status" type="character"></mapfield>
<mapfield saviyntproperty="departmentname" sourceproperty="DepartmentDescription" type="character"></mapfield>
</mapper>
</dataMapping>

And the below user's systemusername changed to D-bardClary1 though there was no change in the firstname/lastname.

[sk]

So you are using DB Connector to import users. Also does that mean every time you running import all users get getting new systemusername who are getting imported through job or only specific users?

[jdoma]

Yes, we are using DB connector to import users and systemusername is generating for new users via rules. But when we apply the preprocessor logic using the query (previously given in the thread), the systemusername is getting updated for all users though we have added where condition.

[sk]

And it is happening all the times when you run import? 

Can you try below query

UPDATE NEWUSERDATA NU INNER JOIN CURRENTUSERS CU ON NU.USERNAME = CU.USERNAME SET NU.SYSTEMUSERNAME = CASE WHEN (NU.FIRSTNAME <> CU.FIRSTNAME OR NU.LASTNAME <> CU.LASTNAME) THEN <<<<LOGIC>>>> ELSE CU.SYSTEMUSERNAME END

[sk]

If it still behaves the same can you make below setting to false in import JSON and see what is the behaviour

<generateSystemUsername>false</generateSystemUsername>

[jdoma]

I have already played with this attribute and when we set it as false, it is clearing the systemusername attribute for the all the users whose name unchanged.

[sk]

Understood did you try the updated query I posted? 

UPDATE NEWUSERDATA NU INNER JOIN CURRENTUSERS CU ON NU.USERNAME = CU.USERNAME SET NU.SYSTEMUSERNAME = CASE WHEN (NU.FIRSTNAME <> CU.FIRSTNAME OR NU.LASTNAME <> CU.LASTNAME) THEN <<<<LOGIC>>>> ELSE CU.SYSTEMUSERNAME END

[jdoma]

We are thinking to use SavForSav DB connector to update systemusername using similar query as below but getting error as "SAV - Error while updating user - Exception occured while Excecuting Query". Even the logs could not help us much to identify the error. 

"update users set systemusername = case when substring(concat(replace('${user.firstname}',' ',''), replace('${user.lastname}',' ','')),1,20) not in (select systemusername from users where systemusername=substring(concat(replace('${user.firstname}', ' ',''), replace('${user.lastname}', ' ','')),1,20)) then substring(concat(replace('${user.firstname}',' ',''), replace('${user.lastname}', ' ','')),1,20) end where userkey = ${user.id}"

[rushikeshvartak]

Instead of DB try REST connector 

https://documenter.getpostman.com/view/1797923/SzKN22aV#5c313b06-3755-4bdd-be1b-05efb0f6a51b

[avinashchhetri]

@jdoma,

Using the pre-processor to achieve this functionality, in my professional opinion, is not the right approach for this problem statement. Even if this works, there is a significant overhead as you will have to check each and every user on every import, compare their names and generate a new systemUser Name.

A better approach would be to explore the SaviyntForSaviynt Connection and use the REST API's to update the systemUserName (if possible) or use the Custom Action in the user update rule and trigger a custom extension to generate the systemUserName. Custom Action has been discussed in this forum before : 

https://forums.saviynt.com/t5/identity-governance/using-custom-class-action-in-update-rule/m-p/15277