Preprocessor error : "Table 'ssminlp.currentusers' doesn't exist"

[Rohit_Mishra]

While attempting to import users through a schema-based approach, we encountered an error stating: "Error while processing data: Table 'ssminlp.currentusers' doesn't exist". Here's an improved version of your message:

We encountered an error while trying to import users using a schema-based method. The error message received was: "Error while processing data: Table 'ssminlp.currentusers' doesn't exist".

We've attempted to resolve this issue using the following JSON configurations:

json
Copy code
#MODIFYUSERDATAJSON={
"ADDITIONAL TABLES": {
"USERS": "SELECT USERNAME, CUSTOMPROPERTY6 FROM USERS",
"CURRENTUSERS": "SELECT USERNAME, user, customproperty6 FROM CURRENTUSERS"
},
"COMPUTEDCOLUMNS": ["EMPLOYEEID", "STATUSKEY"],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET EMPLOYEEID = (SELECT CURRENTUSERS.user FROM CURRENTUSERS WHERE NEWUSERDATA.username = CURRENTUSERS.username)",
"UPDATE NEWUSERDATA SET STATUSKEY = (CASE WHEN CURRENTUSERS.customproperty6 = 'A' THEN '1' ELSE '0' END)"
]
}
Despite implementing these JSON configurations, the error persisted. We're still receiving the message "Table 'ssminlp.currentusers' doesn't exist"

[VisveswaraReddy]

Hi team, could you please help on this 

[This post has been edited by a Moderator. We discourage the @ mention of other forum users or employees unless they have already involved themselves on the forum post.]

[Raghu]

@Rohit_Mishra  your syntax not correct check  users table dont have like column  'user'

use like below

"ADDITIONAL TABLES": {
"USERS": "SELECT USERNAME, CUSTOMPROPERTY6 FROM USERS",
},
"COMPUTEDCOLUMNS": ["EMPLOYEEID", "STATUSKEY"],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET EMPLOYEEID = (SELECT CURRENTUSERS.username FROM CURRENTUSERS WHERE NEWUSERDATA.username = CURRENTUSERS.username)",
"UPDATE NEWUSERDATA SET STATUSKEY = (CASE WHEN CURRENTUSERS.customproperty6 = 'A' THEN '1' ELSE '0' END)"
]
}

Refrence : https://forums.saviynt.com/t5/identity-governance/dataset-usage-in-user-import-preprocessor/m-p/5599...

[PremMahadikar]

Hi @Rohit_Mishra @VisveswaraReddy ,

ssminlp.currentusers -> there is no currentusers table in Saviynt database. In additional tables, as you are using Users, this table can be referred as 'Currentusers' inside preprocessorqueries param. 

Refer this article on inline preprocessor: Integration Options with the Saviynt Database (saviyntcloud.com)

For your use case, please use the below:

{
"ADDITIONAL TABLES": {
"USERS": "SELECT USERNAME, CUSTOMPROPERTY6,EMPLOYEEID,STATUSKEY FROM USERS"
},
"COMPUTEDCOLUMNS": [
"EMPLOYEEID", 
"STATUSKEY"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA, CURRENTUSERS SET NEWUSERDATA.EMPLOYEEID = CURRENTUSERS.EMPLOYEEID WHERE NEWUSERDATA.username = CURRENTUSERS.username",
"UPDATE NEWUSERDATA SET NEWUSERDATA.STATUSKEY = CASE WHEN CURRENTUSERS.customproperty6 = 'A' THEN '1' ELSE '0' END"
]
}

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

[VisveswaraReddy]

Hi Prem,

tried with the Json, same error.

[Raghu]

@VisveswaraReddy  try below and no space b/w "ADDITIONALTABLES":

{
"ADDITIONALTABLES": {
"USERS": "SELECT USERNAME, CUSTOMPROPERTY6,EMPLOYEEID,STATUSKEY FROM USERS"
},
"COMPUTEDCOLUMNS": [
"EMPLOYEEID",
"STATUSKEY"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA, CURRENTUSERS SET NEWUSERDATA.EMPLOYEEID = CURRENTUSERS.EMPLOYEEID WHERE NEWUSERDATA.username = CURRENTUSERS.username",
"UPDATE NEWUSERDATA SET NEWUSERDATA.STATUSKEY = CASE WHEN CURRENTUSERS.customproperty6 = 'A' THEN '1' ELSE '0' END"
]
}

[PremMahadikar]

@VisveswaraReddy @Rohit_Mishra ,

Small change in the query: This is working for me

{
"ADDITIONALTABLES": {
"USERS": "SELECT USERKEY,username,CUSTOMPROPERTY6,EMPLOYEEID,STATUSKEY FROM USERS"
},
"COMPUTEDCOLUMNS": [
"EMPLOYEEID", 
"STATUSKEY",
"CUSTOMPROPERTY6"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA N SET N.EMPLOYEEID = (select U.username from CURRENTUSERS U where N.username=U.username)",
"UPDATE NEWUSERDATA N LEFT JOIN CURRENTUSERS U ON N.username=U.username SET N.STATUSKEY = (CASE WHEN U.CUSTOMPROPERTY6='A' THEN '1' ELSE '0' END)"
]
}

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

[VisveswaraReddy]

Hi Prem,

tried to import users with mentioned JSON, for the first successful run the employee ID field is not populating but after second run only the employee ID field populating.

{"ADDITIONALTABLES":{"USERS":"SELECT USERKEY,username,CUSTOMPROPERTY6,EMPLOYEEID,STATUSKEY FROM USERS"},"COMPUTEDCOLUMNS": ["EMPLOYEEID","STATUSKEY","CUSTOMPROPERTY6"],"PREPROCESSQUERIES":["UPDATE NEWUSERDATA N SET N.EMPLOYEEID = (select U.username from CURRENTUSERS U where N.username=U.username)","UPDATE NEWUSERDATA SET STATUSKEY = (case when customproperty6='A' then '1' else '0' end)"]}

Regards,

Visveswara Reddy

[PremMahadikar]

@VisveswaraReddy,

Please use the exact below code:

{
"ADDITIONALTABLES": {
"USERS": "SELECT USERKEY,username,CUSTOMPROPERTY6,EMPLOYEEID,STATUSKEY FROM USERS"
},
"COMPUTEDCOLUMNS": [
"EMPLOYEEID", 
"STATUSKEY"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA N SET N.EMPLOYEEID = (select U.username from CURRENTUSERS U where N.username=U.username)",
"UPDATE NEWUSERDATA N SET N.STATUSKEY = (select CASE WHEN U.CUSTOMPROPERTY6='A' THEN '1' ELSE '0' END from CURRENTUSERS U where N.username=U.username)"
]
}

Its working as your original request.

1. My input file has 3 columns - 

• 

2. In Data Analyzer, this user details in Saviynt:

• 

3. Import Config:

• 

4. After Import, Data Analyzer verification

• 

5. Let's say for same user, I manually removed employeeid and set Cp6=A after the above testing.

• 

6. Post import using the same code above

• 

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

[rushikeshvartak]

Use json without new line

{"ADDITIONALTABLES":{"USERS":"SELECT USERKEY,username,CUSTOMPROPERTY6,EMPLOYEEID,STATUSKEY FROM USERS"},"COMPUTEDCOLUMNS":["EMPLOYEEID","STATUSKEY"],"PREPROCESSQUERIES":["UPDATE NEWUSERDATA N SET N.EMPLOYEEID = (select U.username from CURRENTUSERS U where N.username=U.username)","UPDATE NEWUSERDATA N SET N.STATUSKEY = (select CASE WHEN U.CUSTOMPROPERTY6='A' THEN '1' ELSE '0' END from CURRENTUSERS U where N.username=U.username)"]}

[PremMahadikar]

 @VisveswaraReddy 

Can you please create a new thread for this request

Hi @Rohit_Mishra ,

If the original question is answered, please click the 'Accept As Solution' button on the reply (or replies) that best answered your question.