How to handle duplicate users(same name users) in Advance configuration of Email generation Rule

[asharma]

Hi 

I am using advance config for generating email for multiple domains and i have 1 use case where we can have multiple users with same name. therefore i referred below link to proceed.

https://forums.saviynt.com/t5/general-discussions/how-to-handle-duplicate-users-same-name-users-in-a...

Still i am not able to go to finish line, please suggest how can i proceed. i have pasted the query which i am using as below.

CASE WHEN (users.entity = 'ro') THEN concat (users.firstname , substring('.',1,1) , users.lastname , '@ro.com') end #
CASE WHEN (users.entity = 'HL') THEN concat (users.firstname , substring('.',1,1) , users.lastname , '@hi.com') end # CASE WHEN (users.username is users.firstname,".",users.lastname) THEN concat(users.firstname , substring('.',1,1) , users.lastname , ((RAND() * (68-10+1))+10), '@hi.com')
CASE WHEN (users.entity = 'dm') THEN concat (users.firstname , substring('.',1,1) , users.lastname, '@dm.net') end #
CASE WHEN (users.entity = 'co') THEN concat (users.firstname , substring('.',1,1) , users.lastname, '@co.com') end #
CASE WHEN (users.entity = 'ml') THEN concat (users.firstname , substring('.',1,1) , users.lastname, '@mi.com') end #
CASE WHEN (users.entity = 'mo') THEN concat (users.firstname , substring('.',1,1) , users.lastname, '@mo.com') end #
END

if possible, please rephrase my code so that it would be easier for me to enahnce in same direction.

[DixshantValecha]

It seems like your query is using CASE WHEN statements to generate email addresses based on the user’s entity. However, there seems to be an issue with the third CASE WHEN statement where you are trying to concatenate the user’s first name, last name and a random number between 10 and 68.

It looks like there is a syntax error in this statement. Instead of users.username is users.firstname,".",users.lastname, it should be users.username = concat(users.firstname,'.',users.lastname).

Here’s the updated query:

CASE WHEN (users.entity = 'ro') THEN concat (users.firstname , substring('.',1,1) , users.lastname , '@ro.com') end #
CASE WHEN (users.entity = 'HL') THEN concat (users.firstname , substring('.',1,1) , users.lastname , '@hi.com') end # CASE WHEN (users.username = concat(users.firstname,'.',users.lastname)) THEN concat(users.firstname , substring('.',1,1) , users.lastname , ((RAND() * (68-10+1))+10), '@hi.com')
CASE WHEN (users.entity = 'dm') THEN concat (users.firstname , substring('.',1,1) , users.lastname, '@dm.net') end #
CASE WHEN (users.entity = 'co') THEN concat (users.firstname , substring('.',1,1) , users.lastname, '@co.com') end #
CASE WHEN (users.entity = 'ml') THEN concat (users.firstname , substring('.',1,1) , users.lastname, '@mi.com') end #
CASE WHEN (users.entity = 'mo') THEN concat (users.firstname , substring('.',1,1) , users.lastname, '@mo.com') end #
END

Please validate and let us know if further details are needed on this.

[asharma]

Hi Dixshant,

I have tried above code but it's not working. I changed 1 attribute i.e. email in place of username. pasting the query again for reference.

CASE WHEN (users.entity = 'ro') THEN concat (users.firstname , substring('.',1,1) , users.lastname , '@ro.com') end #
CASE WHEN (users.entity = 'HL') THEN concat (users.firstname , substring('.',1,1) , users.lastname , '@hi.com') end # CASE WHEN (users.email = concat(users.firstname , substring('.',1,1) , users.lastname , '@hi.com') THEN concat(users.firstname , substring('.',1,1) , users.lastname , ((RAND() * (68-10+1))+10), '@hi.com')
CASE WHEN (users.entity = 'dm') THEN concat (users.firstname , substring('.',1,1) , users.lastname, '@dm.net') end #
CASE WHEN (users.entity = 'co') THEN concat (users.firstname , substring('.',1,1) , users.lastname, '@co.com') end #
CASE WHEN (users.entity = 'ml') THEN concat (users.firstname , substring('.',1,1) , users.lastname, '@mi.com') end #
CASE WHEN (users.entity = 'mo') THEN concat (users.firstname , substring('.',1,1) , users.lastname, '@mo.com') end #
END

What it does is it checks for username for reconciliation and it insert the identity but without email like blank email. As per logs, it ignores the rule if email exists.

Please advise how can i proceed now.

[asharma]

Hi Dixshant,

I am looking for your help here, please suggest me path for next step.

[DixshantValecha]

Hi @asharma,

Our team is currently investigating  and we appreciate your patience while we work on this. We will provide you with an update as soon as possible regarding the status of the investigation and any potential solutions or next steps.

[asharma]

Hi Dixshant,

Any update on above?

Regards

[DixshantValecha]

Based on your updated code, it seems like you are trying to generate email addresses for users based on their entity value, and for cases where the email address is already set to a specific value, you want to append a random number to the email address with the domain '@hi.com'. However, you mentioned that the email address is not being generated correctly and is showing as blank.

Here's a revised version of the code that may address the issue:

CASE 
  WHEN users.entity = 'ro' THEN concat(users.firstname, '.', users.lastname, '@ro.com')
  WHEN users.entity = 'HL' THEN concat(users.firstname, '.', users.lastname, '@hi.com')
  WHEN users.entity = 'dm' THEN concat(users.firstname, '.', users.lastname, '@dm.net')
  WHEN users.entity = 'co' THEN concat(users.firstname, '.', users.lastname, '@co.com')
  WHEN users.entity = 'ml' THEN concat(users.firstname, '.', users.lastname, '@mi.com')
  WHEN users.entity = 'mo' THEN concat(users.firstname, '.', users.lastname, '@mo.com')
  ELSE CASE 
         WHEN users.email = concat(users.firstname, '.', users.lastname, '@hi.com') THEN 
           concat(users.firstname, '.', users.lastname, ((RAND() * (68-10+1)) + 10), '@hi.com')
         ELSE '' -- Default case for handling blank email
       END
END

 Please validate let me know if you have any further questions or issues.

[asharma]

Hi Dixshant,

I'll explain my concern by taking a exanple below:

For example: We have 1 entity as 'hi' and we have 2 users with same name which i need to import using file based schema import. Now when Saviynt will import the users it will cehck for entity and based on entity it will create a email. Here we have 2 users with same name so we want to import both users but we have to make some difference in any attribute so that email becomes unique. For that, we create 1 user with 'first.lastname@hi.com' and 2 user as 'first.lastname1@hi.com'

Now, i need logic for above example, Let me know if you got my concern.

Also, above code is not working, result is still same.

Regards

[DixshantValecha · Accepted solution]

Thank you for the update. We kindly request that you refer to the below query as an example and attempt to develop the code accordingly

For Reference:-

CASE
    WHEN users.entity = 'ro' THEN CONCAT(users.firstname, '.', users.lastname, '@ro.com')
    WHEN users.entity = 'HL' THEN CONCAT(users.firstname, '.', users.lastname, '@hi.com')
    WHEN users.entity = 'dm' THEN CONCAT(users.firstname, '.', users.lastname, '@dm.net')
    WHEN users.entity = 'co' THEN CONCAT(users.firstname, '.', users.lastname, '@co.com')
    WHEN users.entity = 'ml' THEN CONCAT(users.firstname, '.', users.lastname, '@mi.com')
    WHEN users.entity = 'mo' THEN CONCAT(users.firstname, '.', users.lastname, '@mo.com')
    ELSE CONCAT(users.firstname, '.', users.lastname, IF(EXISTS(SELECT 1 FROM users u WHERE u.firstname = users.firstname AND u.lastname = users.lastname), '1', ''), '@hi.com')
END

[asharma]

It works this time, Thankyou Dixshant.

1 more query, what if we have to check with all entities for same condition.

[DixshantValecha · Accepted solution]

Thank you for the update. We would appreciate it if you could mark this as a solution if the provided solution resolved your query. Additionally, for your second question, we kindly request that you try to create an example based on the given instructions, and please do not hesitate to let us know if you require any further assistance.