How to check for collisions when generating a random systemusername using advanced query

[yogesh2]

We are using an advanced rule for systemusername generation where we have below query:

CONCAT(
CHAR(FLOOR(65 + RAND() * 26) using UTF8MB4),
CHAR(FLOOR(65 + RAND() * 26) using UTF8MB4),
CHAR(FLOOR(65 + RAND() * 26) using UTF8MB4),
CHAR(FLOOR(65 + RAND() * 26) using UTF8MB4),
LPAD(FLOOR(RAND() * 1000), 3, '0')
)

This generates a random value like these:

FUEQ606
ZNRV014
WMTK758
...

But there is always a probability that this rule generates a value that is already present in systemusername for an existing user. How can we check for uniqueness?

Can we run this in a loop so that this rule is re-run in case of collisions and calculates a new value again?

what is the syntax to define multiple queries? so that atleast it tries a couple of times, something like this:

CONCAT(
    CHAR(FLOOR(65 + RAND() * 26) using UTF8MB4),
    CHAR(FLOOR(65 + RAND() * 26) using UTF8MB4),
    CHAR(FLOOR(65 + RAND() * 26) using UTF8MB4),
    CHAR(FLOOR(65 + RAND() * 26) using UTF8MB4),
    LPAD(FLOOR(RAND() * 1000), 3, '0'))
###
CONCAT(
    CHAR(FLOOR(65 + RAND() * 26) using UTF8MB4),
    CHAR(FLOOR(65 + RAND() * 26) using UTF8MB4),
    CHAR(FLOOR(65 + RAND() * 26) using UTF8MB4),
    CHAR(FLOOR(65 + RAND() * 26) using UTF8MB4),
    LPAD(FLOOR(RAND() * 1000), 3, '0'))
###
CONCAT(
    CHAR(FLOOR(65 + RAND() * 26) using UTF8MB4),
    CHAR(FLOOR(65 + RAND() * 26) using UTF8MB4),
    CHAR(FLOOR(65 + RAND() * 26) using UTF8MB4),
    CHAR(FLOOR(65 + RAND() * 26) using UTF8MB4),
    LPAD(FLOOR(RAND() * 1000), 3, '0'))

this will try three times but I need confirmation on the syntax if something like this is allowed.

[adarshk]

Make sure CheckforUnique is mapped correctly. 

You can use Auto Increment, if the generated value is mathing against the existing systemusernames, system will increment the value and generated a unique one.

refer the below article:
https://forums.saviynt.com/t5/community-knowledge-base/how-to-generate-a-random-three-letter-systemu...

[yogesh2]

I am not able to find "CheckforUnique" configuration for systemusername generation. I am aware of the autoincrement use case. My question was regarding random names and if there is any mechanism provided by Saviynt to retry the name generation rule in case a collision is created.

[rushikeshvartak]

What happens when collision occurs ? Task fails ?

[yogesh2]

User gets created with a blank systemusername

[rushikeshvartak]

Please share logs