Pre Processor Query

[AshishDas]

Hi,

We have a requirement for pre processor, that whenever the companyname and grade of the user is updated, customproperty10 of the user should become blank, else it should remain as it is.

Companyname and Grade are the attributes that come from our source of truth (Oracle). Is there anyway I can do that via pre processor?

This is the query that we used but it makes cp10 blank everytime.

"UPDATE NEWUSERDATA, CURRENTUSERS SET NEWUSERDATA.CUSTOMPROPERTY10= case when NEWUSERDATA.COMPANYNAME = CURRENTUSERS.COMPANYNAME then 'NA' ELSE 'CURRENTUSERS.CUSTOMPROPERTY10' end"

[vivekmohanty_pm]

@AshishDas Can you share the full JSON?

[AshishDas]

{
"ADDITIONALTABLES": {
"USERS": "SELECT username,employeeType,systemusername,customproperty10 FROM USERS"
},
"COMPUTEDCOLUMNS": [
"username","customproperty60","customproperty25","employeeType","systemUserName","customproperty10","companyname"
],
"PREPROCESSQUERIES": [
"update NEWUSERDATA n left join currentusers c on n.username=c.username set n.employeeType=case when n.employeeType = 'User ID with external mail' then 'ExternalMail' when n.employeeType ='User ID with Internal mail only' then 'InternalMail' when n.employeeType = 'User ID without email' then 'ADOnly' when n.employeeType = 'No User ID' then 'InternalMail' else n.employeeType end",
"update NEWUSERDATA n left join currentusers c on n.username=c.username set n.systemUserName=SUBSTRING(n.systemUserName,1,LOCATE('@',n.systemUserName)-1)",
"update NEWUSERDATA n left join currentusers c on n.username=c.username set n.manager=case when n.manager is null then '987654321' else n.manager end",
"UPDATE NEWUSERDATA, CURRENTUSERS SET NEWUSERDATA.CUSTOMPROPERTY10= case when NEWUSERDATA.COMPANYNAME = CURRENTUSERS.COMPANYNAME then 'NA' ELSE 'CURRENTUSERS.CUSTOMPROPERTY10' end"
]
}

The last one causes the issue

[pmahalle]

Hi @AshishDas ,

You can achieve this using update user config and update rule. Pre-processor query approach is recommended only when there is no way to achieve the use case. Follow below steps for the same.

1. Add below query in UPDATEUSERJSON in Saviynt connection .

{"updateUserQry":["UPDATE USERS SET U.CUSTOMPROPERTY10=null where U.USERKEY=${user.id}"]}

2.  Create update rule with condition (##a.companyname isupdated## and ##a.grade isupdated##) with action Create Update User Task with Saviynt connection where you have added UPDATEUSERJSON in above step.

Let me know if it helps.

[AshishDas]

Hi Paddy,

We need to trigger this via preprocessor, because whenever a company is updated via oracle, an update account task is triggered for AD. CP10 (Org Unit) is populated from Saviynt4Saviynt based on the companyname mapping from dataset.

So when the company is updated via import the cp10 value is still the old one as SAV4SAV hasnt run yet and it immediately triggers Add access for incorrect group based on cp10. Now when SAV4SAV runs, it updates the cp10 and again a new add access task for a new group based on CP10 is triggers. This triggers two different add access tasks for both old and new company

[sudeshjaiswal · Accepted solution]

Hello @AshishDas 

Please try this json

"UPDATE NEWUSERDATA N LEFT JOIN CURRENTUSERS U ON N.USERNAME = U.USERNAME SET N.CUSTOMPROPERTY10 = CASE WHEN (U.COMPANYNAME != N.COMPANYNAME AND U.GRADE != N.GRADE) THEN '' ELSE U.CUSTOMPROPERTY10 END"

[AshishDas]

Hi Sudesh,

We get the following error:

Error in Users Import - Error while processing data: Unknown column 'U.COMPANYNAME' in 'field list'

[dgandhi]

Add company name here

USERS": "SELECT username,employeeType,systemusername,customproperty10,COMPANYNAME FROM USERS"

Also can you check if grade is valid column name for users table (it doesnt seem to be present in users table)

[sudeshjaiswal]

Hello @AshishDas,

Could you attempt the query "SELECT * FROM USERS," I've only indicated it as the companyname and grade. If a different attribute is used in Saviynt, make sure to adjust it accordingly.