Updated user attribute name store in customproperty using preprocessor.

[ankitalande]

Hii,

I have one use case, 

In mover scenario some attributes are change to user profile, I want to store the attribute name like jobcodedesc, department name to users customproperty31 using preprocessor.

following some of sytax I try, but fails to achieve.

{
"ADDITIONALTABLES": {
"USERS": "SELECT * FROM USERS"
},
"COMPUTEDCOLUMNS": [
"CUSTOMPROPERTY31"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET CUSTOMPROPERTY31 = (', ',
CASE WHEN NEWUSERDATA.jobcodedesc != CURRENTUSERS.jobcodedesc THEN 'job profile change' ELSE NULL END
);"
]
}

{
"ADDITIONALTABLES": {
"USERS": "SELECT * FROM USERS"
},
"COMPUTEDCOLUMNS": [
"CUSTOMPROPERTY31"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET CUSTOMPROPERTY31 =
CASE WHEN NEWUSERDATA.jobcodedesc != CURRENTUSERS.jobcodedesc THEN 'job profile change' ELSE NULL END WHERE NEWUSERDATA.jobcodedesc != CURRENTUSERS.jobcodedesc;"
]
}

{
"ADDITIONALTABLES": {
"USERS": "SELECT * FROM USERS"
},
"COMPUTEDCOLUMNS": [
"CUSTOMPROPERTY31"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET CUSTOMPROPERTY31 = CONCAT_WS(', ',
CASE WHEN NEWUSERDATA.LOCATION != CURRENTUSERS.LOCATION THEN 'Location change' ELSE NULL END,
CASE WHEN NEWUSERDATA.MANAGER != CURRENTUSERS.MANAGER THEN 'Manager change' ELSE NULL END,
CASE WHEN NEWUSERDATA.JOBCODEDESC != CURRENTUSERS.JOBCODEDESC THEN 'Job profile change' ELSE NULL END,
CASE WHEN NEWUSERDATA.CUSTOMPROPERTY15 != CURRENTUSERS.CUSTOMPROPERTY15 THEN 'Functional group change' ELSE NULL END
);"
]
}

can you please suggest, how we can achieve this.

[Raghu]

@ankitalande  try below:

{
"ADDITIONALTABLES": {
"USERS": "SELECT JOBCODEDESC FROM USERS"
},
"COMPUTEDCOLUMNS": [
"CUSTOMPROPERTY31","JOBCODEDESC"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET CUSTOMPROPERTY31 = (', ',
CASE WHEN NEWUSERDATA.JOBCODEDESC != CURRENTUSERS.JOBCODEDESC THEN 'job profile change' ELSE NULL END
)"
]
}

{
"ADDITIONALTABLES": {
"USERS": "SELECT JOBCODEDESC FROM USERS"
},
"COMPUTEDCOLUMNS": [
"CUSTOMPROPERTY31","JOBCODEDESC"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET CUSTOMPROPERTY31 =
CASE WHEN NEWUSERDATA.jobcodedesc != CURRENTUSERS.JOBCODEDESC THEN 'job profile change' ELSE NULL END WHERE NEWUSERDATA.JOBCODEDESC != CURRENTUSERS.JOBCODEDESC"
]
}

{
"ADDITIONALTABLES": {
"USERS": "SELECT LOCATION,MANAGER,JOBCODEDESC,CUSTOMPROPERTY15 FROM USERS"
},
"COMPUTEDCOLUMNS": [
"CUSTOMPROPERTY31"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET CUSTOMPROPERTY31 = CONCAT_WS(', ',
CASE WHEN NEWUSERDATA.LOCATION != CURRENTUSERS.LOCATION THEN 'Location change' ELSE NULL END,
CASE WHEN NEWUSERDATA.MANAGER != CURRENTUSERS.MANAGER THEN 'Manager change' ELSE NULL END,
CASE WHEN NEWUSERDATA.JOBCODEDESC != CURRENTUSERS.JOBCODEDESC THEN 'Job profile change' ELSE NULL END,
CASE WHEN NEWUSERDATA.CUSTOMPROPERTY15 != CURRENTUSERS.CUSTOMPROPERTY15 THEN 'Functional group change' ELSE NULL END
)"
]
}

[ankitalande]

still not working, for testing purpose I upload the user through csv and check, User not updated and review history showing 0 as well. 

[Raghu]

try :

{
"ADDITIONALTABLES": {
"USERS": "SELECT JOBCODEDESC FROM USERS"
},
"COMPUTEDCOLUMNS": [
"CUSTOMPROPERTY31","JOBCODEDESC"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET CUSTOMPROPERTY31 = CASE WHEN (NEWUSERDATA.JOBCODEDESC is not null and NEWUSERDATA.JOBCODEDESC != CURRENTUSERS.JOBCODEDESC) THEN 'job profile change' ELSE NULL END"
]
}

{
"ADDITIONALTABLES": {
"USERS": "SELECT JOBCODEDESC FROM USERS"
},
"COMPUTEDCOLUMNS": [
"CUSTOMPROPERTY31","JOBCODEDESC"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET CUSTOMPROPERTY31 =
CASE WHEN (NEWUSERDATA.JOBCODEDESC is not null and NEWUSERDATA.JOBCODEDESC != CURRENTUSERS.JOBCODEDESC) THEN 'job profile change' ELSE NULL END WHERE NEWUSERDATA.JOBCODEDESC != CURRENTUSERS.JOBCODEDESC"
]
}

{
"ADDITIONALTABLES": {
"USERS": "SELECT LOCATION,MANAGER,JOBCODEDESC,CUSTOMPROPERTY15 FROM USERS"
},
"COMPUTEDCOLUMNS": [
"CUSTOMPROPERTY31"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET CUSTOMPROPERTY31 = CONCAT_WS(', ',
CASE WHEN (NEWUSERDATA.LOCATION is not null and NEWUSERDATA.LOCATION != CURRENTUSERS.LOCATION) THEN 'Location change' ELSE NULL END,
CASE WHEN (NEWUSERDATA.MANAGER is not null and NEWUSERDATA.MANAGER != CURRENTUSERS.MANAGER) THEN 'Manager change' ELSE NULL END,
CASE WHEN (NEWUSERDATA.JOBCODEDESC is not null and NEWUSERDATA.JOBCODEDESC != CURRENTUSERS.JOBCODEDESC) THEN 'Job profile change' ELSE NULL END,
CASE WHEN (NEWUSERDATA.CUSTOMPROPERTY15 is not null and NEWUSERDATA.CUSTOMPROPERTY15 != CURRENTUSERS.CUSTOMPROPERTY15) THEN 'Functional group change' ELSE NULL END
)"
]
}

[rushikeshvartak]

Are you updating preprocessor JSON while uploading user?

[ankitalande]

Yes, I updated preprocessor json while uploading user.

[ankitalande]

Is there any other way to achieve this or alternative preprocessor json.

[rushikeshvartak]

Saviynt 4 saviynt

[ankitalande]

How we can define attribute change in saviynt 4 saviynt?

[rushikeshvartak]

based on user update date and comparing value in actual attribute and Custom property