Error in Users Import - Error while processing data: Table 'ssminlp.accounts' doesn't exist

[user228]

Hello,

While trying to import users from the REST App and using MODIFYUSERDATAJSON, I want to join the accounts table like:

"ADDITIONALTABLES": {
"USERS": "SELECT username, customproperty31, manager FROM USERS",
"ACCOUNTS": "SELECT name, customproperty14, customproperty10 FROM ACCOUNTS"
},
"COMPUTEDCOLUMNS": ["customproperty14", "customproperty10", "customproperty31", "username","manager"],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA NU JOIN accounts A ON NU.username = A.name SET NU.manager = A.customproperty14",
"UPDATE NEWUSERDATA NU JOIN accounts A ON NU.username = A.name SET NU.customproperty31 = A.customproperty10"
]
}

but after the job is done it gives an error: "Error while processing data: Table 'ssminlp.accounts' doesn't exist". What can it be? or it is not possible and it only can be done via Sav4Sav Connector?

Thank you!

[DixshantValecha]

Hi @user228,

We are checking on your request and we will keep you posted.

[DixshantValecha]

Hi @user228,

Please refer the below mentioned forum post :-

https://forums.saviynt.com/t5/identity-governance/getting-error-in-inline-preprocessor-query/m-p/537...

Kindly validate and let us know if further assistance is needed.

[user228]

thank you @DixshantValecha but it is not exactly what I needed, because I am not sure if I should write it in modifyjson or not, maybe I need to do it in analytics like:

select 'USERCONTEXT', u.customproperty31, u.username, case when ed.Endpointname = 'AD test' THEN u.customproperty31 END as updated_customproperty31 FROM users u JOIN user_accounts UA ON u.userkey = UA.userkey JOIN accounts A ON UA.accountkey = A.accountkey JOIN endpoints ed ON A.endpointkey = ed.endpointkey WHERE ed.Endpoint name IN ('AD test')

but then I don't know if I can do it...

Can you pls advise, where I can change it.

or maybe via jobs via SAV4SAV connector.

Thank you!

[DixshantValecha]

Hi @user228,

Thanks for the clarity but could you  please let us know what value is being saved in customproperty14,Secondly the error message “Table ‘ssminlp.accounts’ doesn’t exist” suggests that the table accounts you’re trying to join in your PREPROCESSQUERIES does not exist in the database. Here are a few things you could check:

Table Name: Ensure that the table exists and the name is spelled correctly.
Database Connection: Check if your application is connected to the correct database where the accounts table resides

Permissions: Verify that the user account used for the database connection has the necessary permissions to access the accounts table.

So,please let us know what else are you getting in the logs.

Additional you check on the SAV4SAV connector capability by refering below mentioned post:-

Sav4Sav REST User Import and ModifyUserdataJSON (I... - Saviynt Forums - 31804

Why we use sav4sav connector - Saviynt Forums - 53010

[user228]

thank you very much for your full reply!

In one reply it states:

1) Updating user customproperty to trigger user update rule. Ex: pushing AD extension attribute based on 2 attribute values reconciled on account table from AD

This is what we need, to take the value from AD accounts.customproperty and put it in the users.customproperty

The thing is when I run this query in the Data Analyzer:

select 'USERCONTEXT', u.customproperty31, u.username, case when ed.Endpointname = 'AD' THEN u.customproperty31=a.customproperty10 END as updated_customproperty31 FROM users u JOIN user_accounts UA ON u.userkey = UA.userkey JOIN accounts A ON UA.accountkey = A.accountkey JOIN endpoints ed ON A.endpointkey = ed.endpointkey WHERE ed.Endpoint name IN ('AD')

I received some results of course without "case when" (modification), but when I put it in the REST Connector MODIFYUSERDATAJSON from the user import or create analytics it shows 0 result.

So, I assume I must write it in the SAV4SAV connector in MODIFYUSERDATAJSON?

Or? But I do not understand why should I use Analytics in this case?

Thank you!

[DixshantValecha]

Hi @user228,

Let me check on you request and update you accordingly.

[DixshantValecha]

Hi @user228,

Please respond to the internal message that we have sent and we can further triage this issue.

[naveenss · Accepted solution]

Hi @user228  can you please try the below inline preprocessor JSON?

"ADDITIONALTABLES": {
    "USERS": "SELECT username, customproperty31, manager FROM USERS",
    "ACCOUNTS": "SELECT name, customproperty14, customproperty10 FROM ACCOUNTS"
},
"COMPUTEDCOLUMNS": ["customproperty14", "customproperty10", "customproperty31", "username", "manager"],
"PREPROCESSQUERIES": [
    "UPDATE NEWUSERDATA NU JOIN CURRENTACCOUNTS A ON NU.username = A.name SET NU.manager = A.customproperty14",
    "UPDATE NEWUSERDATA NU JOIN CURRENTACCOUNTS A ON NU.username = A.name SET NU.customproperty31 = A.customproperty10"
]
}

 Let me know if this helps!

[user228]

thanks ! It worked we don't receive this error anymore, we were just thinking about how to point out that we need this transformation only in AD accounts.. like ed.Endpoint name IN ('AD'), do you think we can include it?

[naveenss]

Yes, this is doable. join your accounts table with the endpoints table and include your endpoint name. Request you to try the below query. Let me know. 

"ACCOUNTS": "SELECT A.name, A.customproperty14, A.customproperty10 FROM ACCOUNTS A, ENDPOINTS ED where A.ENDPOINTKEY=ED.ENDPOINTKEY and ED.ENDPOINTNAME='AD'"