Click HERE to see how Saviynt Intelligence is transforming the industry. |
05/10/2024 06:26 AM
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.{
2.{
05/10/2024 07:10 AM
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
05/10/2024 09:36 AM
Hi Prem,
We tried with your code and the results remains same. Still the issue persists.
Any other suggestions ?
Thanks!!
05/10/2024 09:50 AM
{
"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)"
]
}
05/10/2024 10:13 AM
@rushikeshvartak
With the above code, Users are not inserting into the saviynt.
Any other ideas might help.
Thanks!!
05/10/2024 10:02 AM - edited 05/10/2024 10:03 AM
@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
05/10/2024 10:09 AM
With the above, I got response like below and the user is not inserting into the saviynt.
Thanks!!
05/10/2024 10:20 AM - edited 05/10/2024 10:21 AM
@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
05/10/2024 10:26 AM
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!!
05/10/2024 10:37 AM
@KME ,
Yes, it's working fine with covering both the case
While uploading - you have checked - Generate systemusername right?
Can you pass the file you are using.
05/10/2024 10:52 AM - edited 05/10/2024 11:02 AM
05/10/2024 11:10 AM - edited 05/10/2024 11:10 AM
@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
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
05/13/2024 11:06 PM
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!!