Leveraging Intelligent Recommendations for Operational Transformation. AMS Partners Click HERE | EMEA/APJ Click HERE |
12/08/2023 02:53 PM
All,
Good afternoon. I am trying to integrate a SQL database into Saviynt. The connection is successful; however, within the database, we have different tables. I been trying to find information of how to select the specific table in the database.
Has anyone encountered this situation?
Thanks!
Solved! Go to Solution.
12/10/2023 07:29 PM
Did you specified Database name in URL
jdbc:sqlserver://host:port;DatabaseName=<dabase name>;authenticationScheme=JavaKerberos;integratedSecurity=true;userName=${USERNAME};password=${PASSWORD}
12/10/2023 09:20 PM
@rushikeshvartak Thank you for your replying. Yes. I am able to connect to the database successfully. However, within the database, I need to specify the table where the accounts and the entitlements are located. How can I do this in Saviynt?
12/10/2023 09:21 PM
You can define same in XMLs
12/10/2023 09:40 PM
@rushikeshvartak Is there any example of how to structure this?
I appreciate your response.
12/10/2023 10:18 PM
12/12/2023 06:54 AM - edited 12/12/2023 06:55 AM
Thank you for the reply. however, I was looking at the documentation and I still confused how to call the specific table. for instance, and I want to call a table called employees. where do I specify this information in the section of the xml?
<dataMapping>
<sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="name" >
<![CDATA[select accountname,securitysystem,endpoint,entitlementtype,entitlementvalue,status from accounts]]>
</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="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>
12/12/2023 11:44 AM
You can define query in instead of below query in above post you mentioned
select accountname,securitysystem,endpoint,entitlementtype,entitlementvalue,status from accounts
12/12/2023 12:43 PM
@rushikeshvartak I am getting the following error:
If I have a table called testtable
<dataMapping>
<sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="account_name"><![CDATA[select account_name as 'User', 'Inv-test' as endpoint, 'Inv-test' as securitysystem, '1' as status from [dbo].[testtable];]]></sql-query>
<mapper description="This is the mapping field for Db Import" ifusernotexists="noaction">
<mapfield saviyntproperty="accounts.name" sourceproperty="account_name" type="character" />
<mapfield saviyntproperty="accounts.status" sourceproperty="status" type="character" />
<mapfield saviyntproperty="securitysystems.systemname" sourceproperty="end_point" type="character" />
<mapfield saviyntproperty="endpoints.endpointname" sourceproperty="end_point" type="character" />
</mapper>
</dataMapping>
This is the schema of the table.
any inputs?
12/12/2023 01:11 PM
12/12/2023 01:21 PM
@rushikeshvartak thank you for your time and support. Where should I remove the information. like this?
<dataMapping>
<sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="accountname"><![CDATA[select accountname as 'User', 'Inv-test' as endpoint, 'Inv-test' as securitysystem, '1' as status from [dbo].[testtable];]]></sql-query>
<mapper description="This is the mapping field for Db Import" ifusernotexists="noaction">
<mapfield saviyntproperty="accounts.name" sourceproperty="accountname" type="character" />
<mapfield saviyntproperty="accounts.status" sourceproperty="status" type="character" />
<mapfield saviyntproperty="securitysystems.systemname" sourceproperty="end_point" type="character" />
<mapfield saviyntproperty="endpoints.endpointname" sourceproperty="end_point" type="character" />
</mapper>
</dataMapping>
12/12/2023 01:33 PM
<dataMapping>
<dataMapping>
<sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="accountname"><![CDATA[select accountname as 'User', 'Inv-test' as endpoint, 'Inv-test' as securitysystem, '1' as status from [dbo].[testtable];]]></sql-query>
<mapper description="This is the mapping field for Db Import" ifusernotexists="noaction">
<mapfield saviyntproperty="accounts.name" sourceproperty="User" type="character" />
<mapfield saviyntproperty="accounts.status" sourceproperty="status" type="character" />
<mapfield saviyntproperty="securitysystems.systemname" sourceproperty="securitysystem" type="character" />
<mapfield saviyntproperty="endpoints.endpointname" sourceproperty="endpoint" type="character" />
</mapper>
</dataMapping>
12/12/2023 01:49 PM
@rushikeshvartak Now I have the following error:
Invalid column name 'accountname'
The columns in our SQL Server table has the name of account_name which contains the account of the application.
and it has an underscore. Very strange.
12/12/2023 02:10 PM - edited 12/12/2023 02:11 PM
<dataMapping>
<dataMapping>
<sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="name"><![CDATA[select account_name as 'User', 'Inv-test' as endpoint, 'Inv-test' as securitysystem, '1' as status from [dbo].[testtable];]]></sql-query>
<mapper description="This is the mapping field for Db Import" ifusernotexists="noaction">
<mapfield saviyntproperty="accounts.name" sourceproperty="User" type="character" />
<mapfield saviyntproperty="accounts.status" sourceproperty="status" type="character" />
<mapfield saviyntproperty="securitysystems.systemname" sourceproperty="securitysystem" type="character" />
<mapfield saviyntproperty="endpoints.endpointname" sourceproperty="endpoint" type="character" />
</mapper>
</dataMapping>
12/12/2023 02:34 PM
@rushikeshvartak Thank you for all your time !! it worked!!