How to create user update JSON in DB connection

[MK_0o7]

Hi Team,

I'm looking to merge 2 USER UPDATE JSON's into 1 connection.

Using below code for the same

{ "UpdateUserQry" :["Update users u1 set u1.customproperty5 = (case when u1.customproperty5 is null and u1.statuskey=1 and u1.employeeType = 'Intern' and u1.userkey='${user.id}' then 'NEW EMPLOYEE PROFILE CREATED Intern' when u1.customproperty5 is null and u1.statuskey=1 and u1.employeeType = 'External' and u1.userkey='${user.id}' then 'NEW EMPLOYEE PROFILE CREATED EXT ' else 'user.customproperty5' end)","update users u1 set u1.systemusername = ''","update users u1 set u1.username = (case when u1.customproperty5 is null and u1.statuskey=1 and u1.employeeType='External' and u1.userkey='${user.id}' then 'ID created via Function randomly'' else 'u1.username' end)"]}

When saving the connection, throwing error to use where condition. And  i've not used where condition. Any help on this is appreciated.

[pmahalle]

Hi @MK_0o7 ,

It is not recommended to run update query without where clause, as it might update all the records in the table.

So better to put where clause. If you don’t have specific where clause and want to update all the records always whenever update account task created, can you try where 1=1 in your query.

[rushikeshvartak]

That is saviynt gauradrail that every json in update account and remove account should have where clause this is validated whethere json contains where keyword or not 

{
  "UpdateUserQry": [
    "Update users u1 set u1.customproperty5 = (case when u1.customproperty5 is null and u1.statuskey=1 and u1.employeeType = 'Intern' and u1.userkey='${user.id}' then 'NEW EMPLOYEE PROFILE CREATED Intern' when u1.customproperty5 is null and u1.statuskey=1 and u1.employeeType = 'External' and u1.userkey='${user.id}' then 'NEW EMPLOYEE PROFILE CREATED EXT ' else 'user.customproperty5' end)where 1=1",
    "update users u1 set u1.systemusername = ''where 1=1",
    "update users u1 set u1.username = (case when u1.customproperty5 is null and u1.statuskey=1 and u1.employeeType='External' and u1.userkey='${user.id}' then 'ID created via Function randomly'' else 'u1.username' end)where 1=1"
  ]
}

[MK_0o7]

Hi @pmahalle & @rushikeshvartak , Thanks for the response and helping me with the code. I think the above code will update the values based on conditions in case block.So, all the attributes won't be updated. Correct me if i'm wrong.

And 1 question, what is the meaning of using "WHERE 1=1". Or just to fulfill the syntax of using where condition?

[pmahalle]

Hi @MK_0o7 ,

Where 1=1 became true always and allow you to update all the rows. Yes it can fulfill the syntax in case you does not have where clause and want to update all the rows.

[armaanzahir]

The update statement is aimed for all the users in the users table. the update statement will be invoked for all user entries and based on the case statement the attributes of the corresponding user entry would be populated. If you want to intentionally make an update only for the user for whom the request/task has been created then use the following 

{
"UpdateUserQry": [
"Update users u1 set u1.customproperty5 = (case when u1.customproperty5 is null and u1.statuskey=1 and u1.employeeType = 'Intern' and u1.userkey='${user.id}' then 'NEW EMPLOYEE PROFILE CREATED Intern' when u1.customproperty5 is null and u1.statuskey=1 and u1.employeeType = 'External' and u1.userkey='${user.id}' then 'NEW EMPLOYEE PROFILE CREATED EXT ' else 'user.customproperty5' end) where u1.userkey='${user.id}'",
"update users u1 set u1.systemusername = '' where u1.userkey='${user.id}'",
"update users u1 set u1.username = (case when u1.customproperty5 is null and u1.statuskey=1 and u1.employeeType='External' and u1.userkey='${user.id}' then 'ID created via Function randomly'' else 'u1.username' end) where u1.userkey='${user.id}'"
]
}

Ideally, a task operation should be associated to a single user entry. Saviynt allows us to modify multiple records from multiple tables where we have complex use cases, but if your intention is to update only the user entry for whom the request/ task has been generated, it's highly recommended you use this approach.

[MK_0o7]

Thanks for the info @armaanzahir. I want to update for users who are pending tasks alone.

While testing, I found that only the 1st set block is working in the above code i.e., Only CP5 is getting updated but not the rest attributes like setting systemusername to null and username changing to random number is working not working as expected.

[MK_0o7]

Hi Team, Can anyone help me with code in resolving this issue to make sure that my system username and username are updated according to the condition given in the code.

To brief my issue, I want 3 attributes to be updated(CP5, systemusername,username) but only the 1st attributes in getting updated(CP5) and systemusername & username.

Please help!

[armaanzahir]

Hi @MK_0o7 ,

Are you getting an error in logs? Can you provide the logs when the task is being executed?

Also, if you want to update all the three attributes, better to use a single update statement instead of three, as all three have the same where condition:

{
"UpdateUserQry": [
"Update users u1 set u1.customproperty5 = (case when u1.customproperty5 is null and u1.statuskey=1 and u1.employeeType = 'Intern' and u1.userkey=${user.id} then 'NEW EMPLOYEE PROFILE CREATED Intern' when u1.customproperty5 is null and u1.statuskey=1 and u1.employeeType = 'External' and u1.userkey=${user.id} then 'NEW EMPLOYEE PROFILE CREATED EXT ' else 'user.customproperty5' end),u1.systemusername = '',u1.username = (case when u1.customproperty5 is null and u1.statuskey=1 and u1.employeeType='External' and u1.userkey=${user.id} then 'ID created via Function randomly'' else 'u1.username' end) where u1.userkey=${user.id}",
]
}

Would like to know the use case, as there are many issues with the above query updates on the users table

[MK_0o7]

Hi @armaanzahir , Thanks for the reply.

User case --> I've users(Interns & externals) who are onboarding via SNOW. Once user's profile is created in Saviynt, I've 2 User update rules(1 for Interns and 1 for externals) which will trigger based on employee type and creates user update tasks (2 DB connections - 1 for intern and 1 for External).

So, As a part of optimizing this I would like to create only 1 User update rule with created via API as trigger action which will create user update task (only 1 DB connection instead of 2). Hence, in my User update JSON I'm trying to merge the JSON's from 2 DB connections to 1 DB connection. Hence my requirement.

Please suggest if any better way to achieve this!

[MK_0o7]

Hi Team,

I'm still facing the same issue...only CP5 is getting updated but not all 3. I'm using the below JSON.

{ "UpdateUserQry" :["Update users u1 set u1.customproperty5 = (case when u1.customproperty5 is null and u1.statuskey=1 and u1.employeeType = 'intern' and u1.regioncode='EUR' and u1.userkey='${user.id}' then 'NEW EMPLOYEE PROFILE CREATED intern EUR' when u1.customproperty5 is null and u1.statuskey=1 and u1.employeeType = 'external' and u1.regioncode='EUR' and u1.userkey='${user.id}' then 'NEW EMPLOYEE PROFILE CREATED EXT EUR' else 'user.customproperty5' end),u1.systemusername = '',u1.username = (case when u1.customproperty5 is null and u1.statuskey=1 and u1.employeeType='external' and u1.regioncode='EUR' and u1.userkey='${user.id}' then GenerateCIDExternalUsersEUR() else u1.username end) where u1.userkey='${user.id}'" ]}

Please help me out here.

As far as logs, I'm not able to get any logs on the error.

[rushikeshvartak]

{ "UpdateUserQry" :["Update users u1 set u1.username = (case when u1.customproperty5 is null and u1.statuskey=1 and u1.employeeType='external' and u1.regioncode='EUR' and u1.userkey='${user.id}' then GenerateCIDExternalUsersEUR() else u1.username end) where u1.userkey='${user.id}'" ]}

Try if above works

[rmishra]

Hi @rushikeshvartak 

Please help me, I am trying to write a query to update users cp 19 with accounts  table account ID which is DN

EXample

Select u1.username as username, a.AccountID as u1.customproperty19 from users u1 join user_accounts ua on ua.userkey = u1.userkey join accounts a on a.accountkey = ua.accountkey where a.endpointkey in (select endpointkey from endpoints where endpointname like '%AD CANARY%') and a.status in ('1','2','Manually Provisioned') and u1.employeeType='Internal Employee' and 

I am updating this through userupdateJson

[pmahalle]

Hi @rmishra ,

Use below query in your User Import XML to update user's CP19 with account's accountID. This query will take care to skip the records, if CP19 is already updated with correct accountID.

SELECT U.USERNAME AS USERNAME, A.ACCOUNTID AS CUSTOMPROPERTY19 FROM USERS U, ACCOUNTS A, USER_ACCOUNTS UA, ENDPOINTS E WHERE U.USERKEY=UA.USERKEY AND UA.ACCOUNTKEY = A.ACCOUNTKEY AND A.ENDPOINTKEY=E.ENDPOINTKEY AND E.ENDPOINTNAME LIKE '%AD CANARY%' AND A.ACCOUNTID IS NOT NULL AND A.STATUS IN ('1','2','Manually Provisioned') AND (U.CUSTOMPROPERTY19 IS NULL OR U.CUSTOMPROPERTY19 != A.ACCOUNTID)

Let me know if it helps

[rmishra]

Thank you so much @pmahalle 

[rmishra]

can we not update this from userupdatejson?

[pmahalle]

Hi @rmishra ,

You can use UPDATEUSERJSON, where you need to write query and that can be executed through update rule on user updation.

Here, you want to update account's accountid in user's CP36 and not triggering any updaterule, so better approach would be SAV4SAV