Saviynt unveils its cutting-edge Intelligence Suite products to revolutionize Identity Security!
Click HERE to see how Saviynt Intelligence is transforming the industry.
Saviynt Copilot Icon

Avoid Systemusername creation for mutilple times

KME
New Contributor III
New Contributor III

Hi Folks,

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

but there 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 (Be it how many times those users have been uploaded to Saviynt). 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"]}
 
Any suggestions/ resolutions might be helpful
 
Thanks!!
12 REPLIES 12

PremMahadikar
All-Star
All-Star

Hi @KME ,

Can you try the below code:

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

 

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

KME
New Contributor III
New Contributor III

Hi Prem,

We tried with your code and the results remains same. Still the issue persists.

Any other suggestions ?

Thanks!!

{
  "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) AND N.USERNAME NOT IN (select CURRENTUSERS.username from CURRENTUSERS where systemusername is not null)"
  ]
}

Regards,
Rushikesh Vartak
If this helped you move forward, click 'Kudos'. If it solved your query, select 'Accept As Solution'.

KME
New Contributor III
New Contributor III

@rushikeshvartak 

With the above code, Users are not inserting into the saviynt.

Any other ideas might help.

Thanks!!

@KME ,

Small error in the code: NU.username=U.username

Below code working for me:

 

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

 

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

KME
New Contributor III
New Contributor III

@PremMahadikar 

With the above, I got response like below and the user is not inserting into the saviynt.

KME_0-1715360907978.png

Thanks!!

@KME ,

Seems like you are not passing right columns. The code work correctly.

My csv file has below columns:

username,statuskey,systemusername,customproperty6

Pass the above columns, you will get the result.

 

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

KME
New Contributor III
New Contributor III

I have passed all 4 columns and 3 values out of 4 as I kept systemusername as null. 
The usecase is like if a user doesn't have systemusername value in both csv file and saviynt, then only it should create-- to achieve this, i have given systemusername as empty.

Thanks!!

 

@KME ,

Yes, it's working fine with covering both the case

  • If systemusername in both saviynt and file isn't present - it is creating new systemusername
  • if systemusername is present in Saviynt, it will not generate another systemusername even though there is any value or not in the file

While uploading - you have checked - Generate systemusername right?

Can you pass the file you are using.

 

KME
New Contributor III
New Contributor III

Please find the reference test csv file.

Also, I checked generate systemusername.

Thanks!!

@KME ,

As mentioned above, there is no statuskey and username in your csv file.

We have used NU.username=U.username, if no username in the file, there would be no mapping with existing users in saviynt

  • Please add statuskey - leave it blank
  • If you generating username during import, In the code for mapping use common attribute. (for example: I see you have email - you can use NU.email=U.email)

Try the updated code:

 

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

 

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

KME
New Contributor III
New Contributor III

@PremMahadikar 

Thanks for the idea. 

We tried with the above code, but it is partially working.

Failures:
if the new user with systemusername is uploaded via UI, Saviynt is creating a new systemusername and it is not considering csv file systemusername -- which fails the primary UseCase.

Success cases:
But if the same file uploaded again, it is not creating any new systemusername for existing users.

To conclude all, Pre-processor is able to recognize systemusername from currentusers even csv file doesn't have systemusername

But it fails to recognize the systemusername even it is present in csv file for new users (Which are not present in Saviynt and yet to imported to Saviynt) and it is creating a new system username for those  new users alone.

Any resolution ideas will be helpful.

Thanks!!