Couldn't use inner queries and Database function in preprocessor during import of users

[PremMahadikar]

Hi All,

We have a use-case to generate unique username and copy username to systemusername in Saviynt. Data is imported from connected HR SAP source. Currently we are using MODIFYUSERDATAJSON to run preprocessor queries. 

Use-case should cover:

1. username should be unique

2. username should have first 5 letters from 'lastname' and first 2 letters of 'firstname'

3. If username already exists, append the username with 1 and then 2 ...goes on

4. ñ should be replaced with n

5. username to be copied to systemusername

To replicate the use-case, we are using file import and passing preprocessor JSON to generate username and systemusername. We have tried many simple to complex query to generate unique username but only simple query works, but we couldn't achieve above use-case because Inner queries and DB functions aren't working.  Data from .csv file couldn't be imported, it always shows 0 records in file and 0 inserted.

csv File used:

firstname,lastname,startdate,enddate,customproperty17,customproperty1,employeeid,employeetype,username,companyname

"Test","ñjoy24","2023-11-15","","1","TestCP1","Testjoy24","test4","","<companyname>"

Query in preprocessor used:

"PREPROCESSQUERIES": [

    "UPDATE NEWUSERDATA SET USERNAME = (CASE WHEN companyname = '<companyname>' LENGTH(lastname) > 5 and SELECT replace(concat(substring(lastname, 1, 5), substring(firstname, 1, 2), 'tst'),'ñ','n') IN (SELECT distinct(u.username) from users u)= 0 then replace(concat(substring(lastname, 1, 5), substring(firstname, 1, 2)),'ñ','n')

WHEN companyname = '<companyname>' AND (LENGTH(lastname) > 5 and SELECT replace(concat(substring(lastname, 1, 5), substring(firstname, 1, 2), 'tst'),'ñ','n') IN (SELECT distinct(u.username) from users u)= 1 then replace(concat(substring(lastname, 1, 5), substring(firstname, 1, 2),FN_EIC_SEQGEN('increment')),'ñ','n') ELSE USERNAME END)",

    "UPDATE NEWUSERDATA SET SYSTEMUSERNAME = username",

]

Note: In Admin -> Identity Repository -> Dataset, 'increment' is defined

Saviynt version used: V23.6

Any suggestion to achieve this use case?

[sai_sp · Accepted solution]

Hi @PremMahadikar 

You can use the out of the box feature to generate username. Go to Admin > Global Configs > Identity Lifecycle > Register User Generation Rule / Username generation rule.

All this logic used in preprocessor can be configured in the UI itself. Same thing can be configured in System Username generation rule as well.

[PremMahadikar]

Hi @sai_sp ,

Okay. Once we set the configuration, how can we use this username in user import from HR system? Even during UI user import, we don't have an option like 'Generate username', how can we use Register User Generation Rule value?

Thanks,

Prem

[PremMahadikar]

Hi Darshan,

I have seen your best practice post on inline preprocessor. We have above usecase mentioned plus we also tried to use DB functions which is not working as expected (which is mentioned in your best practice solution that it works). I would need your help if you can let me know what's the correct method to use DB functions, case statements because we have given multiple tries and non are working?

The above suggestion from Sai, I believe it doesn't work for import of users through connectors or UI and only works while registering from UI.

Reference ticket: https://forums.saviynt.com/t5/user-import/best-practice-for-inline-preprocessing/ta-p/35948

Thanks,

Prem

[This post has been edited by a Moderator - we do not allow the tagging of employees or other forum users unless they have already been active on the forum thread.)

[Darshanjain · Accepted solution]

HI @PremMahadikar 

The above requirement can be achieved by Username generation rule as suggested by Sai, In user upload from UI, if username is passed as blank it will auto trigger the username, same works for User import from connectors as well.

Also on how we can use the functions, it still works try via simple logic and then start expanding the logic, Also when you have used the above use case of inline preprocessing you will get errors in the logs to see to where the sql is breaking, from that you should be able to understand and change it as per your requriement.

Thanks

Darshan