Saviynt Forums

vambrale · ‎10/31/2022

Hi,

I have been working on the Saviynt Instance for quite a while now. However, I am not able to configure the appropriate JSON for accountimportJSON in the DB connection.

Here is the Query that I wrote for AccountImportJSON:

<sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="name" >
<![CDATA[SELECT Username,FirstName,LastName,EmailAddress FROM securdi.pulse;]]>
</sql-query>
<mapper description="This is the mapping field for Db Import" ifusernotexists="noaction">
<mapfield saviyntproperty="accounts.name" sourceproperty="Username" type="character"></mapfield>
<mapfield saviyntproperty="accounts.customproperty1" sourceproperty="EmailAddress" type="character"></mapfield>
<mapfield saviyntproperty="accounts.customproperty2" sourceproperty="FirstName" type="character"></mapfield>
<mapfield saviyntproperty="accounts.customproperty3" sourceproperty="LastName" type="character"></mapfield>
</mapper>
</dataMapping>

This query is giving out SQL Syntax error ")" at line 1

The connection to the DB is successful

Please help me in configuring the correct JSON.

rushikeshvartak · ‎10/31/2022

Please create Security System & Endpoint as Pulse & Entitlement Type as Role or Update query as per your need.

Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

vambrale · ‎10/31/2022

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1

No signature of method: java.lang.Long.replace() is applicable for argument types: (java.lang.String, java.lang.String) values: [\, \\]

This is the error it is showing, even after running the above query

rushikeshvartak · ‎10/31/2022

Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

jbaskaran · ‎02/10/2023

Hi, I am also facing the similar problem.

Below are the AccountImportJSON in DB Connection details:

Accounts Import (Accounts Import (Complete) from Database (AccountsImportFullJob)) Job fails throwing below error "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1"

Request you to please help me in getting this fixed. Thank you

Regards

Jayashree

Saathvik · ‎02/10/2023

What is the your target database?

Regards,
Saathvik
If this reply answered your question, please Accept As Solution and give Kudos to help others facing similar issue.

jbaskaran · ‎02/10/2023

Oracle DB

Saathvik · ‎02/10/2023

Try this query

select '1' as status,'GYBONID_LOGIN_NAME' as accountName , GYBONID_EMAIL_ADDRESS as EmailAddress, 'GYBONID' as securitysystem, 'GYBONID' as endpoint, 'ROLE' as entitlementtype, null as entitlementvalue, '2' as syskey from GYBONID WHERE rownum < 5

Regards,
Saathvik
If this reply answered your question, please Accept As Solution and give Kudos to help others facing similar issue.

varunambrale · ‎02/10/2023

<sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="name"><![CDATA[select '1' as status,"GYBONID_LOGIN_NAME" as accountName , "GYBONID_EMAIL_ADDRESS" as EmailAddress, "GYBONID" as securitysystem, "GYBONID" as endpoint, "ROLE" as entitlementtype, "null" as entitlementvalue, "2" as syskey from GYBONID WHERE rownum < 5]]></sql-query>

Try using this, if not check the syntax it is in SQL query

jbaskaran · ‎02/10/2023

I tried with the below:

<sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="name"><![CDATA[select '1' as status,"GYBONID_LOGIN_NAME" as accountName , "GYBONID_EMAIL_ADDRESS" as EmailAddress, 'GYBONID' as securitysystem, 'GYBONID' as endpoint, ROLE as entitlementtype, null as entitlementvalue, '2' as syskey from GYBONID WHERE rownum < 5]]></sql-query>

It throws "ORA-00904: "ROLE": invalid identifier"

Saathvik · ‎02/10/2023

Try the one I posted @jbaskaran

Regards,
Saathvik
If this reply answered your question, please Accept As Solution and give Kudos to help others facing similar issue.

jbaskaran · ‎02/10/2023

I tried that as well, it throws "Unknown column 'SYSTEMKEY' in 'field list'

rushikeshvartak · ‎02/10/2023

Remove below mapping

Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

Saathvik · ‎02/10/2023

remove the mapping

<mapfield saviyntproperty="entitlementtypes.systemkey" sourceproperty="syskey" type="character" />

Regards,
Saathvik
If this reply answered your question, please Accept As Solution and give Kudos to help others facing similar issue.

jbaskaran · ‎02/10/2023

Yeah, I tired that, and Job was successful, but Accounts not imported properly.

Saathvik · ‎02/10/2023

Did you get any data when you run this query on target?

Also make sure that below values you passed or right names(not display names)

'GYBONID' as securitysystem, - this values should match with name not display name

'GYBONID' as endpoint, - this values should match with name not display name

'ROLE' as entitlementtype, - this values should match with name not display name

Also I feel its better to open new thread for your issue

Regards,
Saathvik
If this reply answered your question, please Accept As Solution and give Kudos to help others facing similar issue.

jbaskaran · ‎02/10/2023

Thank you so much for all your help, now it is working fine.

I changed few of the attribute values and mapping fixed the issue.

rushikeshvartak · ‎02/10/2023

Properly ? None account imported or mapping is not done ?

what is error in logs

Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

jbaskaran · ‎02/10/2023

Account was Imported but mapping was not done.

I added/changed few of the values in the query and mapping that fixed the issue.

Thank you so much for all your help.

Saviynt Forums

AccountImportJSON for Database Connector