Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.

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

deepa
New Contributor III
New Contributor III

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

12 REPLIES 12

PremMahadikar
Valued Contributor
Valued Contributor

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)

PremMahadikar_0-1715984799787.png

 

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

 

Raghu
Honored Contributor
Honored Contributor

@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

Raghu_0-1716037057299.png

 

 

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

 

 

 


Thanks,
Raghu
If this reply answered your question, Please Accept As Solution and hit Kudos.

deepa
New Contributor III
New Contributor III

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
Honored Contributor
Honored Contributor

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

 


Thanks,
Raghu
If this reply answered your question, Please Accept As Solution and hit Kudos.

deepa
New Contributor III
New Contributor III

Below is the sample mapping

CostcenterPrefix
SalesS01
MarketingS02
SuppliesS81

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.

CostcenterPrefixUserGenerated code (Prefix + Random number)
SalesS01User1S014567
MarketingS02User2S027685
SuppliesS81User3S816745

Please let me know if you have any further question

@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 


Regards,
Saathvik
If this reply answered your question, please Accept As Solution and give Kudos to help others facing similar issue.

Raghu
Honored Contributor
Honored Contributor

@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 )


Thanks,
Raghu
If this reply answered your question, Please Accept As Solution and hit Kudos.

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


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

@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

You can use preprocessor and store value on users custom property


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

@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


Regards,
Saathvik
If this reply answered your question, please Accept As Solution and give Kudos to help others facing similar issue.

PremMahadikar
Valued Contributor
Valued Contributor

@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
  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

PremMahadikar_0-1716240580613.png

PremMahadikar_2-1716240664118.png

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.