Auto increment username creation from import

[sampath18]

Hi,

We have a requirement that we need to create the username based on logic "RTN-"+First letter of user first name+First letter of user last name+3 digit number starting with 001 like RTN-SR001 but if this already exist in saviynt then the username will be RTN-SR002 then RTN-SR003 and so on .Using below Modifyuserdatajson I am able to do the increment based on existing username but at every import its incrementing for the same users.

{
"ADDITIONALTABLES": {
"USERS": "SELECT username,SYSTEMUSERNAME,lastname,firstname,employeeid from USERS"
},
"COMPUTEDCOLUMNS": [
"username",
"SYSTEMUSERNAME"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET username = CONCAT('RTN-',UPPER(substring(NEWUSERDATA.firstname,1,1)),UPPER(substring(NEWUSERDATA.lastname,1,1)),(select LPAD(CAST(IFNULL(max(substring(cu.username,7,3)), '0' ) as UNSIGNED) +1, 3, '0') from currentusers cu where cu.username like 'RTN-%' and substring(cu.username,5,1)=substring(NEWUSERDATA.firstname,1,1) and substring(cu.username,6,1)=substring(NEWUSERDATA.lastname,1,1) and length(cu.username)=9))",
"UPDATE NEWUSERDATA SET SYSTEMUSERNAME = USERNAME"
]
}

I tried below also now no user is coming in the import.

{
"ADDITIONALTABLES": {
"USERS": "SELECT username,SYSTEMUSERNAME,lastname,firstname,employeeid from USERS"
},
"COMPUTEDCOLUMNS": [
"username",
"SYSTEMUSERNAME"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA join currentusers CU on cu.employeeid =NEWUSERDATA.employeeid SET NEWUSERDATA.username = (select IFNULL(cu.username, CONCAT('RTN-',UPPER(substring(NEWUSERDATA.firstname,1,1)),UPPER(substring(NEWUSERDATA.lastname,1,1)),(select LPAD(CAST(IFNULL(max(substring(cu1.username,7,3)), '0' ) as UNSIGNED) +1, 3, '0') from currentusers cu1 where cu1.username like 'RTN-%' and substring(cu1.username,5,1)=substring(NEWUSERDATA.firstname,1,1) and substring(cu1.username,6,1)=substring(NEWUSERDATA.lastname,1,1) and length(cu1.username)=9))))",
"UPDATE NEWUSERDATA SET SYSTEMUSERNAME = USERNAME"
]

{
"ADDITIONALTABLES": {
"USERS": "SELECT username,SYSTEMUSERNAME,lastname,firstname,employeeid from USERS"
},
"COMPUTEDCOLUMNS": [
"username",
"SYSTEMUSERNAME"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA as N JOIN CURRENTUSERS C on N.employeeid=C.employeeid SET N.username = CONCAT('RTN-',UPPER(substring(N.firstname,1,1)),UPPER(substring(N.lastname,1,1)),(select LPAD(CAST(IFNULL(max(substring(cu.username,7,3)), '0' ) as UNSIGNED) +1, 3, '0') from currentusers cu where cu.username like 'RTN-%' and substring(cu.username,5,1)=substring(N.firstname,1,1) and substring(cu.username,6,1)=substring(N.lastname,1,1) and length(cu.username)=9)) where C.username is null",
"UPDATE NEWUSERDATA SET SYSTEMUSERNAME = USERNAME"
]
}

{
"ADDITIONALTABLES": {
"USERS": "SELECT username,SYSTEMUSERNAME,lastname,firstname,employeeid from USERS"
},
"COMPUTEDCOLUMNS": [
"username",
"SYSTEMUSERNAME"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET username = (select IFNULL(cu.username, CONCAT('RTN-',UPPER(substring(NEWUSERDATA.firstname,1,1)),UPPER(substring(NEWUSERDATA.lastname,1,1)),(select LPAD(CAST(IFNULL(max(substring(cu.username,7,3)), '0' ) as UNSIGNED) +1, 3, '0') from currentusers cu where cu.username like 'RTN-%' and substring(cu.username,5,1)=substring(NEWUSERDATA.firstname,1,1) and substring(cu.username,6,1)=substring(NEWUSERDATA.lastname,1,1) and length(cu.username)=9))) from currentusers cu where cu.employeeid =NEWUSERDATA.employeeid)",
"UPDATE NEWUSERDATA SET SYSTEMUSERNAME = USERNAME"
]
}

[AmitM]

HI @sampath18 , Saviynt ootb DB function - https://docs.saviyntcloud.com/bundle/EIC-Admin-v2022x/page/Content/Chapter02-Identity-Repository/Dat...

Thanks,

Amit

If this answers your query, Please ACCEPT SOLUTION and give KUDOS.

[sampath18]

Hi @AmitM 

Thankyou for your response. I am able to autoincrement using FN_EIC_SEQGEN in Add Register User Rule but we need to increment based on format of the username like if one user fullname is Ashok Gupta and Second user fullname is Anil Kumar then we need username of Ashok Gupta as RTN-AG001 and for Anil Kumar RTN-Ak001 but for Anil Kumar it generated as RTN-AK002 . How can we achieve this.

CASE
WHEN users.employeeType = 'Results Talent Network' THEN
CONCAT("RTN-",
UPPER(SUBSTRING(users.firstname, 1, 1)),
UPPER(SUBSTRING(users.lastname, 1, 1)),
RIGHT(CONCAT('000', FN_EIC_SEQGEN('username')), 3))
END

[rushikeshvartak]

CASE
WHEN users.employeeType = 'Results Talent Network' THEN
CONCAT("RTN-",
UPPER(SUBSTRING(users.firstname, 1, 1)),
UPPER(SUBSTRING(users.lastname, 1, 1)),
RIGHT(CONCAT('000', FN_EIC_SEQGEN('RTN-' || UPPER(SUBSTRING(users.firstname, 1, 1)) || UPPER(SUBSTRING(users.lastname, 1, 1)))), 3))
END

[sampath18]

Hi @rushikeshvartak I am getting this as a username now after using your format.

[Manu269]

Can you try changing user modification workflow as AutoApprove?

[sampath18]

Hi @rushikeshvartak any update? 

[amit_krishnajit]

Hi @sampath18 

This scenario cannot be achieved using SQL queries as the same named users might be present in the feed at the same time. 

The solution for this would be to invoke a custom jar from the preprocessor and use the java code to form your logic. You may use a SQL query to identify all the users for which you may want to generate the username and then in the java code you can form your logic to generate the username and update that into the newuserdata table in the jar itself. 

Thanks