and more in a single search tool across platforms. Read the announcement here. |
01/12/2024 04:18 AM
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!
Solved! Go to Solution.
01/15/2024 11:52 PM
Hi @user228,
We are checking on your request and we will keep you posted.
01/17/2024 04:03 AM
Hi @user228,
Please refer the below mentioned forum post :-
Kindly validate and let us know if further assistance is needed.
01/17/2024 04:21 AM - edited 01/17/2024 04:22 AM
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!
01/17/2024 07:11 AM
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
01/17/2024 07:35 AM - edited 01/17/2024 07:41 AM
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!
01/22/2024 08:38 PM
Hi @user228,
Let me check on you request and update you accordingly.
02/22/2024 12:08 AM
Hi @user228,
Please respond to the internal message that we have sent and we can further triage this issue.
02/22/2024 12:52 AM
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!
02/22/2024 01:27 AM
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?
02/22/2024 01:30 AM - edited 02/22/2024 01:31 AM
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'"