User Pre-processor Config JSON : Not able to update User's Customproperty

[Saviynt_Savvy]

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

I have the requirement to bring the user's AD account name to the user's customproperty8.
While using the .CSV upload, I am using User Pre-Processor Config JSON to update the user's customproperty8 with AD account name.
But I don't see any user updated after the .CSV upload and the updated records count=0.

I have checked the pre-processor in Data Analyzer also and able to get the User's account names.

But the same query is not worked in Pre-Processor Config JSON while doing the .CSV upload.
Please suggest any modifications in the User Pre-Processor below.

User Pre-Processor Config JSON:

{

    "ADDITIONALTABLES": {

        "USERS": "SELECT USERKEY,CUSTOMPROPERTY1,CUSTOMPROPERTY8 FROM USERS",

        "ACCOUNTS": "SELECT NAME,ENDPOINTKEY,ACCOUNTKEY FROM ACCOUNTS WHERE ENDPOINTKEY = 6",

        "USER_ACCOUNTS": "SELECT USERKEY,ACCOUNTKEY FROM USER_ACCOUNTS WHERE ACCOUNTKEY IN (SELECT DISTINCT ACCOUNTKEY FROM ACCOUNTS WHERE ENDPOINTKEY = 6)"

    },

    "COMPUTEDCOLUMNS": [

        "CUSTOMPROPERTY8",

"CUSTOMPROPERTY1",

"USERKEY",

"NAME",

"ENDPOINTKEY"

    ],

    "PREPROCESSQUERIES": [

"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.CUSTOMPROPERTY1 = CURRENTUSERS.CUSTOMPROPERTY1 SET NEWUSERDATA.CUSTOMPROPERTY8 = (SELECT CURRENTACCOUNTS.NAME FROM CURRENTACCOUNTS INNER JOIN CURRENTUSER_ACCOUNTS ON CURRENTUSER_ACCOUNTS.ACCOUNTKEY=CURRENTACCOUNTS.ACCOUNTKEY INNER JOIN CURRENTUSERS ON CURRENTUSERS.USERKEY = CURRENT USER_ACCOUNTS.USERKEY WHERE CURRENTACCOUNTS.ENDPOINTKEY=6)"

    ]

}

Thanks & Regards,
SaviyntSavyy

[PremMahadikar · Accepted solution]

Hi @Saviynt_Savvy ,

In preprocessor query, while you are updating CP8, the sub query returns multiple columns. You have missed to use NEWUSERDATA mapping.

I am not sure how your CSV file looks. But follow the below, it works! (I tested this and its working for me)

• Add username in the csv file if it's not present
• Update your code as below

{
    "ADDITIONALTABLES": {
        "USERS": "SELECT USERKEY,USERNAME, CUSTOMPROPERTY1,CUSTOMPROPERTY8 FROM USERS",
        "ACCOUNTS": "SELECT NAME,ENDPOINTKEY,ACCOUNTKEY FROM ACCOUNTS WHERE ENDPOINTKEY = 3",
        "USER_ACCOUNTS": "SELECT USERKEY,ACCOUNTKEY FROM USER_ACCOUNTS WHERE ACCOUNTKEY IN (SELECT DISTINCT ACCOUNTKEY FROM ACCOUNTS WHERE ENDPOINTKEY = 3)"
    },
    "COMPUTEDCOLUMNS": [
        "CUSTOMPROPERTY8"
    ],
    "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.username=NEWUSERDATA.username)"
    ]
}

Highlighting updated code:

• Additional tables:  "USERS": "SELECT USERKEY,USERNAME,
• Preprocessorqueries: WHERE CURRENTUSERS.username=NEWUSERDATA.username

FYI: I have just used three columns for testing

username,statuskey,customproperty8

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

[Saviynt_Savvy]

Hi @PremMahadikar ,

Thank you for the reply.
It worked perfectly fine for me.
But what if I wanted to bring the user account name in another endpoint to the User's customproperty?
I am adding currentaccounts.endpointkey=6 condition in the pre-processor as given below. But I don't see any update in the User customproperty.
Any suggestions please !

In-Line 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 CURRENTACCOUNTS.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 CURRENTACCOUNTS.ENDPOINTKEY=9)"
]
}

Thanks & Regards,
SaviyntSavvy

[NM]

Hi @PremMahadikar ,

I have tried this it doesn't work.

User Pre-Processor Config JSON

{
"ADDITIONALTABLES": {
"USERS": "SELECT USERKEY,USERNAME,CUSTOMPROPERTY1,CUSTOMPROPERTY51,CUSTOMPROPERTY52 FROM USERS",
"ACCOUNTS": "SELECT NAME,ENDPOINTKEY,ACCOUNTKEY,CUSTOMPROPERTY2 FROM ACCOUNTS WHERE ENDPOINTKEY = 532"
},
"COMPUTEDCOLUMNS": [
"CUSTOMPROPERTY51"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET NEWUSERDATA.CUSTOMPROPERTY51 = (SELECT CURRENTACCOUNTS.CUSTOMPROPERTY1 FROM CURRENTUSERS INNER JOIN ON CURRENTUSERS.CUSTOMPROPERTY52=CURRENTACCOUNTS.NAME INNER JOIN NEWUSERDATA ON CURRENTACCOUNTS.NAME=NEWUSERDATA.CUSTOMPROPERTY52 WHERE CURRENTUSERS.username=NEWUSERDATA.username)"
]
}

CSV File columns

username,statuskey,customproperty52,customproperty51

Currently for testing only one record is present which matches the criteria.

Update: Was able to accomplish it.

[PremMahadikar]

@NM ,

The preprocessor mentioned earlier is working for me.

You mean this works right? I see your last update.

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