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

Generate sysytemusername with capital N followed by 6 digit random numbers padded with zeros

VisveswaraReddy
New Contributor
New Contributor

Hi,

we need to generate sysytemusername with capital N followed by 6 digit random numbers padded with zeros, for this we made global config with SELECT concat(UPPER('N'),(FLOOR(RAND()*1000000))),

but their is one more use case 

If the users.csv file is uploaded in Saviynt twice with Same user details, It is creating New Systemusername every time.
The need is " If the user has Systemusername in Saviynt, it should not create a new Systemusername for the user. It should create Systemusername only when User doesn't have systemusername in Saviynt".

We tried with below preprocessor queries to achieve the use case but did not work

1.{

"ADDITIONALTABLES": {
"USERS": "SELECT USERKEY,username,CUSTOMPROPERTY6,STATUSKEY FROM USERS"
}, "COMPUTEDCOLUMNS": [
"EMPLOYEEID", "STATUSKEY"
], "PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET STATUSKEY = (case when customproperty6='A' then '1' else '0' end)", "UPDATE NEWUSERDATA N SET N.systemusername = (select U.systemusername from USERS U where N.username=U.username)"
]

}2.{

"ADDITIONALTABLES": {
"USERS": "SELECT USERKEY,username,CUSTOMPROPERTY6,STATUSKEY,SYSTEMUSERNAME FROM USERS"
}, "COMPUTEDCOLUMNS": [
"EMPLOYEEID", "STATUSKEY", "SYSTEMUSERNAME"
], "PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET STATUSKEY = (case when customproperty6='A' then '1' else '0' end)", "UPDATE NEWUSERDATA NU INNER JOIN USERS U ON NU.EMPLOYEEID = U.USERNAME SET NU.SYSTEMUSERNAME = U.SYSTEMUSERNAME WHERE NU.SYSTEMUSERNAME IS NULL"
]

}

[This message has been edited by moderator to move a reply comment to his own new post]

2 REPLIES 2

NM
Regular Contributor III
Regular Contributor III

Hi @VisveswaraReddy ,

try below once..

{
"ADDITIONALTABLES": {
"USERS": "SELECT USERKEY,USERNAME,CUSTOMPROPERTY6,STATUSKEY,SYSTEMUSERNAME FROM USERS"
}, "COMPUTEDCOLUMNS": [
"CUSTOMPROPERTY51"
], "PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA NU SET NU.CUSTOMPROPERTY51=(SELECT U.SYSTEMUSERNAME FROM CURRENTUSERS U where U.USERNAME=NU.USERNAME AND U.SYSTEMUSERNAME IS NOT NULL)"
]
}

replace cp51 with systemusername

rushikeshvartak
All-Star
All-Star

 

{
"ADDITIONALTABLES": {
"USERS": "SELECT USERKEY,USERNAME,CUSTOMPROPERTY6,STATUSKEY,SYSTEMUSERNAME FROM USERS"
}, "COMPUTEDCOLUMNS": [
"SYSTEMUSERNAME"
], "PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA NU SET NU.SYSTEMUSERNAME=(SELECT U.SYSTEMUSERNAME FROM CURRENTUSERS U where U.USERNAME=NU.USERNAME AND U.SYSTEMUSERNAME IS NOT NULL)"
]
}


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