and more in a single search tool across platforms. Read the announcement here. |
10/31/2022 02:25 AM
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:
<dataMapping>
<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.
Solved! Go to Solution.
10/31/2022 04:54 AM
<dataMapping>
<sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="name" >
<![CDATA[select Username as accountname,FirstName,LastName,EmailAddress,'Pulse' as securitysystem,'Pulse' as endpoint, 'Role' as entitlementtype,null as entitlementvalue, 1 as status from securdi.pulse]]>
</sql-query>
<mapper description="This is the mapping field for Saviynt Field name" accountnotinfileaction="Suspend" deleteaccountentitlement="true" ifusernotexists="noaction">
<mapfield saviyntproperty="accounts.name" sourceproperty="accountname" type="character"/>
<mapfield saviyntproperty="accounts.customproperty1" sourceproperty="FirstName" type="character"/>
<mapfield saviyntproperty="accounts.customproperty2" sourceproperty="LastName" type="character"/>
<mapfield saviyntproperty="accounts.customproperty3" sourceproperty="EmailAddress" type="character"/>
<mapfield saviyntproperty="securitysystems.systemname" sourceproperty="securitysystem" type="character"/>
<mapfield saviyntproperty="endpoints.endpointname" sourceproperty="endpoint" type="character"/>
<mapfield saviyntproperty="entitlementtypes.entitlementname" sourceproperty="entitlementtype" type="character"/>
<mapfield saviyntproperty="entitlementvalues.entitlementvalue" sourceproperty="entitlementvalue" type="character"/>
<mapfield saviyntproperty="accounts.status" sourceproperty="status" type="character"/>
</mapper>
</dataMapping>
Please create Security System & Endpoint as Pulse & Entitlement Type as Role or Update query as per your need.
10/31/2022 05:15 AM - edited 10/31/2022 05:57 AM
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
10/31/2022 07:37 AM
<dataMapping>
<sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="name" >
<![CDATA[select Username as accountname,FirstName,LastName,EmailAddress,'Pulse' as securitysystem,'Pulse' as endpoint, 'Role' as entitlementtype,null as entitlementvalue, '1' as status from securdi.pulse]]>
</sql-query>
<mapper description="This is the mapping field for Saviynt Field name" accountnotinfileaction="Suspend" deleteaccountentitlement="true" ifusernotexists="noaction">
<mapfield saviyntproperty="accounts.name" sourceproperty="accountname" type="character"/>
<mapfield saviyntproperty="accounts.customproperty1" sourceproperty="FirstName" type="character"/>
<mapfield saviyntproperty="accounts.customproperty2" sourceproperty="LastName" type="character"/>
<mapfield saviyntproperty="accounts.customproperty3" sourceproperty="EmailAddress" type="character"/>
<mapfield saviyntproperty="securitysystems.systemname" sourceproperty="securitysystem" type="character"/>
<mapfield saviyntproperty="endpoints.endpointname" sourceproperty="endpoint" type="character"/>
<mapfield saviyntproperty="entitlementtypes.entitlementname" sourceproperty="entitlementtype" type="character"/>
<mapfield saviyntproperty="entitlementvalues.entitlementvalue" sourceproperty="entitlementvalue" type="character"/>
<mapfield saviyntproperty="accounts.status" sourceproperty="status" type="character"/>
</mapper>
</dataMapping>
02/10/2023 06:53 AM
Hi, I am also facing the similar problem.
Below are the AccountImportJSON in DB Connection details:
<dataMapping>
<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>
<mapper description="This is the mapping field for Saviynt Field name" accountnotinfileaction="noaction" deleteaccountentitlement="false" ifusernotexists="noaction" systems="'GYBONID'">
<mapfield saviyntproperty="accounts.name" sourceproperty="accountName" type="character" />
<mapfield saviyntproperty="accounts.customproperty3" sourceproperty="EmailAddress" type="character" />
<mapfield saviyntproperty="securitysystems.systemname" sourceproperty="securitysystem" type="character" />
<mapfield saviyntproperty="endpoints.endpointname" sourceproperty="endpoint" type="character" />
<mapfield saviyntproperty="entitlementtypes.entitlementname" sourceproperty="entitlementtype" type="character" />
<mapfield saviyntproperty="entitlementvalues.entitlementvalue" sourceproperty="entitlementvalue" type="character" />
<mapfield saviyntproperty="entitlementtypes.systemkey" sourceproperty="syskey" type="character" />
<mapfield saviyntproperty="accounts.customproperty30" sourceproperty="status" type="character" />
</mapper>
</dataMapping>
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
02/10/2023 07:07 AM
What is the your target database?
02/10/2023 07:13 AM
Oracle DB
02/10/2023 07:57 AM
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
02/10/2023 07:20 AM
<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
02/10/2023 07:59 AM
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"
02/10/2023 08:33 AM
Try the one I posted @jbaskaran
02/10/2023 08:37 AM
I tried that as well, it throws "Unknown column 'SYSTEMKEY' in 'field list'
<dataMapping>
<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>
<mapper description="This is the mapping field for Saviynt Field name" accountnotinfileaction="noaction" deleteaccountentitlement="false" ifusernotexists="noaction" systems="'GYBONID'">
<mapfield saviyntproperty="accounts.name" sourceproperty="accountName" type="character" />
<mapfield saviyntproperty="accounts.customproperty3" sourceproperty="EmailAddress" type="character" />
<mapfield saviyntproperty="securitysystems.systemname" sourceproperty="securitysystem" type="character" />
<mapfield saviyntproperty="endpoints.endpointname" sourceproperty="endpoint" type="character" />
<mapfield saviyntproperty="entitlementtypes.entitlementname" sourceproperty="entitlementtype" type="character" />
<mapfield saviyntproperty="entitlementvalues.entitlementvalue" sourceproperty="entitlementvalue" type="character" />
<mapfield saviyntproperty="entitlementtypes.systemkey" sourceproperty="syskey" type="character" />
<mapfield saviyntproperty="accounts.customproperty30" sourceproperty="status" type="character" />
</mapper>
</dataMapping>
02/10/2023 08:41 AM
Remove below mapping
<mapfield saviyntproperty="entitlementtypes.systemkey" sourceproperty="syskey" type="character" />
02/10/2023 08:52 AM
remove the mapping
<mapfield saviyntproperty="entitlementtypes.systemkey" sourceproperty="syskey" type="character" />
02/10/2023 09:03 AM
Yeah, I tired that, and Job was successful, but Accounts not imported properly.
02/10/2023 09:07 AM - edited 02/10/2023 09:11 AM
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
02/10/2023 07:50 PM
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.
02/10/2023 04:25 PM - edited 02/10/2023 04:27 PM
Properly ? None account imported or mapping is not done ?
what is error in logs
02/10/2023 07:52 PM
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.