How to use generate random numbers based on dataset and map to sAMAccountName?

[deepa]

Hi,

We have a requirement to generate a code with prefix data like 'S81' + random numbers and use that as the account name for AD during account creation. What is the best way to achieve this?

We were thinking of having a Dataset with the defined prefix data but not sure how to get 'S81' + random numbers during account creation. We also need to make sure it is a unique identifier and the account name that is generated is not an existing account name. We will not be able to reference the accounts data in pre-processor too. Any recommendations on how to approach it?

Thanks,

Deepa.S

[PremMahadikar]

Hi @deepa,

Can you try below:

Account Name Rule: select concat('S81',FLOOR(RAND()*10000)) # select concat('S81',FLOOR(RAND()*10000)) # select concat('S81',FLOOR(RAND()*10000))

Ex: S812461, S817007, S813807

Note:

1. Here 10000, number of zero's will define length of random code. Increase or decrease as per the use case of account name length
2. # is used has 'OR' in account name rule. If first value is in the system, it will check second code. (Added to handle uniqueness)

If this helps your question, please consider selecting Accept As Solution and hit Kudos

[Raghu]

@deepa  We have account name rule every endpoint level , need configure based on your respected endpoint level.

Navigation Endpoint->Endpoint details->Account Name Rule -> Advance Config -> SQL

AccountName Rule :  concat('S81',(FLOOR(RAND()*10000)) # concat('S81',(FLOOR(RAND()*10000)) # concat('S81',(FLOOR(RAND()*10000))

Here  we passing 10000 5 digit number , based on digit it will apply value 'S81'+ 5digit number , based on you requirement you pass it digit value  and click on okay and update endpoint.

Sample Query for checking in Data analyzer:

SELECT CONCAT('S81', FLOOR(RAND() * 10000)) AS RandomString1,CONCAT('S81', FLOOR(RAND() * 10000)) AS RandomString2,CONCAT('S81', FLOOR(RAND() * 10000)) AS RandomString3

[deepa]

Hi Prem/Raghu,

The prefix code "S81" is not same always. It will change depending on the user's costcenter hence we were thinking to store all the the prefix in dataset. 

Any recommendations on how to handle it when it is the above case for prefix?

Thanks,

Deepa.S

[Raghu]

@deepa  can give sample please ?with "S81" and users.costcenter case :

[deepa]

Below is the sample mapping

Costcenter	Prefix
Sales	S01
Marketing	S02
Supplies	S81

When we create the account for the user we want a customeproperty in user profile for each user based on the costcenter mapping to be updated with this generated  code and map it to sAMAccountName during creation of the account.

Costcenter	Prefix	User	Generated code (Prefix + Random number)
Sales	S01	User1	S014567
Marketing	S02	User2	S027685
Supplies	S81	User3	S816745

Please let me know if you have any further question

[Saathvik]

@deepa : You may have to use CASE condition and include all scenarios that are possible and use the same logic others mentioned to generate prefix + random number. Having that in dataset will not help because it is not resolvable in accountname rule.

For details refer Account Name Rule explanation in Viewing-or-Updating-Endpoints 

[Raghu]

@deepa  try below

conact(case
when user.costcenter = 'sales' then concat('s01', user.costcenter, floor(rand() * 10000))
when user.costcenter = 'marketing' then concat('s02', user.costcenter, floor(rand() * 10000))
when user.costcenter = 'supplies' then concat('s81', user.costcenter, floor(rand() * 10000))
else concat('s81', user.costcenter, floor(rand() * 10000))
end )# conact(case
when user.costcenter = 'sales' then concat('s01', user.costcenter, floor(rand() * 10000))
when user.costcenter = 'marketing' then concat('s02', user.costcenter, floor(rand() * 10000))
when user.costcenter = 'supplies' then concat('s81', user.costcenter, floor(rand() * 10000))
else concat('s81', user.costcenter, floor(rand() * 10000))
end )#conact(
case
when user.costcenter = 'sales' then concat('s01', user.costcenter, floor(rand() * 10000))
when user.costcenter = 'marketing' then concat('s02', user.costcenter, floor(rand() * 10000))
when user.costcenter = 'supplies' then concat('s81', user.costcenter, floor(rand() * 10000))
else concat('s81', user.costcenter, floor(rand() * 10000))
end )

[rushikeshvartak]

case when users.costcenter ='Sales' then concat('S01',(FLOOR(RAND()*10000)) # concat('S81',(FLOOR(RAND()*10000)) when  users.costcenter ='Marketing' then concat('S02',(FLOOR(RAND()*10000)) # concat('S81',(FLOOR(RAND()*10000)) when users.costcenter ='Supplies' then concat('S81',(FLOOR(RAND()*10000)) # concat('S81',(FLOOR(RAND()*10000)) END

[deepa]

@rushikeshvartak / @Raghu / @Saathvik ,

I have around 400 costcenter/prefix combination. It might grow too. Case condition for this large set is going to unmanagable. Do you think i can do a preprocessor query to manipulate the customproperty using the dataset and use that customproperty for the accountname?

Thanks,

Deepa.S

[rushikeshvartak]

You can use preprocessor and store value on users custom property

[Saathvik]

@deepa : Yes that is possible, Build a dataset with combination of cost center, prefix and populate one of CP's of user to that prefix it should have as part of inline preprocessor and then use that CP in accountname rule

[PremMahadikar]

@deepa ,

As Rushi and Saathvik mentioned above, use inline preprocessor!

1. Refer few articles below to use dataset and case statement to set a value in users' custom property
   • Solved: Dataset usage in User Import Preprocessor - Saviynt Forums - 55992
   • Solved: Accessing dataset information in User Import Prepr... - Saviynt Forums - 60721
2. Run the inline preprocessor (configured under ModifyUserJson) during import which updates existing user's customproperty
3. configure account name rule to call user custom property

My suggestion here would be

• Only use preprocessor to generate prefix in user customporperty only
• Use user's customproperty in account name rule
• As you can handle special characters and duplicates easily in account name rule
• Random numeric can also be generated like below

If this helps your question, hit Kudos and please click the 'Accept As Solution' button on the reply (or replies) that best answered your original question.