We are delighted to share our new EIC Delivery Methodology for efficiently managing Saviynt Implementations and delivering quick time to value. CLICK HERE.

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

PremMahadikar
New Contributor
New Contributor

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?

Regards,
Prem Mahadikar
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.
4 REPLIES 4

sai_sp
Saviynt Employee
Saviynt Employee

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
New Contributor
New Contributor

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

Regards,
Prem Mahadikar
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

PremMahadikar
New Contributor
New Contributor

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

Regards,
Prem Mahadikar
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

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