PREPROCESSQUERIES: Generate a Unique System Username based on the Company Name while importing

[Jayesh]

Hello,

I am looking to generate the system username based on the different company names which can be achieved in JSON pre-processing when importing all users from a CSV file

Scenarios:

1. System Username should be generated based on the first name, last name, and Company Name
2. Generated System username should be unique to that company 
3. If the System username already exists then it should auto-increment 

Example 1:

First Name: Manoj; Last Name: Yadav; Company Name: ABC

The expected System Username: Manoj.Yadav

Example 2:

First Name: Manoj; Last Name: Yadav; Company Name: DEF

The expected System Username: Manoj.Yadav

Example 3:

First Name: Manoj; Last Name: Yadav; Company Name: DEF

The expected System Username: Manoj.Yadav1

I tried Global Configuration for System UserName Generation which works for the form but doesn't work

JSON Query I am working.

{
"ADDITIONALTABLES": {
"USERS": "SELECT SYSTEMUSERNAME,firstname,lastname,employeeid,companyname from USERS"
},
"COMPUTEDCOLUMNS": [
"SYSTEMUSERNAME"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA as N JOIN USERS U on N.employeeid=U.employeeid SET SYSTEMUSERNAME = CASE
WHEN COMPANYNAME != 'ABC' AND COMPANYNAME != 'DEF' THEN CONCAT(firstname, lastname, '1')
ELSE SYSTEMUSERNAME",
"UPDATE NEWUSERDATA SET SYSTEMUSERNAME"
]
}

 Regards,\Jayesh

[rushikeshvartak]

What is error you are getting and how you are tetsing ? via import job or uploading user ?

[Jayesh]

Import job using a CSV file containing user details from the HR system. I am still working on JSON code which I already posted but need some help with validating duplicate system username and auto increment.

[rushikeshvartak]

Share logs in text file

[Jayesh]

I am not getting any error. It just says 0 records

csv.file

Code:

{
"ADDITIONALTABLES": {
"USERS": "SELECT SYSTEMUSERNAME, firstname, lastname, employeeid, COMPANYNAME FROM USERS"
},
"COMPUTEDCOLUMNS": [
"SYSTEMUSERNAME"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA AS N JOIN USERS U ON N.employeeid = U.employeeid SET SYSTEMUSERNAME = CONCAT(N.firstname, '.', N.lastname)",
"UPDATE NEWUSERDATA AS N SET SYSTEMUSERNAME = CONCAT(N.firstname, '.', N.lastname, '1') WHERE EXISTS (
SELECT 1 FROM USERS U WHERE U.SYSTEMUSERNAME = CONCAT(N.firstname, '.', N.lastname) AND U.COMPANYNAME = N.COMPANYNAME
)",
"UPDATE NEWUSERDATA AS N SET SYSTEMUSERNAME = CONCAT(N.firstname, '.', N.lastname, '2') WHERE EXISTS (
SELECT 1 FROM USERS U WHERE U.SYSTEMUSERNAME = CONCAT(N.firstname, '.', N.lastname, '1') AND U.COMPANYNAME = N.COMPANYNAME
)",
"UPDATE NEWUSERDATA AS N SET SYSTEMUSERNAME = CONCAT(N.firstname, '.', N.lastname, '3') WHERE EXISTS (
SELECT 1 FROM USERS U WHERE U.SYSTEMUSERNAME = CONCAT(N.firstname, '.', N.lastname, '2') AND U.COMPANYNAME = N.COMPANYNAME
)"
]
}

[rushikeshvartak]

Select reconciliation field during import

[Jayesh]

Still, it is zero records. Selected reconciliation field as EmployeeID

[rushikeshvartak]

Did you checked logs

[Jayesh]

Here you go.

[rushikeshvartak]

What error you have seen in logs. logs does not contain relevant logs

[Sampo]

You mentioned that the system usernames only need to be unique within a company, not in the Saviynt users table. I haven't tried to configure a rule to produce non-unique systemusername but you could try to use the system username generation rule instead of user import preprocessor, then you can use the FN_EIC_SEQGEN function to produce unique ids.

Something like this:

CASE WHEN CONCAT (firstname, ', ', lastname) NOT IN (SELECT systemusername FROM users existing WHERE existing.companyname = companyname)
THEN CONCAT (firstname, ', ', lastname)
ELSE CONCAT (firstname, ', ', lastname, FN_EIC_SEQGEN(CONCAT (firstname, ', ', lastname, companyname)))
END

best regards,

Sampo

[Jayesh]

Thank you, I tried the code below but the system username is not getting generated when I tried with the same firstname, lastname and company name. Any suggestions?

CASE WHEN CONCAT (firstname, '. ', lastname) NOT IN (SELECT systemusername FROM users existing WHERE existing.customer = '1')
THEN CONCAT (firstname, '. ', lastname)
ELSE CONCAT (firstname, '. ', lastname, FN_EIC_SEQGEN(CONCAT (firstname, '. ', lastname, '1')))
###
CASE WHEN CONCAT (firstname, '. ', lastname) NOT IN (SELECT systemusername FROM users existing WHERE existing.customer = '2')
THEN CONCAT (firstname, '. ', lastname)
ELSE CONCAT (firstname, '. ', lastname, FN_EIC_SEQGEN(CONCAT (firstname, '. ', lastname, '2')))
###
CASE WHEN CONCAT (firstname, '. ', lastname) NOT IN (SELECT systemusername FROM users existing WHERE existing.customer IS Null)
THEN CONCAT (firstname, '. ', lastname)
ELSE CONCAT (firstname, '. ', lastname, FN_EIC_SEQGEN(CONCAT (firstname, '. ', lastname, '1')))
END

[Sampo]

Hi Jayesh, it looks like you have multiple system username generation rules that are separated by ###

However only the last one contains the END statement that is required after each CASE - WHEN - THEN - ELSE statement, so you should add END to all the rules, otherwise they  fail because of invalid SQL.

You can check if the FN_EIC_SEQGEN generated the unique id by looking at dataset values from dataset SEQUENCE_GEN_MAPPING. The function stores the unique ids for each identifier there.

Also you can check application logs from the time you imported the user and system username generation rule failed and check if there are error messages related to system username generation.

[Jayesh]

FN_EIC_SEQGEN is getting generated and test cases are below:

1. Firstname: Rahul, Lastname: Kumar, and customer: 1. The System username generated is Rahul.Kumar1. Expected: Rahul.Kumar

2. Firstname: Rahul, Lastname: Kumar, and customer: 2. System username generated is Rahul.Kumar. Expected: Rahul.Kumar

3.  Firstname: Rahul, Lastname: Kumar, and customer: 2. The System username generated is Rahul.Kumar2. Expected: Rahul.Kumar1

4. Firstname: Rahul, Lastname: Kumar, and customer: 1. The System username was not generated.

Code:

CASE WHEN CONCAT (firstname, '. ', lastname) NOT IN (SELECT systemusername FROM users existing WHERE existing.customer = '1')
THEN CONCAT (firstname, '. ', lastname)
ELSE CONCAT (firstname, '. ', lastname, FN_EIC_SEQGEN(CONCAT (firstname, '. ', lastname, customer)))
END
###
CASE WHEN CONCAT (firstname, '. ', lastname) NOT IN (SELECT systemusername FROM users existing WHERE existing.customer = '2')
THEN CONCAT (firstname, '. ', lastname)
ELSE CONCAT (firstname, '. ', lastname, FN_EIC_SEQGEN(CONCAT (firstname, '. ', lastname, customer)))
END
###
CASE WHEN CONCAT (firstname, '. ', lastname) NOT IN (SELECT systemusername FROM users existing WHERE existing.customer IS Null)
THEN CONCAT (firstname, '. ', lastname)
ELSE CONCAT (firstname, '. ', lastname, FN_EIC_SEQGEN(CONCAT (firstname, '. ', lastname, customer)))
END

[Sampo]

Hi Jaeysh, did you import all four users at the same time or one by one to get these results?

Your current config will always first search if the system username exists in users whose customer == '1', even if the customer of the user is different. 

Why not write the rule like this:

CASE WHEN customer IS NOT NULL AND CONCAT (firstname, '. ', lastname) NOT IN (SELECT systemusername FROM users existing WHERE existing.customer = customer)
THEN CONCAT (firstname, '. ', lastname)

CASE WHEN customer IS NULL AND CONCAT (firstname, '. ', lastname) NOT IN (SELECT systemusername FROM users existing WHERE existing.customer IS NULL)
THEN CONCAT (firstname, '. ', lastname)
ELSE CONCAT (firstname, '. ', lastname, FN_EIC_SEQGEN(CONCAT (firstname, '. ', lastname, customer)))
END

But there might be another problem - I tested configuring a system username rule that intentionally produces a non-unique value. It failed to create the systemusername if another user already existed with the same systemusername in the Users table. The following message appears in application logs:

2024-09-12T17:32:04+03:00-ecm-services.SaviyntCommonUtilityService-http-nio-8080-exec-23-v57xb-DEBUG-Unable to get Unique System username from System Username Generation Rule.

So maybe the system username generation rule cannot be used when the requirement is that the same systemusername can be generated for users in different organisations. I also noticed that EIC won't let you set a duplicate systemusername for a user through UI or CSV import (not sure if there is any config option that would enable it though).

Can you modify your requirements so that systemusernames would contain the organization name or id or that the incrementing id would be shared by user all organisations? Then you could use system username generation rule to generate an unique systemusername for each user.

[rushikeshvartak]

{
  "ADDITIONALTABLES": {
    "USERS": "SELECT SYSTEMUSERNAME, firstname, lastname, companyname FROM USERS"
  },
  "COMPUTEDCOLUMNS": [
    "SYSTEMUSERNAME"
  ],
  "PREPROCESSQUERIES": [
    "UPDATE NEWUSERDATA as N SET N.SYSTEMUSERNAME = (SELECT CASE WHEN NOT EXISTS (SELECT U.SYSTEMUSERNAME FROM USERS U WHERE U.SYSTEMUSERNAME = CONCAT(N.firstname, '.', N.lastname) AND U.companyname = N.companyname) THEN CONCAT(N.firstname, '.', N.lastname ELSE CONCAT(N.firstname, '.', N.lastname, (SELECT COALESCE(MAX(CAST(SUBSTRING(U.SYSTEMUSERNAME, LENGTH(CONCAT(N.firstname, '.', N.lastname)) + 1) AS UNSIGNED)), 0) + 1 FROM USERS U WHERE U.SYSTEMUSERNAME LIKE CONCAT(N.firstname, '.', N.lastname, '%') AND U.companyname = N.companyname)) END) WHERE N.COMPANYNAME IN ('ABC', 'DEF')"
  ]
}

[Jayesh]

Getting an Error:

2024-09-13T12:54:30+02:00-ecm--null-4lj7s--13-Sep-2024 10:54:29.748 WARNING [http-nio-8080-exec-49] groovy.sql.Sql.executeUpdate Failed to execute: UPDATE TEMPNEWUSERS_37736 as N SET N.SYSTEMUSERNAME = (SELECT CASE WHEN NOT EXISTS (SELECT U.SYSTEMUSERNAME FROM USERS U WHERE U.SYSTEMUSERNAME = CONCAT(N.firstname, '.', N.lastname) AND U.companyname = N.companyname) THEN CONCAT(N.firstname, '.', N.lastname ELSE CONCAT(N.firstname, '.', N.lastname, (SELECT COALESCE(MAX(CAST(SUBSTRING(U.SYSTEMUSERNAME, LENGTH(CONCAT(N.firstname, '.', N.lastname)) + 1) AS UNSIGNED)), 0) + 1 FROM USERS U WHERE U.SYSTEMUSERNAME LIKE CONCAT(N.firstname, '.', N.lastname, '%') AND U.companyname = N.companyname)) END) WHERE N.COMPANYNAME IN ('GEIC - DEV', 'INFI') because: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ELSE CONCAT(N.firstname, '.', N.lastname, (SELECT COALESCE(MAX(CAST(SUBSTRING(U.' at line 1

2024-09-13T12:54:29+02:00-ecm-services.ImportSAvDataUserService-http-nio-8080-exec-49-4lj7s-ERROR-Error while processing data:

2024-09-13T12:54:30+02:00-ecm--null-4lj7s--java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ELSE CONCAT(N.firstname, '.', N.lastname, (SELECT COALESCE(MAX(CAST(SUBSTRING(U.' at line 1 at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) at com.mysql.cj.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1335) at com.mysql.cj.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2108) at com.mysql.cj.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1245) at com.saviynt.ecm.services.ImportSAvDataUserService$_doImportDataPreprocessing_closure10.doCall(ImportSAvDataUserService.groovy:416) at com.saviynt.ecm.services.ImportSAvDataUserService.doImportDataPreprocessing(ImportSAvDataUserService.groovy:405) at com.saviynt.ecm.services.ImportSAvDataUserService.importDataFromFile(ImportSAvDataUserService.groovy:733) at com.saviynt.ecm.services.ImportSAvDataUserService.importDataFromFile(ImportSAvDataUserService.groovy:686) at com.saviynt.ecm.services.UsersService.uploadUserFinalStep(UsersService.groovy:8808) at com.saviynt.ecm.identitywarehouse.controllers.UsersController$_closure26.doCall(UsersController.groovy:1720) at grails.plugin.springsecurity.web.filter.GrailsAnonymousAuthenticationFilter.doFilter(GrailsAnonymousAuthenticationFilter.java:53) at com.saviynt.webservice.SaviyntRestAuthenticationFilter.doFilter(SaviyntRestAuthenticationFilter.groovy:158) at grails.plugin.springsecurity.web.authentication.logout.MutableLogoutFilter.doFilter(MutableLogoutFilter.java:62) at grails.plugin.springsecurity.web.SecurityRequestHolderFilter.doFilter(SecurityRequestHolderFilter.java:59) at com.mrhaki.grails.plugin.xframeoptions.web.XFrameOptionsFilter.doFilterInternal(XFrameOptionsFilter.java:69) at com.brandseye.cors.CorsFilter.doFilter(CorsFilter.java:82) at java.lang.Thread.run(Thread.java:750)

[rushikeshvartak]

{
  "ADDITIONALTABLES": {
    "USERS": "SELECT SYSTEMUSERNAME, firstname, lastname, companyname FROM USERS"
  },
  "COMPUTEDCOLUMNS": [
    "SYSTEMUSERNAME"
  ],
  "PREPROCESSQUERIES": [
    "UPDATE NEWUSERDATA as N SET N.SYSTEMUSERNAME = (SELECT CASE WHEN NOT EXISTS (SELECT U.SYSTEMUSERNAME FROM USERS U WHERE U.SYSTEMUSERNAME = CONCAT(N.firstname, '.', N.lastname) AND U.companyname = N.companyname) THEN CONCAT(N.firstname, '.', N.lastname) ELSE CONCAT(N.firstname, '.', N.lastname, (SELECT COALESCE(MAX(CAST(SUBSTRING(U.SYSTEMUSERNAME, LENGTH(CONCAT(N.firstname, '.', N.lastname)) + 1) AS UNSIGNED)), 0) + 1 FROM USERS U WHERE U.SYSTEMUSERNAME LIKE CONCAT(N.firstname, '.', N.lastname, '%') AND U.companyname = N.companyname)) END) WHERE N.COMPANYNAME IN ('ABC', 'DEF')"
  ]
}