unique cn via sql query

[musthak_ahamad]

Hi 

we have a requirement that we need to generate the unique CN when the user is created or user name change is updated , am trying to use the preprocessor handler in this , but am not sure abt incrementing  the cn if duplicate found and remove all the special characters. does anyone have idea on this query ? how to generate a unique cn and store it in some cp value. below is the query i tried 

{
"ADDITIONALTABLES":{"USERS": "SELECT username,firstname,lastname, CUSTOMPROPERTY45, customproperty46,EMPLOYEEID from users"},
"COMPUTEDCOLUMNS": ["CUSTOMPROPERTY45","CUSTOMPROPERTY46"],
"TABLEINDEXES": {
"currentusers": ["username"]
},
"PREPROCESSQUERIES": ["ALTER TABLE CURRENTUSERS ADD INDEX username (username ASC)",

"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.EMPLOYEEID=CURRENTUSERS.EMPLOYEEID SET NEWUSERDATA.CUSTOMPROPERTY45=CASE WHEN ((CURRENTUSERS.CUSTOMPROPERTY45 IS NULL OR CURRENTUSERS.CUSTOMPROPERTY45 ='') AND (SELECT COUNT(USERNAME) FROM CURRENTUSERS WHERE CURRENTUSERS.LASTNAME=NEWUSERDATA.LASTNAME AND NEWUSERDATA.FIRSTNAME=CURRENTUSERS.FIRSTNAME AND NEWUSERDATA.EMPLOYEEID <> CURRENTUSERS.EMPLOYEEID)=0) THEN CONCAT(TRIM(NEWUSERDATA.FIRSTNAME),' ',TRIM(NEWUSERDATA.LASTNAME)) ELSE (CURRENTUSERS.CUSTOMPROPERTY45) END",

"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.EMPLOYEEID = CURRENTUSERS.EMPLOYEEID SET NEWUSERDATA.CUSTOMPROPERTY46 = (CASE WHEN (NEWUSERDATA.CUSTOMPROPERTY45 <> CURRENTUSERS.CUSTOMPROPERTY45 OR CURRENTUSERS.CUSTOMPROPERTY45 IS NULL) And (SELECT COUNT(CURRENTUSERS.CUSTOMPROPERTY45) AS DUPLICATEUSERCOUNT FROM CURRENTUSERS GROUP BY CURRENTUSERS.CUSTOMPROPERTY45 HAVING CURRENTUSERS.CUSTOMPROPERTY45 = NEWUSERDATA.CUSTOMPROPERTY45) then ELSE (CURRENTUSERS.CUSTOMPROPERTY46) END)"]
}

i tried to use systemgeneration rule to generate the unique cn but the problem is on first & last  name change the systemuser rule dont generate new cn .

[pmahalle]

Hi @musthak_ahamad ,

You want to generate cn for AD provisioning, right? You can use ACCOUNTNAMERULE in AD connection to generate unique cn for each user.

Example:

CN=${user.lastname}\\, ${user.firstname} (Global-V)
[${user.username}],CN=Users,DC=abccompany,DC=com###CN=${user.lastname}1\\,
${user.firstname}1 (Global-V)
[${user.username}1],CN=Users,DC=abccompany,DC=com###CN=${user.lastname}\\,
${user.firstname} (Global-V) [${user.username}2],CN=Users,DC=abccompany,DC=com

Refer:

https://docs.saviyntcloud.com/bundle/AD-v23x/page/Content/Configuring-the-Integration-for-Provisioni...

[musthak_ahamad]

No , the problem is every time we trigger the update account task it is going and updating  the cn with incremental value which should not happen. that is the reason we are not depending the account rule name uniquness and doing it out of json and just passing the generated  unique cn to account rule name .

account rule name:

CN=${user.firstname} ${user.lastname},${if(user.orgunitid.contains('(')){'OU='+user.orgunitid.toString().substring(0,user.orgunitid.indexOf('('))+',OU=Users,OU=Saviynt,DC=client,DC=local'}}###CN=${user.firstname} ${user.lastname}1,${if(user.orgunitid.contains('(')){'OU='+user.orgunitid.toString().substring(0,user.orgunitid.indexOf('('))+',OU=Users,OU=Saviynt,DC=client,DC=local'}}

[rushikeshvartak]

Store values in account or users and you should be able to prepare incremental