Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.

User Pre-processor Config JSON - Account Attributes at User Custom Attributes

Saviynt_Savvy
Regular Contributor
Regular Contributor

Hi All,
I'm using the version 24.2.
I am updating the users using .CSV upload.

I have the requirement of bringing the user's account's attribute values to User's customproperty attributes.

A User have the accounts in Endpoint-A and Endpoint-B.
I need to bring the user's account name from Endpoint-A to user's customproperty8 (at user level) and
account's customproperty29 from Endpoint-B to the user's customproperty9.
While uploading the users using .CSV file, I am using User Pre-Processor Config JSON to update the user's customproperty8,9.
But I don't see any user updated after the .CSV upload and the updated records count=0.

What should I modify in the below pre-processor config JSON:

{
"ADDITIONALTABLES": {
"USERS": "SELECT * FROM USERS",
"ACCOUNTS": "SELECT NAME,ENDPOINTKEY,ACCOUNTKEY,CUSTOMPROPERTY29 FROM ACCOUNTS WHERE ENDPOINTKEY IN (6,9)",
"USER_ACCOUNTS": "SELECT USERKEY,ACCOUNTKEY FROM USER_ACCOUNTS WHERE ACCOUNTKEY IN (SELECT DISTINCT ACCOUNTKEY FROM ACCOUNTS WHERE ENDPOINTKEY IN (6,9))"
},
"COMPUTEDCOLUMNS": [
"CUSTOMPROPERTY8",
"CUSTOMPROPERTY9"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET NEWUSERDATA.CUSTOMPROPERTY8 = (SELECT CURRENTACCOUNTS.NAME FROM CURRENTACCOUNTS INNER JOIN CURRENTUSER_ACCOUNTS ON CURRENTUSER_ACCOUNTS.ACCOUNTKEY=CURRENTACCOUNTS.ACCOUNTKEY INNER JOIN CURRENTUSERS ON CURRENTUSERS.USERKEY = CURRENTUSER_ACCOUNTS.USERKEY WHERE CURRENTUSERS.CUSTOMPROPERTY1=NEWUSERDATA.CUSTOMPROPERTY1 AND ACCOUNTS.ENDPOINTKEY=6)",
"UPDATE NEWUSERDATA SET NEWUSERDATA.CUSTOMPROPERTY9 = (SELECT CURRENTACCOUNTS.CUSTOMPROPERTY29 FROM CURRENTACCOUNTS INNER JOIN CURRENTUSER_ACCOUNTS ON CURRENTUSER_ACCOUNTS.ACCOUNTKEY=CURRENTACCOUNTS.ACCOUNTKEY INNER JOIN CURRENTUSERS ON CURRENTUSERS.USERKEY = CURRENTUSER_ACCOUNTS.USERKEY WHERE CURRENTUSERS.CUSTOMPROPERTY1=NEWUSERDATA.CUSTOMPROPERTY1 AND ACCOUNTS.ENDPOINTKEY=9)"
]
}

Thanks & Regards,
SaviyntSavvy

9 REPLIES 9

NM
Regular Contributor III
Regular Contributor III

Hi @Saviynt_Savvy , can you try processing only customproperty8 update once and see the result

Saviynt_Savvy
Regular Contributor
Regular Contributor

Hi @NM ,
I tired the below query in the Pre-Processor for updating only CP8 and it is not updating the users. The updated records count is zero.

Pre-Processor Query:
{
"ADDITIONALTABLES": {
"USERS": "SELECT * FROM USERS",
"ACCOUNTS": "SELECT NAME,ENDPOINTKEY,ACCOUNTKEY,CUSTOMPROPERTY29,CUSTOMPROPERTY5 FROM ACCOUNTS WHERE ENDPOINTKEY IN (6,9) ",
"USER_ACCOUNTS": "SELECT USERKEY,ACCOUNTKEY FROM USER_ACCOUNTS WHERE ACCOUNTKEY IN (SELECT DISTINCT ACCOUNTKEY FROM ACCOUNTS WHERE ENDPOINTKEY IN (6,9))"
},
"COMPUTEDCOLUMNS": [
"CUSTOMPROPERTY8",
"CUSTOMPROPERTY9"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET NEWUSERDATA.CUSTOMPROPERTY8 = (SELECT CURRENTACCOUNTS.NAME FROM CURRENTACCOUNTS INNER JOIN CURRENTUSER_ACCOUNTS ON CURRENTACCOUNTS.ACCOUNTKEY=CURRENTUSER_ACCOUNTS.ACCOUNTKEY INNER JOIN CURRENTUSERS ON CURRENTUSER_ACCOUNTS.USERKEY = CURRENTUSERS.USERKEY WHERE CURRENTUSERS.CUSTOMPROPERTY1=NEWUSERDATA.CUSTOMPROPERTY1 AND ACCOUNTS.ENDPOINTKEY=6)"
]
}

Thanks & Regards,
SaviyntSavvy

NM
Regular Contributor III
Regular Contributor III

@Saviynt_Savvy  use CURRENTACCOUNTS.endpointkey instead of ACCOUNTS 

rushikeshvartak
All-Star
All-Star

Does it show data in data analyzer


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

Hi @rushikeshvartak ,
I have ran the query in the Data Analyzer like below and able to get the result.

Saviynt_Savvy_0-1715784260950.png

Any thing not correct here!

Thanks & Regards,
SaviyntSavvy

PremMahadikar
Valued Contributor
Valued Contributor

Hi @Saviynt_Savvy ,

The below code is working for me.  Please try it.

 

{
"ADDITIONALTABLES": {
"USERS": "SELECT * FROM USERS",
"ACCOUNTS": "SELECT NAME,ENDPOINTKEY,ACCOUNTKEY,CUSTOMPROPERTY29 FROM ACCOUNTS",
"USER_ACCOUNTS": "SELECT USERKEY,ACCOUNTKEY FROM USER_ACCOUNTS"
},
"COMPUTEDCOLUMNS": [
"CUSTOMPROPERTY8",
"CUSTOMPROPERTY9"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET NEWUSERDATA.CUSTOMPROPERTY8 = (SELECT CURRENTACCOUNTS.NAME FROM CURRENTACCOUNTS INNER JOIN CURRENTUSER_ACCOUNTS ON CURRENTUSER_ACCOUNTS.ACCOUNTKEY=CURRENTACCOUNTS.ACCOUNTKEY INNER JOIN CURRENTUSERS ON CURRENTUSERS.USERKEY = CURRENTUSER_ACCOUNTS.USERKEY WHERE CURRENTUSERS.CUSTOMPROPERTY1=NEWUSERDATA.CUSTOMPROPERTY1 AND CURRENTACCOUNTS.ENDPOINTKEY=3)",
"UPDATE NEWUSERDATA SET NEWUSERDATA.CUSTOMPROPERTY9 = (SELECT CURRENTACCOUNTS.CUSTOMPROPERTY29 FROM CURRENTACCOUNTS INNER JOIN CURRENTUSER_ACCOUNTS ON CURRENTUSER_ACCOUNTS.ACCOUNTKEY=CURRENTACCOUNTS.ACCOUNTKEY INNER JOIN CURRENTUSERS ON CURRENTUSERS.USERKEY = CURRENTUSER_ACCOUNTS.USERKEY WHERE CURRENTUSERS.CUSTOMPROPERTY1=NEWUSERDATA.CUSTOMPROPERTY1 AND CURRENTACCOUNTS.ENDPOINTKEY=2)"
]
}

 

I used below columns in my csv file: (for reference)

statuskey,firstname,lastname,systemusername,username,customproperty8,customproperty9,customproperty1

 

If this answers your question, please consider selecting Accept As Solution and hit kudos

Hi @PremMahadikar ,
The query is not working for me.
I even added CURRENTACCOUNTS.STATUS=1 in the query and added the it in the "ACCOUNTS" table columns also.

Thanks & Regards,
SaviyntSavvy

NM
Regular Contributor III
Regular Contributor III

Hi @Saviynt_Savvy , can you share the pre-processor query again

Saviynt_Savvy
Regular Contributor
Regular Contributor

Hi @NM ,
Here is the Pre-Processor:
{
"ADDITIONALTABLES": {
"USERS": "SELECT * FROM USERS",
"ACCOUNTS": "SELECT NAME,ENDPOINTKEY,ACCOUNTKEY,STATUS FROM ACCOUNTS",
"USER_ACCOUNTS": "SELECT USERKEY,ACCOUNTKEY FROM USER_ACCOUNTS"
},
"COMPUTEDCOLUMNS": [
"CUSTOMPROPERTY8",
"CUSTOMPROPERTY9"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET NEWUSERDATA.CUSTOMPROPERTY8 = (SELECT CURRENTACCOUNTS.NAME FROM CURRENTACCOUNTS INNER JOIN CURRENTUSER_ACCOUNTS ON CURRENTACCOUNTS.ACCOUNTKEY=CURRENTUSER_ACCOUNTS.ACCOUNTKEY INNER JOIN CURRENTUSERS ON CURRENTUSER_ACCOUNTS.USERKEY = CURRENTUSERS.USERKEY WHERE CURRENTUSERS.CUSTOMPROPERTY1=NEWUSERDATA.CUSTOMPROPERTY1 AND CURRENTACCOUNTS.ENDPOINTKEY=6 AND CURRENTACCOUNTS.STATUS=1 LIMIT 1)",
"UPDATE NEWUSERDATA SET NEWUSERDATA.CUSTOMPROPERTY9 = (SELECT CURRENTACCOUNTS.NAME FROM CURRENTACCOUNTS INNER JOIN CURRENTUSER_ACCOUNTS ON CURRENTACCOUNTS.ACCOUNTKEY=CURRENTUSER_ACCOUNTS.ACCOUNTKEY INNER JOIN CURRENTUSERS ON CURRENTUSER_ACCOUNTS.USERKEY = CURRENTUSERS.USERKEY WHERE CURRENTUSERS.CUSTOMPROPERTY1=NEWUSERDATA.CUSTOMPROPERTY1 AND CURRENTACCOUNTS.ENDPOINTKEY=6 AND CURRENTACCOUNTS.STATUS=1 LIMIT 1)"
]
}