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

SystemUserName Generation using advance SQL query

anujapawar
New Contributor
New Contributor

I want to generate the systemusername according to following conditions. Please, lead me on how to achieve it:- 

  1. First character of Firstname and full Lastname

               Example: Firstname= John, Lastname= Doe, SamAccountName= JDoe

  1. Duplicates should start using the first two letters of the Firstname, and full last name, and continue adding next available letter until Firstname is exhausted.

Example: Firstname= John, Lastname= Doe, SamAccountName= JoDoe

Example: Firstname= John, Lastname= Doe, SamAccountName= JohDoe

  1. Duplicates once FirstName has been exhausted will have full Firstname and full Lastname incrementing numbers starting from 1 (SamAccountName cannot be reused)

               Example: JohnDoe1, JohnDoe2, JohnDoe3…

  1. If Lastname < 2 characters – use the first two letters of the Firstname, and full Lastname.

Example: Firstname= John, Lastname= D, SamAccountName= JoD

  1. Duplicates where Lastname < 2 characters – continue adding next available letter until Firstname is exhausted.

Example: Firstname= John, Lastname= D, SamAccountName=JohD

  1. Duplicates where Lastname < 2 characters and have exhausted adding letters from FirstName, use full Firstname, and full Lastname incrementing numbers from 1.

Example: Firstname= John, Lastname= D, SamAccountName= JohnD1, JohnD2

  1. Accented characters will be converted while generating SamAccountName.

             Example: Firstname= Jóhn, Lastname= Dóe, SamAccountName= JDoe

  1. If Firstname is missing, use Lastname.

            Example: Lastname= Doe, SamAccountName= Doe

  1. If Lastname is missing, use Firstname.

           Example: Firstname= John, SamAccountName= John

 

NOTE:

  1. Preferred Firstname and Lastname will be taken into consideration for creation of SamAccountName if available in place of the Firstname and Lastname.
  2. SamAccountName generation will have a minimum of 3 characters and maximum of 20 characters.
1 REPLY 1

rushikeshvartak
All-Star
All-Star

Could you kindly provide a detailed query tried so far during the execution of this process? Your assistance in furnishing this information would greatly aid in the analysis and resolution of any issues .


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