what is the syntax for modifyuserjson in saviynt db connector?

[theosveg]

what is the syntax for modifyuserjson in saviynt db connector? is it even available to use? I am trying to preprocess user data, and am wondering if this is allowed.

[Raghu]

@theosveg 

https://forums.saviynt.com/t5/user-import/best-practice-for-inline-preprocessing/ta-p/35948

https://forums.saviynt.com/t5/identity-governance/username-and-systemusername-not-picking-up-from-gl...

Sample :

{
"ADDITIONALTABLES":
{
"USERS":"SELECT USERKEY,CUSTOMER,COMPANYNAME,COSTCENTER,JOBCODEDESC FROM USERS",
"CUSTOMER":"SELECT CUSTOMERKEY,CUSTOMERNAME FROM CUSTOMER"
},
"COMPUTEDCOLUMNS":
[
"CUSTOMER"
],
"PREPROCESSQUERIES":
[
"UPDATE NEWUSERDATA SET CUSTOMER = ( SELECT C.CUSTOMERNAME FROM CURRENTCUSTOMER C WHERE NEWUSERDATA.COMPANYNAME = C.CUSTOMERKEY)",
]
}

[PremMahadikar]

Hi @theosveg ,

This is allowed!

General Format: Integration Options with the Saviynt Database (saviyntcloud.com)

Sample MODIFYUSERDATAJSON:

{ 
"ADDITIONALTABLES": { 
"USERS":"SELECT USERKEY,username,employeeid FROM USERS" }, 
"COMPUTEDCOLUMNS":[ 
"username",
"systemusername",
"manager" 
], 
"PREPROCESSQUERIES": [ 
"UPDATE NEWUSERDATA SET USERNAME = (CASE WHEN username = '' then CONCAT(employeeid,'tst') else CONCAT(username,'tst') END)", 
"UPDATE NEWUSERDATA SET SYSTEMUSERNAME = username", 
"UPDATE NEWUSERDATA SET manager = (select u.username from currentusers u where u.employeeid = manager LIMIT 1)"
] 
}

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

[theosveg]

what is the issue with this format below: 

I keep getting this error: Error in Users Import - Error while processing data: Unknown column 'TEMPUSERS_1610364.CUSTOMPROPERTY1' in 'field list'

{
"ADDITIONALTABLES": {
"USERS": "SELECT STATUSKEY, USERNAME, CUSTOMPROPERTY1 FROM USERS"
},
"COMPUTEDCOLUMNS": [
"STATUSKEY"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET STATUSKEY = CASE WHEN currentusers.CUSTOMPROPERTY1 = 'Active' THEN '1' ELSE '0' END WHERE USERNAME IN (SELECT USERNAME FROM currentusers)"
]
}

[PremMahadikar]

@theosveg ,

Try below, its working for me.

{
"ADDITIONALTABLES": {
"USERS": "SELECT STATUSKEY, USERNAME, CUSTOMPROPERTY1 FROM USERS"
},
"COMPUTEDCOLUMNS": [
"STATUSKEY"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA NEW, CURRENTUSERS CU SET NEW.STATUSKEY = CASE WHEN CU.CUSTOMPROPERTY1 = 'Active' THEN '1' ELSE '0' END WHERE NEW.USERNAME=CU.USERNAME"
]
}

  Please make sure - statuskey, username, customproperty1 are in your csv file.

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

[theosveg]

say, I want to update existing users and not while import, what would be the way to go?

[PremMahadikar]

@theosveg ,

• If you are using Saviynt DB connector, configure Update User JSON with simple SQL query.

{
    "updateUserQry": [
        "UPDATE users u SET u.statuskey = 1 WHERE u.customproperty1='active' AND u.userkey = ${user.id};"]
  })

• Configure a user update rule to match the condition of a user and action to trigger 'Create Update User Task'
• You can run this rule via detective job to run for bulk users

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

[theosveg]

I am wondering if my approach is even valid, nothing happens after the task is created and moves to pending task, WSRETRY does nothing. If it helps, the user is updated from the UI through a form.

[rushikeshvartak]

Task proceessing and modifyuserjson has nothing to do. Check if json is proper 

share logs

[theosveg]

here's what I am using, { "UpdateUserQry" :["Update users u1 set u1.statuskey = (case when u1.customproperty1 = 'Active' and u1.employeetype = 'ABC' and u1.userkey='${user.id}' then 1 else 0 end) where u1.userkey='${user.id}'" ]}.

Sav4Sav DB connector, task is created on user updated in UI, but nothing happens after... What exactly is the expected behavior?

[rushikeshvartak]

Run WSRETRY for particular task and share logs

[theosveg]

am I supposed to see nothing populated for endpoint, SS etc? update user tasks only allows the selection of a connection..

[rushikeshvartak]

No . Run wsretry with task type as update user

[PremMahadikar]

@theosveg ,

• Have a csv export for the users you want to update
• Bulk user import using .csv file

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