Username as SystemUsername

[sabthamis]

Hi Everyone,

We have a requirement to assign the systemusername as Username. Systemusername is generated by Saviynt based on firstname lastname which is working fine. 

I have added the same logic as in mentioned for systemusername in Registeruser rule(screenshot below). But still when i import a user manually from UI this is not updating with systemusername value.

Any idea how to achieve this ?

[rushikeshvartak]

copysystemusernametousername=true

[sabthamis]

Can you say which file is this and where to get this one ? externalconfig.properties ? @rushikeshvartak 

[sabthamis]

@rushikeshvartak  one more thing when i run the import job without username column the import fails does this change you suggested above ll resolve that issue as well ?

[rushikeshvartak]

Yes  externalconfig.properties and username is mandatory

[sabthamis]

@rushikeshvartak  I have updated this from configuration files and restarted the application. Then i imported the user from csv file from import user option but still i see the username is updated with some random numeric values.

[indra_hema_95]

Hi @sabthamis If you are not putting user name in your csv then you have to use some other attribute as reconciliation field, and for that field a value has to be there in your csv and that has to be unique for each user. 

Regards,

Indra

[NM]

Hi @sabthamis did you populate the generate username option in global config?

If you are using an attribute in register form which isn't present in CSV file it might fail the username generration

[sabthamis]

@NM  yes i m using a field username in my input file. Also the register user rule (username rule) and systemusername generation  is also configured in global setting . When I added the username field as you told and tried to  import the user in saviynt the user is not importing.

[NM]

@sabthamis if you keep username field empty as you are trying to create a new account.

Does it automatically created a user ID try once and share logs

[sabthamis]

the username s created as some numeric values and systemusername generated as per my logic i added.

[rushikeshvartak]

• What was reconciliation field selected during import ?
• Please share logs in .txt file

[sabthamis]

@rushikeshvartak  I have made the changes in configuration file and i imported the user from csv file. Manual user import. I have not configured any job to import the user to test it. While the file import i just kept the reconciliation field as Blank.

[rushikeshvartak]

• You can't keep the reconciliation field as blank; there should be some unique field like employee ID.

[sabthamis]

@rushikeshvartak  in the actual user import job i use username as reconciliation field...should i try to import the user from csv with a unique attribute and use it as reconciliation field while import...?

[rushikeshvartak]

Yes

[sabthamis]

@rushikeshvartak  -  I have tried with below file and used CP7 as recon field while importing. I still don't see the systemusername is copied as username

[sabthamis]

@rushikeshvartak I am using the below rule in Register user rule. I am using the same in systemusername generation in which it works. but getting below error in log when i use the same code for register user rule.

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 '' at line 1 at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) at

CASE

       WHEN (users.middlename IS NOT NULL)  THEN

                CONCAT(SUBSTRING(lower(users.firstname),1,1),SUBSTRING(lower(users.middlename),1,1),SUBSTRING(lower(users.lastname),1,6))

###CONCAT(SUBSTRING(lower(users.firstname),1,1),SUBSTRING(lower(users.middlename),1,1),SUBSTRING(lower(users.lastname),1,5) , substring('1',1,1)) ###CONCAT(SUBSTRING(lower(users.firstname),1,1),SUBSTRING(lower(users.middlename),1,1),SUBSTRING(lower(users.lastname),1,5) , substring('2',1,1))

###CONCAT(SUBSTRING(lower(users.firstname),1,1),SUBSTRING(lower(users.middlename),1,1),SUBSTRING(lower(users.lastname),1,5) , substring('3',1,1))

ELSE

                CONCAT(SUBSTRING(lower(users.firstname),1,1),SUBSTRING(lower(users.lastname),1,7))

### CONCAT(SUBSTRING(lower(users.firstname),1,1),SUBSTRING(lower(users.lastname),1,6) , substring('1',1,1))

### CONCAT(SUBSTRING(lower(users.firstname),1,1),SUBSTRING(lower(users.lastname),1,6) , substring('2',1,1))

### CONCAT(SUBSTRING(lower(users.firstname),1,1),SUBSTRING(lower(users.lastname),1,6) , substring('3',1,1))

END

[sabthamis]

@rushikeshvartak  Can i use inline preprocessor to use the systemusername as username while identity is created ?

[rushikeshvartak]

• Did you defined system user name in global configuration

[sabthamis]

yes its defined and its working fine. the only thing is we need to use the same value for username. @rushikeshvartak 

[rushikeshvartak]

Can you share logs

[sabthamis]

@rushikeshvartak  Now i can generate the username using the same code used for  systemusername. But I see they both are different means its taking username and appending 1 duplicate check is done against username and system username i guess. Attached here is the log and screenshot for the same.

Code used for Username:

CASE

       WHEN (users.middlename IS NOT NULL)  THEN            CONCAT(SUBSTRING(lower(users.firstname),1,1),SUBSTRING(lower(users.middlename),1,1),SUBSTRING(lower(users.lastname),1,6))

#CONCAT(SUBSTRING(lower(users.firstname),1,1),SUBSTRING(lower(users.middlename),1,1),SUBSTRING(lower(users.lastname),1,5) , substring('1',1,1)) #CONCAT(SUBSTRING(lower(users.firstname),1,1),SUBSTRING(lower(users.middlename),1,1),SUBSTRING(lower(users.lastname),1,5) , substring('2',1,1))

#CONCAT(SUBSTRING(lower(users.firstname),1,1),SUBSTRING(lower(users.middlename),1,1),SUBSTRING(lower(users.lastname),1,5) , substring('3',1,1))

ELSE

                CONCAT(SUBSTRING(lower(users.firstname),1,1),SUBSTRING(lower(users.lastname),1,7))

# CONCAT(SUBSTRING(lower(users.firstname),1,1),SUBSTRING(lower(users.lastname),1,6) , substring('1',1,1))

#CONCAT(SUBSTRING(lower(users.firstname),1,1),SUBSTRING(lower(users.lastname),1,6) , substring('2',1,1))

# CONCAT(SUBSTRING(lower(users.firstname),1,1),SUBSTRING(lower(users.lastname),1,6) , substring('3',1,1)) END.

Code used for systemusername:

CASE

       WHEN (users.middlename IS NOT NULL)  THEN

                CONCAT(SUBSTRING(lower(users.firstname),1,1),SUBSTRING(lower(users.middlename),1,1),SUBSTRING(lower(users.lastname),1,6))

#CONCAT(SUBSTRING(lower(users.firstname),1,1),SUBSTRING(lower(users.middlename),1,1),SUBSTRING(lower(users.lastname),1,5)   , substring('1',1,1)) #CONCAT(SUBSTRING(lower(users.firstname),1,1),SUBSTRING(lower(users.middlename),1,1),SUBSTRING(lower(users.lastname),1,5)   , substring('2',1,1))

#CONCAT(SUBSTRING(lower(users.firstname),1,1),SUBSTRING(lower(users.middlename),1,1),SUBSTRING(lower(users.lastname),1,5)   , substring('3',1,1))

ELSE

                CONCAT(SUBSTRING(lower(users.firstname),1,1),SUBSTRING(lower(users.lastname),1,7))

# CONCAT(SUBSTRING(lower(users.firstname),1,1),SUBSTRING(lower(users.lastname),1,6)   , substring('1',1,1))

# CONCAT(SUBSTRING(lower(users.firstname),1,1),SUBSTRING(lower(users.lastname),1,6)   , substring('2',1,1))

# CONCAT(SUBSTRING(lower(users.firstname),1,1),SUBSTRING(lower(users.lastname),1,6)   , substring('3',1,1))

END

[rushikeshvartak]

Do you have any other user with same username ?

[sabthamis]

No i checked it from Data analyzer only user i getting in result it the same user.

[rushikeshvartak]

• You need to run your logic from data analyzer

[rushikeshvartak]

Also you have extra ) in SQL, run query from data analyzer and validate

[sabthamis]

Updated query which i using is below i don't see any error when running from data analyzer 

CASE

    WHEN (users.middlename IS NOT NULL) THEN

                CONCAT(

                    SUBSTRING(LOWER(REGEXP_REPLACE(users.firstname, '[^a-zA-Z]', '')), 1, 1),

                    SUBSTRING(LOWER(REGEXP_REPLACE(users.middlename, '[^a-zA-Z]', '')), 1, 1),

                    SUBSTRING(LOWER(REGEXP_REPLACE(users.lastname, '[^a-zA-Z]', '')), 1, 6))

                ###CONCAT(

                    SUBSTRING(LOWER(REGEXP_REPLACE(users.firstname, '[^a-zA-Z]', '')), 1, 1),

                    SUBSTRING(LOWER(REGEXP_REPLACE(users.middlename, '[^a-zA-Z]', '')), 1, 1),

                    SUBSTRING(LOWER(REGEXP_REPLACE(users.lastname, '[^a-zA-Z]', '')), 1, 5) , substring('1',1,1))

###CONCAT(

                    SUBSTRING(LOWER(REGEXP_REPLACE(users.firstname, '[^a-zA-Z]', '')), 1, 1),

                    SUBSTRING(LOWER(REGEXP_REPLACE(users.middlename, '[^a-zA-Z]', '')), 1, 1),

                    SUBSTRING(LOWER(REGEXP_REPLACE(users.lastname, '[^a-zA-Z]', '')), 1, 5),

                    substring('2',1,1))

###CONCAT(

                    SUBSTRING(LOWER(REGEXP_REPLACE(users.firstname, '[^a-zA-Z]', '')), 1, 1),

                    SUBSTRING(LOWER(REGEXP_REPLACE(users.middlename, '[^a-zA-Z]', '')), 1, 1),

                    SUBSTRING(LOWER(REGEXP_REPLACE(users.lastname, '[^a-zA-Z]', '')), 1, 5),

                    substring('3',1,1))

            ELSE

                CONCAT(

            SUBSTRING(LOWER(REGEXP_REPLACE(users.firstname, '[^a-zA-Z]', '')), 1, 1),

            SUBSTRING(LOWER(REGEXP_REPLACE(users.lastname, '[^a-zA-Z]', '')), 1, 7))

   ###CONCAT(SUBSTRING(LOWER(REGEXP_REPLACE(users.firstname, '[^a-zA-Z]', '')), 1, 1),

            SUBSTRING(LOWER(REGEXP_REPLACE(users.lastname, '[^a-zA-Z]', '')), 1, 6) , substring('1',1,1))

  ###CONCAT(SUBSTRING(LOWER(REGEXP_REPLACE(users.firstname, '[^a-zA-Z]', '')), 1, 1),

            SUBSTRING(LOWER(REGEXP_REPLACE(users.lastname, '[^a-zA-Z]', '')), 1, 6) , substring('2',1,1))

###CONCAT(SUBSTRING(LOWER(REGEXP_REPLACE(users.firstname, '[^a-zA-Z]', '')), 1, 1),

            SUBSTRING(LOWER(REGEXP_REPLACE(users.lastname, '[^a-zA-Z]', '')), 1, 6) , substring('3',1,1))

END

[rushikeshvartak]

Add all conditions with same user

[sabthamis]

I used the below query..I doubt if it will work since we use ### for multiple condition getting error as 

Error : 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 'SUBSTRING(LOWER(REGEXP_REPLACE('Sabthami', '[^a-zA-Z]', '')), 1, 1), SUB' at line 9

[rushikeshvartak]

use case when

[sabthamis]

@rushikeshvartak  I am able to achieve it . Thanks. We can directly map the systemusername to username in global configuration which worked for my requirements.

[rushikeshvartak]

Can you share configs Screenshot

[sabthamis]

@rushikeshvartak  here you go...

[rushikeshvartak]

Can you edit reply and also add username screenshot and logic and accept your answer as solution