Padding numbers to systemusername when character count is less than certain number

[nvachhani]

Using an advanced query in the systemusername generation rule, how can we pad numbers to systemusername if the character count is say less than 4? I understand I can use RPAD to do this, but that also truncates values if the string is greater than the minimum you have specified.

[rushikeshvartak]

Share current configuration.

[nvachhani]

Current configuration is:

First 5 letters of lastname

First letter of firstname

First letter of middlename

Auto Increment number for duplicates

We need to add a clause to ensure this does not produce a systemusername with less than 4 characters, if so, we need to add numbers in order to reach 4 characters.

[rushikeshvartak]

Share actual configuration implemented in saviynt

[nvachhani]

concat(RPAD((LEFT(REPLACE(users.firstname,' ',''), 5)),(LEFT(REPLACE(users.lastname,' ',''), 1)),(LEFT(REPLACE(users.middlename,' ',''), 1))), 4, '0') # concat(RPAD((LEFT(REPLACE(users.firstname,' ',''), 5)),(LEFT(REPLACE(users.lastname,' ',''), 1)),(LEFT(REPLACE(users.middlename,' ',''), 1))), 4, '0') + 1

This would truncate systemusername if longer than 4 characters

[rushikeshvartak]

LEFT(
CONCAT(
LEFT(users.lastname, 5),
LEFT(users.firstname, 1),
LEFT(users.middlename , 1),
LPAD(FLOOR(RAND() * 10000), 5, '0')),5)

[nvachhani]

This generates the following systemusername:

LEFTCONCATLEFTlastname5LEFTfirstname1LEFTmiddlename1LPADFLOORRAND100005051

[nvachhani]

The following works for systemusernames less than 4 characters but truncates the result if it is larger than 4 characters:

LEFT((CONCAT(LEFT(users.lastname, 5), LEFT(users.firstname, 1), LEFT(users.middlename , 1), LPAD(FLOOR(RAND() * 1000), 4, '0'))), 4)

[rushikeshvartak]

Great it was just spacing and new line issue

[nvachhani]

But this still truncates the systemusername if it is larger than 4 characters

[nvachhani]

Any help with this? This still truncates the systemusername if it is larger than 4 characters and fails entirely if any value such as middlename is null

[rushikeshvartak]

IF(LENGTH(CONCAT(LEFT(users.lastname, 5), LEFT(users.firstname, 1), LEFT(users.middlename , 1))) >= 4, CONCAT(LEFT(users.lastname, 5), LEFT(users.firstname, 1), LEFT(users.middlename , 1)), LEFT(CONCAT(LEFT(users.lastname, 5), LEFT(users.firstname, 1), LEFT(users.middlename , 1), LPAD(FLOOR(RAND() * 1000), 4, '0')), 4) )

[nvachhani]

This no longer truncates if value is greater than 4 but still fails if a value is missing like middlename. Some users will not have this value, is there a check we can do in the query?

[rushikeshvartak]

IF(LENGTH(CONCAT(LEFT(ifnull(users.lastname,''), 5), LEFT(ifnull(users.firstname,''), 1), LEFT(ifnull(users.middlename,'') , 1))) >= 4, CONCAT(LEFT(ifnull(users.lastname,''), 5), LEFT(ifnull(users.firstname,''), 1), LEFT(ifnull(users.middlename,'') , 1)), LEFT(CONCAT(LEFT(ifnull(users.lastname,''), 5), LEFT(ifnull(users.firstname,''), 1), LEFT(ifnull(users.middlename,'') , 1), LPAD(FLOOR(RAND() * 1000), 4, '0')), 4) )

[rushikeshvartak]

Did above one worked ?

[rushikeshvartak]

IF(LENGTH(CONCAT(LEFT(ifnull(users.lastname,''), 5), LEFT(ifnull(users.firstname,''), 1), LEFT(ifnull(users.middlename,'') , 1))) >= 4, CONCAT(LEFT(ifnull(users.lastname,''), 5), LEFT(ifnull(users.firstname,''), 1), LEFT(ifnull(users.middlename,'') , 1)), LEFT(CONCAT(LEFT(ifnull(users.lastname,''), 5), LEFT(ifnull(users.firstname,''), 1), LEFT(ifnull(users.middlename,'') , 1), LPAD(FLOOR(RAND() * 1000), 4, '0')), 4) )###IF(LENGTH(CONCAT(LEFT(ifnull(users.lastname,''), 5), LEFT(ifnull(users.firstname,''), 1), LEFT(ifnull(users.middlename,'') , 1))) >= 4, CONCAT(LEFT(ifnull(users.lastname,''), 5), LEFT(ifnull(users.firstname,''), 1), LEFT(ifnull(users.middlename,'') , 1)), LEFT(CONCAT(LEFT(ifnull(users.lastname,''), 5), LEFT(ifnull(users.firstname,''), 1), LEFT(ifnull(users.middlename,'') , 1), LPAD(FLOOR(RAND() * 1000), 4, '0')), 4) )###IF(LENGTH(CONCAT(LEFT(ifnull(users.lastname,''), 5), LEFT(ifnull(users.firstname,''), 1), LEFT(ifnull(users.middlename,'') , 1))) >= 4, CONCAT(LEFT(ifnull(users.lastname,''), 5), LEFT(ifnull(users.firstname,''), 1), LEFT(ifnull(users.middlename,'') , 1)), LEFT(CONCAT(LEFT(ifnull(users.lastname,''), 5), LEFT(ifnull(users.firstname,''), 1), LEFT(ifnull(users.middlename,'') , 1), LPAD(FLOOR(RAND() * 1000), 4, '0')), 4) )###IF(LENGTH(CONCAT(LEFT(ifnull(users.lastname,''), 5), LEFT(ifnull(users.firstname,''), 1), LEFT(ifnull(users.middlename,'') , 1))) >= 4, CONCAT(LEFT(ifnull(users.lastname,''), 5), LEFT(ifnull(users.firstname,''), 1), LEFT(ifnull(users.middlename,'') , 1)), LEFT(CONCAT(LEFT(ifnull(users.lastname,''), 5), LEFT(ifnull(users.firstname,''), 1), LEFT(ifnull(users.middlename,'') , 1), LPAD(FLOOR(RAND() * 1000), 4, '0')), 4) )###IF(LENGTH(CONCAT(LEFT(ifnull(users.lastname,''), 5), LEFT(ifnull(users.firstname,''), 1), LEFT(ifnull(users.middlename,'') , 1))) >= 4, CONCAT(LEFT(ifnull(users.lastname,''), 5), LEFT(ifnull(users.firstname,''), 1), LEFT(ifnull(users.middlename,'') , 1)), LEFT(CONCAT(LEFT(ifnull(users.lastname,''), 5), LEFT(ifnull(users.firstname,''), 1), LEFT(ifnull(users.middlename,'') , 1), LPAD(FLOOR(RAND() * 1000), 4, '0')), 4) )

[nvachhani]

The solution provided does not work for duplicates, in the logs I can see it just generates the same name five times and then fails since it is duplicate

[nvachhani]

How can this be modified to auto increment for duplicates? The below has been tried but the systemusername generated is '1.0'

IF(LENGTH(CONCAT(LEFT(ifnull(users.lastname,''), 5), LEFT(ifnull(users.firstname,''), 1), LEFT(ifnull(users.middlename,'') , 1))) >= 4, CONCAT(LEFT(ifnull(users.lastname,''), 5), LEFT(ifnull(users.firstname,''), 1), LEFT(ifnull(users.middlename,'') , 1)), LEFT(CONCAT(LEFT(ifnull(users.lastname,''), 5), LEFT(ifnull(users.firstname,''), 1), LEFT(ifnull(users.middlename,'') , 1), LPAD(FLOOR(RAND() * 1000), 4, '0')), 4) ) ### IF(LENGTH(CONCAT(LEFT(ifnull(users.lastname,''), 5), LEFT(ifnull(users.firstname,''), 1), LEFT(ifnull(users.middlename,'') , 1))) >= 4, CONCAT(LEFT(ifnull(users.lastname,''), 5), LEFT(ifnull(users.firstname,''), 1), LEFT(ifnull(users.middlename,'') , 1)), LEFT(CONCAT(LEFT(ifnull(users.lastname,''), 5), LEFT(ifnull(users.firstname,''), 1), LEFT(ifnull(users.middlename,'') , 1), LPAD(FLOOR(RAND() * 1000), 4, '0')), 4)) +1

[rushikeshvartak]

IF(LENGTH(CONCAT(LEFT(ifnull(users.lastname,''), 5), LEFT(ifnull(users.firstname,''), 1), LEFT(ifnull(users.middlename,'') , 1))) >= 4, CONCAT(LEFT(ifnull(users.lastname,''), 5), LEFT(ifnull(users.firstname,''), 1), LEFT(ifnull(users.middlename,'') , 1)), LEFT(CONCAT(LEFT(ifnull(users.lastname,''), 5), LEFT(ifnull(users.firstname,''), 1), LEFT(ifnull(users.middlename,'') , 1), LPAD(FLOOR(RAND() * 1000), 4, '0')), 4) ) ### IF(LENGTH(CONCAT(LEFT(ifnull(users.lastname,''), 5), LEFT(ifnull(users.firstname,''), 1), LEFT(ifnull(users.middlename,'') , 1))) >= 4, CONCAT(LEFT(ifnull(users.lastname,''), 5), LEFT(ifnull(users.firstname,''), 1), LEFT(ifnull(users.middlename,'') , 1)), LEFT(CONCAT(LEFT(ifnull(users.lastname,''), 5), LEFT(ifnull(users.firstname,''), 1), LEFT(ifnull(users.middlename,'') , 1), LPAD(FLOOR(RAND() * 1000+1), 4, '0')), 4))

[nvachhani]

I am seeing the same issue, the logs are producing the same systemusername in the second case and thus failing.

[rushikeshvartak]

IF(LENGTH(CONCAT(LEFT(ifnull(users.lastname,''), 5), LEFT(ifnull(users.firstname,''), 1), LEFT(ifnull(users.middlename,'') , 1))) >= 4, CONCAT(LEFT(ifnull(users.lastname,''), 5), LEFT(ifnull(users.firstname,''), 1), LEFT(ifnull(users.middlename,'') , 1)), LEFT(CONCAT(LEFT(ifnull(users.lastname,''), 5), LEFT(ifnull(users.firstname,''), 1), LEFT(ifnull(users.middlename,'') , 1), LPAD(FLOOR(RAND() * 1000), 4, '0')), 4) ) ### IF(LENGTH(CONCAT(LEFT(ifnull(users.lastname,''), 5), LEFT(ifnull(users.firstname,''), 1), LEFT(ifnull(users.middlename,'') , 1))) >= 4, CONCAT(LEFT(ifnull(users.lastname,''), 5), LEFT(ifnull(users.firstname,''), 1), LEFT(ifnull(users.middlename,'') , 1)), LEFT(CONCAT(LEFT(ifnull(users.lastname,''), 5), LEFT(ifnull(users.firstname,''), 1), LEFT(ifnull(users.middlename,'') , 1), LPAD(FLOOR(RAND() * 100+1), 4, '0')), 4))

[nvachhani]

Unfortunately it is still failing, I have attached the logs for clarity. It still produces same systemusername as first rule.

[rushikeshvartak]

Refer https://forums.saviynt.com/t5/identity-governance/advance-config-for-systemusername-creation-excludi...