Click HERE to see how Saviynt Intelligence is transforming the industry. |
09/11/2024 06:46 AM
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:
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
09/11/2024 06:49 AM
What is error you are getting and how you are tetsing ? via import job or uploading user ?
09/11/2024 06:56 AM
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.
09/11/2024 08:44 AM
Share logs in text file
09/11/2024 10:46 AM
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
)"
]
}
09/11/2024 10:49 AM
Select reconciliation field during import
09/11/2024 10:55 AM
Still, it is zero records. Selected reconciliation field as EmployeeID
09/11/2024 11:01 AM
Did you checked logs
09/11/2024 11:13 AM
09/11/2024 11:14 AM - edited 09/11/2024 11:15 AM
What error you have seen in logs. logs does not contain relevant logs
09/11/2024 11:58 PM
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
09/12/2024 05:38 AM
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
09/12/2024 06:08 AM
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.
09/12/2024 06:46 AM
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
09/12/2024 07:46 AM
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.
09/12/2024 07:54 AM
{
"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')"
]
}
09/13/2024 03:57 AM
Getting an Error:
09/13/2024 05:44 AM
{
"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')"
]
}