Leveraging Intelligent Recommendations for Operational Transformation. AMS Partners click HERE | EMEA/APJ Partners click HERE |
11/22/2023 04:46 PM
Hello Team,
I am getting an error below while running saviynt 4 saviynt database import.
For input string: "[:]" |
<?xml version="1.0"?> <dataMapping> <before-import> </before-import> <sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="name" > <![CDATA[ SELECT DISTINCT name AS name, accountid AS accountid, 'AccessLevel' AS ettypename, accounttype AS entvalue, 'SAP Litmos' AS systemname, 'SAP Litmos' AS endpointname from accounts where status like '1' and accounttype is not null and endpointkey = 824 ]]> </sql-query> <mapper description="This is the mapping field for Saviynt Field name" accountnotinfileaction="suspend" deleteaccountentitlement="false" ifusernotexists="noaction" incrementalcolumn="updatedate" dateformat="timestamp"> <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="ettypename" type="character"/> <mapfield saviyntproperty="entitlementvalues.entitlementvalue" sourceproperty="entvalue" type="character"/> <mapfield saviyntproperty="accounts.accountid" sourceproperty="accountid" type="character"/> </mapper> <after-import description="EMAIL,BATCH,SQL"> </after-import> </dataMapping> |
11/22/2023 05:49 PM
Which import is this ?
it seems account import if yes then Systems is missing
11/22/2023 05:52 PM
Hi @rushikeshvartak ,
Yes It is, should I put system in mapper description? I already put in the mapfield though.
11/22/2023 06:26 PM
Its required in mapper
@Joon wrote:Hi @rushikeshvartak ,
Yes It is, should I put system in mapper description? I already put in the mapfield though.
<dataMapping>
<before-import></before-import>
<sql-query description="SAP Litmos Database Accounts and Account to Entitlement Import" uniquecolumnsascommaseparated="name">
<![CDATA[
SELECT DISTINCT
name AS name,
accountid AS accountid,
'AccessLevel' AS ettypename,
accounttype AS entvalue,
'SAP Litmos' AS systemname,
'SAP Litmos' AS endpointname
from
accounts
where status like '1' and accounttype is not null and endpointkey = 824
]]>
</sql-query>
<mapper description="SAP LitmosDatabase Accounts and Account to Entitlement Import" accountnotinfileaction="suspend" deleteaccountentitlement="true" dateformat="date" incrementalcolumn="updatedate" systems="'SAP Litmos'">
<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="ettypename" type="character"/>
<mapfield saviyntproperty="entitlementvalues.entitlementvalue" sourceproperty="entvalue" type="character"/>
<mapfield saviyntproperty="accounts.accountid" sourceproperty="accountid" type="character"/>
</mapper>
<after-import description="EMAIL,BATCH,SQL"></after-import>
</dataMapping>
11/22/2023 10:29 PM
Thanks @rushikeshvartak ,
Now I can see this error.
Duplicate entry '1558-DQRC9la_3vEQr8oGqZLNTw2-tester@coupang.com' for key 'ENDPOINTKEY' |
11/22/2023 11:16 PM
There is a uniqueness constraint on the accounts table which is a combination of ENDPOINTKEY-ACCOUNTID-ACCOUNTNAME. From the above-mentioned error, it looks like you have duplicate entries in the accounts table for the ENDPOINTKEY 1558 for the account tester@coupang.com.
You may need to clean up some of the account metadata for the duplicate accounts.
11/23/2023 04:43 PM
Are you using account id mapping ?
can you check accountid mentioned in error not populated for 2 accounts if yes then for one them append -old using update query
11/22/2023 11:51 PM
Hello Amit,
the weird thing is it's not even duplicated in the account table of the endpoint...
This is the real account **sang.cho@coupang.com
and it's not duplicated...
Can I get any advice ?
11/23/2023 12:56 AM
Try query like the following:
select endpointkey, name, count(*) from accounts group by endpointkey,name having count(*) > 1
11/23/2023 05:00 PM
11/23/2023 05:08 PM
Endpointkey is 1588
11/23/2023 05:17 PM
I am Sorry for making you confused,
1588 was dev and Now I am working on prod..
11/23/2023 05:27 PM
Your query is wrong
select endpointkey, name, count(*) from accounts where endpointkey=824 group by endpointkey,name having count(*) > 1
12/05/2023 06:57 PM
@Joon Is this resolved ?