We are delighted to share our new EIC Delivery Methodology for efficiently managing Saviynt Implementations and delivering quick time to value. CLICK HERE.

How to create user update JSON in DB connection

MK_0o7
New Contributor III
New Contributor III

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.

16 REPLIES 16

pmahalle
All-Star
All-Star

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.


Pandharinath Mahalle(Paddy)
If this reply answered your question, please Accept As Solution to help other who may have a same problem. Give Kudos 🙂

rushikeshvartak
All-Star
All-Star

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"
  ]
}

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

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?

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.


Pandharinath Mahalle(Paddy)
If this reply answered your question, please Accept As Solution to help other who may have a same problem. Give Kudos 🙂

armaanzahir
Valued Contributor
Valued Contributor

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.

Regards,
Md Armaan Zahir

MK_0o7
New Contributor III
New Contributor III

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
New Contributor III
New Contributor III

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
Valued Contributor
Valued Contributor

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

 

Regards,
Md Armaan Zahir

MK_0o7
New Contributor III
New Contributor III

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
New Contributor III
New Contributor III

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.

{ "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


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

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

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


Pandharinath Mahalle(Paddy)
If this reply answered your question, please Accept As Solution to help other who may have a same problem. Give Kudos 🙂

rmishra
New Contributor II
New Contributor II

Thank you so much @pmahalle 

rmishra
New Contributor II
New Contributor II

can we not update this from userupdatejson?

 

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


Pandharinath Mahalle(Paddy)
If this reply answered your question, please Accept As Solution to help other who may have a same problem. Give Kudos 🙂