Accounts Import from Database - XML config issue

[itinjic]

Hello community,

I should do a full import from the SQL database, on the DB we call the store procedure that returns three parameters per item:

(DB) Username (ex value : Test1,Test2) ---> (Saviynt) Account Name
(DB) Account Status(ex value : ENABLED, DISABLED) ---> (Saviynt) Account Status
(DB) Lock Date (ex value : null,or some date value) ---> (Saviynt) Account Name

Accounts Import XML file content:

<dataMapping>
  <sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="Username">
    <![CDATA[EXEC SPHardCodedValue]]>
  </sql-query>
<mapper description="Import Accounts From DB">
   <mapfield saviyntproperty="accounts.name" sourceproperty="Username" type="character"></mapfield>
   <mapfield saviyntproperty="accounts.CUSTOMPROPERTY10" sourceproperty="Account Status" type="character"></mapfield>
   <mapfield saviyntproperty="accounts.CUSTOMPROPERTY11" sourceproperty="Lock Date" type="character"></mapfield>
    <mapfield saviyntproperty="securitysystems.systemname"  type="character" >SSHardCodedValue</mapfield>
    <mapfield saviyntproperty="endpoints.endpointname"  type="character" >EPHardCodedValue</mapfield>
     <mapfield saviyntproperty="accounts.status"   type="number">1</mapfield>
</mapper>
</dataMapping>

I tried several combinations where I left these values as they are Account Status or Lock Date (or both) but I keep getting an error:

No such property: for class: groovy.sql.GroovyRowResult Possible solutions: class, empty

Do they have any fields that still need to be present during account import and is this mapping good ?

The connector is good and the connection is successful when test it.

[rushikeshvartak]

Column name should not contains space

[itinjic]

I tried that case also but unsuccessfully (get the same error) ,

<dataMapping>
  <sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="Username">
    <![CDATA[EXEC SPHardCodedValue]]>
  </sql-query>
<mapper description="Import Accounts From DB">
   <mapfield saviyntproperty="accounts.name" sourceproperty="Username" type="character"></mapfield>
    <mapfield saviyntproperty="securitysystems.systemname"  type="character" >SSHardCodedValue</mapfield>
    <mapfield saviyntproperty="endpoints.endpointname"  type="character" >EPHardCodedValue</mapfield>
     <mapfield saviyntproperty="accounts.status"   type="number">1</mapfield>
</mapper>
</dataMapping>

[rushikeshvartak]

You can’t hardcore ss / endpoint and status in mapper it should come from stored procedure 

[SumathiSomala]

@itinjic change the below xml as per your requirement

<dataMapping>
<sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="Username">
<![CDATA[select 'EPHardCodedValue' as endpoint, 'SSHardCodedValue' as securitysystem, '1' as status from DB ]]>
</sql-query>
<mapper description="Import Accounts From DB">
<mapfield saviyntproperty="accounts.name" sourceproperty="Username" type="character"></mapfield>
<mapfield saviyntproperty="securitysystems.systemname" sourceproperty="securitysystem" type="character"></mapfield>
<mapfield saviyntproperty="endpoints.endpointname" sourceproperty="endpoint" type="character"></mapfield>
<mapfield saviyntproperty="accounts.status" sourceproperty="status" type="character"></mapfield>
</mapper>
</dataMapping>

Configuring the Integration for Importing Accounts (saviyntcloud.com)

[itinjic]

Too bad that means we can not use a hybrid for example to use a hybrid to retrieve 3 params from the database and three params to be hardcoded in connector.

This would be a good idea to implement in future versions 🤔

[rushikeshvartak]

Raise idea ticket

[Manu269]

restructure the PROC to get the data directly.

Also it wud be easy to maintain.

[Raghu]

@itinjictry like

dataMapping>
<sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="accountname">
<![CDATA[ call [PRQ].[dbo].[TS_spGetUsersList] ]]>
</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="securitysystems.systemname" sourceproperty="systemname" type="character"/>
<mapfield saviyntproperty="endpoints.endpointname" sourceproperty="endpointname" type="character"/>
<mapfield saviyntproperty="entitlementtypes.entitlementname" sourceproperty="entitlementname" type="character"/>
<mapfield saviyntproperty="entitlementvalues.entitlementvalue" sourceproperty="entitlementvalue" type="character"/>
<mapfield saviyntproperty="users.username" sourceproperty="username" type="character"/>
</mapper>
</dataMapping>