MySQL application ( Using application integration) : Import Users not working

[PrashantG]

I am trying to use create mysql Application using application integration

For now, intention is to start with import Accounts from the DB

setup only 3 features

changed as per my DB table the mapping xml setup

and one batch job

1.Though batchjob is running successfully, could not see any accounts added in DB endpoint. There are 2 entries in the table.

2.do not see any errors as well in the logs

any missing setup to be done here...appreciate any help 

[sudeshjaiswal]

Hello @PrashantG,

Can you please provide the XML file, you are using for importing the accounts.

Thanks.

[PrashantG]

<dataMapping>
<sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="name">
<![CDATA[ select name,group,entitlementvalue,status from accountfullrecon ]]>
</sql-query>
<mapper description="This is the mapping field for Saviynt Field name" accountnotinfileaction="Suspend" deleteaccountentitlement="true" ifusernotexists="noaction">
<mapfield saviyntproperty="accounts.name" sourceproperty="name" type="character"/>
<mapfield saviyntproperty="entitlementtypes.entitlementname" sourceproperty="group" type="character"/>
<mapfield saviyntproperty="entitlementvalues.entitlementvalue" sourceproperty="entitlementvalue" type="character"/>
<mapfield saviyntproperty="accounts.status" sourceproperty="status" type="character"/>
</mapper>
</dataMapping>

[sudeshjaiswal]

Hello @PrashantG,

Thanks for sharing the XML, while you run the import , what error do you see in the logs ?

Regards,

[PrashantG]

no errors.....the job status shows success. 

[sudeshjaiswal]

Hello @PrashantG 

Did you try integrating it via Classic integration.

like from going identity repository—> Connection.

Please try in that way and let me know if it works there.

Thanks

[PrashantG]

thanks !

I was able to import only "accounts" using classic integration, though

- still not working with placeholders of endpoint & securitysystem (which gets autogenerated in the value)

- also need to look at entitlement type and values are not importing

<dataMapping>
<sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="name" >
<![CDATA[
select name, `group`,entitlementvalue,status,"SS_value" as securitysystem, "SS_endpoint" as endpoint from mytable
]]>
</sql-query>
<mapper description="This is the mapping field for Saviynt Field name" accountnotinfileaction="Suspend" deleteaccountentitlement="true" ifusernotexists="noaction">
<mapfield saviyntproperty="accounts.name" sourceproperty="name" type="character"/>
<mapfield saviyntproperty="entitlementtypes.entitlementname" sourceproperty="group" type="character"/>
<mapfield saviyntproperty="entitlementvalues.entitlementvalue" sourceproperty="entitlementvalue" 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>

as per this documentation

[sudeshjaiswal]

Hello @PrashantG,

Could you please provide more details about the issue you are facing so that I can better assist you?

As I understand it, your accounts are being imported but the entitlements and entitlement types are not being imported?

Have the security system and endpoint been created? Did you create them manually, or were they auto-generated from the design module?

Thanks.

[PrashantG]

Hi Sudesh,

though i started with application integration, went back to classic integration, as suggested by you.

now in classic integration

- able to import only accounts but as per xml example, entitlementtype & value are not importing. (below xml also has imports statements for entitlementtype & value)

Hoping i am clear now

also another point is incremental account access, getting syntax error...can you also validate this, if this is correct xml. I have setup incremental job for this

<dataMapping>

<sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="name" >

<![CDATA[

select name, `group`,entitlementvalue,status,'SS' as securitysystem, 'DB_App' as endpoint from Table

 ]]>

</sql-query>

<incrementalcondition>

    <![CDATA['2023-01-01']]>

</incrementalcondition>

<mapper description="This is the mapping field for Saviynt Field name" accountnotinfileaction="noaction" deleteaccountentitlement="true" ifusernotexists="noaction" dateformat="date" incrementalcolumn="updatedate" systems="SS">

<mapfield saviyntproperty="accounts.name" sourceproperty="name" type="character"/>

<mapfield saviyntproperty="entitlementtypes.entitlementname" sourceproperty="group" type="character"/>

<mapfield saviyntproperty="entitlementvalues.entitlementvalue" sourceproperty="entitlementvalue" 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"/>

<mapfield saviyntproperty="accounts.customproperty25" sourceproperty="updatedate" type="date"/>

</mapper>

</dataMapping>

getting below error

2023-10-30T11:10:07+05:30-ecm-worker----30-Oct-2023 05:40:07.205 WARNING [quartzScheduler_Worker-5] groovy.sql.Sql$AbstractQueryCommand.execute Failed to execute: SELECT SYSTEMKEY FROM SECURITYSYSTEMS WHERE SYSTEMNAME IN (SS) because: Unknown column 'SS' in 'where clause'

2023-10-30T11:10:07+05:30-ecm-worker-saviynt.AccountsImportService-quartzScheduler_Worker-5-ERROR-Exception in AccountsImportService

2023-10-30T11:10:07+05:30-ecm-worker----java.sql.SQLSyntaxErrorException: Unknown column 'SS' in 'where clause' at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) at com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1200) at com.saviynt.AccountsImportService.importData(AccountsImportService.groovy:194) at AccountsImportIncrementalJob.execute(AccountsImportIncrementalJob.groovy:60) at org.quartz.core.JobRunShell.run(JobRunShell.java:199) at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:546)