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

SAV file MODIFYUSERDATAJSON update

IAM
Regular Contributor
Regular Contributor

I have the below query in a custom query job. Instead, I need to move this to a SAV file to process instead of in the custom query job but I'm not sure how to do this.

Here is the SQL query:

update users inner join user_accounts on users.MANAGER = user_accounts.USERKEY and users.STATUSKEY=1 inner join accounts on user_accounts.ACCOUNTKEY = accounts.ACCOUNTKEY and accounts.endpointkey in (1) and accounts.status = '1' set users.CUSTOMPROPERTY1 = accounts.customproperty15

Here is what I have currently in my SAV file that I need to modify:

#MODIFYUSERDATAJSON={ "ADDITIONALTABLES" : { "USERS" : "SELECT USERKEY,USERNAME,CUSTOMPROPERTY5,STATUSKEY FROM USERS" }, "COMPUTEDCOLUMNS" : ["MANAGER","EMPLOYEEID","CUSTOMPROPERTY39","CUSTOMPROPERTY34"], "PREPROCESSQUERIES" : [ "UPDATE NEWUSERDATA SET MANAGER = CUSTOMPROPERTY5","UPDATE NEWUSERDATA SET EMPLOYEEID = USERNAME","UPDATE NEWUSERDATA SET CUSTOMPROPERTY39 = 'UNPROCESSED'","UPDATE NEWUSERDATA SET CUSTOMPROPERTY34 = ( CASE WHEN (STATUSKEY = 'ACTIVE_ASSIGN') THEN 'ACTIVE' WHEN (STATUSKEY = 'SUSP_ASSIGN') THEN 'SUSPENDED' ELSE ('TERMED') END )"]}

 

8 REPLIES 8

Saathvik
All-Star
All-Star

@IAM : You can try below JSON, Also I see you are mapping MANGER and EMPLOYEEID with CUSTOMPROPERTY5 and USERNAME columns of user respectively using MODIFYUSERDATAJSON which can be mapped directly as part of import itself isn't it?

 

#MODIFYUSERDATAJSON={ "ADDITIONALTABLES": { "USERS": "SELECT USERKEY,USERNAME,MANAGER,CUSTOMPROPERTY1,CUSTOMPROPERTY5,STATUSKEY FROM USERS", "ACCOUNTS": "SELECT ACCOUNTKEY, NAME, ACCOUNTID, ENDPOINTKEY, CUSTOMPROPERTY15, STATUS FROM ACCOUNTS WHERE ENDPOINTKEY=1", "USER_ACCOUNTS": "SELECT USERKEY,ACCOUNTKEY FROM USER_ACCOUNTS" }, "COMPUTEDCOLUMNS": [ "MANAGER", "EMPLOYEEID", "CUSTOMPROPERTY39", "CUSTOMPROPERTY34", "CUSTOMPROPERTY1" ], "PREPROCESSQUERIES": [ "UPDATE NEWUSERDATA SET MANAGER = CUSTOMPROPERTY5", "UPDATE NEWUSERDATA SET EMPLOYEEID = USERNAME", "UPDATE NEWUSERDATA SET CUSTOMPROPERTY39 = 'UNPROCESSED'", "UPDATE NEWUSERDATA SET CUSTOMPROPERTY34 = ( CASE WHEN (STATUSKEY = 'ACTIVE_ASSIGN') THEN 'ACTIVE' WHEN (STATUSKEY = 'SUSP_ASSIGN') THEN 'SUSPENDED' ELSE ('TERMED') END )", "UPDATE NEWUSERDATA NU INNER JOIN CURRENTUSER_ACCOUNTS CCA on NU.MANAGER=CCA.USERKEY AND NU.STATUSKEY=1 INNER JOIN CURRENTACCOUNTS CA ON CCA.ACCOUNTKEY=CA.ACCOUNTKEY AND CA.ENDPOINTKEY=1 AND CA.STATUS=1 SET NU.CUSTOMPROPERTY1=CA.CUSTOMPROPERTY15" ] }

 


Regards,
Saathvik
If this reply answered your question, please Accept As Solution and give Kudos to help others facing similar issue.

IAM
Regular Contributor
Regular Contributor

@Saathvik Thank you. This is the error I'm getting now

Error in Users Import - Error while processing
data: Data truncation: Truncated incorrect DOUBLE
value: 'ACTIVE_ASSIGN'

@IAM: ACTIVE_ASSIGN which attribute is having this value and to which user column it is mapped ?


Regards,
Saathvik
If this reply answered your question, please Accept As Solution and give Kudos to help others facing similar issue.

here was an attempt to insert a value into a column with a DOUBLE data type, but the value being inserted is not a valid DOUBLE. Specifically, the value 'ACTIVE_ASSIGN' cannot be converted to a DOUBLE because it is a string.


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

IAM
Regular Contributor
Regular Contributor

@rushikeshvartak @Saathvik 

This was working before. This is how the active/inactive status comes in from HR. We are just setting CP34 either to ACTIVE, SUSPENDED, or TERMED. This hasn't changed, the only thing I added was what Saathvik gave.

@IAM : Looks like you are assigning STATUSKEY column of user to a string which it doesn't support it only accepts 1 or 0. This I am assuming based on the existing logic you have in your MODIFYUSERDATAJSON.

"UPDATE NEWUSERDATA SET CUSTOMPROPERTY34 = (CASE WHEN STATUSKEY = 'ACTIVE_ASSIGN' THEN 'ACTIVE' WHEN STATUSKEY = 'SUSP_ASSIGN' THEN 'SUSPENDED' ELSE ('TERMED') END )",

Saathvik_0-1715957699188.png

Please correct you logic and try again.


Regards,
Saathvik
If this reply answered your question, please Accept As Solution and give Kudos to help others facing similar issue.

Share input record details masking sensitive info


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

Raghu
All-Star
All-Star

@IAM  try below

{
"ADDITIONALTABLES": {
"USERS": "SELECT USERKEY,MANAGER,USERNAME, CUSTOMPROPERTY5, STATUSKEY FROM USERS",
"USER_ACCOUNTS": "SELECT USERKEY, ACCOUNTKEY FROM USER_ACCOUNTS",
"ACCOUNTS": "SELECT ACCOUNTKEY, CUSTOMPROPERTY15 FROM ACCOUNTS WHERE ENDPOINTKEY = 1 AND STATUS = '1'"
},
"COMPUTEDCOLUMNS": ["MANAGER", "EMPLOYEEID", "CUSTOMPROPERTY39", "CUSTOMPROPERTY34","CUSTOMPROPERTY1"],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET MANAGER = CUSTOMPROPERTY5",
"UPDATE NEWUSERDATA SET EMPLOYEEID = USERNAME",
"UPDATE NEWUSERDATA SET CUSTOMPROPERTY39 = 'UNPROCESSED'",
"UPDATE NEWUSERDATA SET CUSTOMPROPERTY34 = (CASE WHEN STATUSKEY = 'ACTIVE_ASSIGN' THEN 'ACTIVE' WHEN STATUSKEY = 'SUSP_ASSIGN' THEN 'SUSPENDED' ELSE ('TERMED') END )",
"UPDATE NEWUSERDATA INNER JOIN USER_ACCOUNTS ON NEWUSERDATA.MANAGER = USER_ACCOUNTS.USERKEY AND NEWUSERDATA.STATUSKEY = 1 INNER JOIN ACCOUNTS ON USER_ACCOUNTS.ACCOUNTKEY = ACCOUNTS.ACCOUNTKEY SET NEWUSERDATA.CUSTOMPROPERTY1 = ACCOUNTS.CUSTOMPROPERTY15"
]
}


Thanks,
Raghu
If this reply answered your question, Please Accept As Solution and hit Kudos.