We are delighted to share our new EIC Delivery Methodology for efficiently managing Saviynt Implementations and delivering quick time to value. CLICK HERE.

Oracle DB - Account Import - is not working

IAM_99
Regular Contributor II
Regular Contributor II

Hi All,

We have Oracle DB integrated , while running Account import  job, If there is results it works fine.

But if there is no results returned from the query, Saviynt is not making existing account status as 'Suspended from import service', instead all account status remained unchanged , Connector is not doing any thing. (No issues with SQL query.)

Here is my Account Import XML 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

<dataMapping>
<sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="name" >
<![CDATA[Select 'AM1' as endpoint ,'AM1' as securitysystem,
a1.Employee_NUM as CERTIFIERUSERNAME,
a1.EmailID as CERTIFIEREMAIL,
a1.Identity_displayName as CERTIFIERDISPLAYNAME,
CASE a1.EMP_Status
WHEN 'Active' then '1'
ELSE '2'
END as CERTIFIERSTATUS,
LISTAGG(a1.pendingcert, ',') WITHIN GROUP (
ORDER BY
a1.pendingcert
) as PENDINGCERTIFICATIONS
from
(
select
DISTINCT spcert.name AS pendingcert,
spgrp.percent_complete as percentage_Complete,
spcertifier.certifier as AM_ID,
spiden.DISPLAY_NAME as Identity_displayName,
spiden.EMAIL as EmailID,
spiden.Extended1 as Employee_NUM,
spiden.Extended4 as EMP_Status
from
identityiq.spt_certification spcert
JOIN identityiq.spt_certifiers spcertifier on spcertifier.certification_id = spcert.id
JOIN identityiq.spt_identity spiden on spcertifier.certifier = spiden.name
JOIN identityiq.spt_certification_groups sptcertgroups on spcert.id = sptcertgroups.certification_id
JOIN identityiq.spt_certification_group spgrp on spgrp.id = sptcertgroups.group_id
where
spcert.COMPLETE = '0'
and spiden.Extended4 NOT IN ('Terminated')

) a1
GROUP BY
a1.Employee_NUM,
a1.EmailID,
a1.Identity_displayName,
a1.EMP_Status;]]>
</sql-query>
<mapper description="This is the mapping field for Saviynt Field name" accountnotinfileaction="Suspend" deleteaccountentitlement="true" ifusernotexists="noaction" addOnlyMode="false">
<mapfield saviyntproperty="securitysystems.systemname" sourceproperty="securitysystem" type="character"/>
<mapfield saviyntproperty="endpoints.endpointname" sourceproperty="endpoint" type="character"/>
<mapfield saviyntproperty="accounts.name" sourceproperty="CERTIFIERUSERNAME" type="character"/>
<mapfield saviyntproperty="accounts.AccountID" sourceproperty="CERTIFIERUSERNAME" type="character"/>
<mapfield saviyntproperty="accounts.customproperty1" sourceproperty="CERTIFIEREMAIL" type="character"/>
<mapfield saviyntproperty="accounts.customproperty2" sourceproperty="CERTIFIERUSERNAME" type="character"/>
<mapfield saviyntproperty="accounts.status" sourceproperty="CERTIFIERSTATUS" type="character"/>
<mapfield saviyntproperty="accounts.customproperty3" sourceproperty="CERTIFIERSTATUS" type="character"/>
<mapfield saviyntproperty="accounts.displayname" sourceproperty="CERTIFIERDISPLAYNAME" type="character"/>
<mapfield saviyntproperty="accounts.customproperty31" sourceproperty="PENDINGCERTIFICATIONS" type="character"/>
</mapper>
</dataMapping>

 

 

Any inputs on the above issue ?

 

Note: 

11 REPLIES 11

rushikeshvartak
All-Star
All-Star

Add threshold config json in connection 


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

IAM_99
Regular Contributor II
Regular Contributor II

tried already but no luck.

rushikeshvartak
All-Star
All-Star

Share threshold config 

 

<dataMapping>
<sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="name" >
<![CDATA[Select 'AM1' as endpoint ,'AM1' as securitysystem,
a1.Employee_NUM as CERTIFIERUSERNAME,
a1.EmailID as CERTIFIEREMAIL,
a1.Identity_displayName as CERTIFIERDISPLAYNAME,
CASE a1.EMP_Status
WHEN 'Active' then '1'
ELSE '2'
END as CERTIFIERSTATUS,
LISTAGG(a1.pendingcert, ',') WITHIN GROUP (
ORDER BY
a1.pendingcert
) as PENDINGCERTIFICATIONS
from
(
select
DISTINCT spcert.name AS pendingcert,
spgrp.percent_complete as percentage_Complete,
spcertifier.certifier as AM_ID,
spiden.DISPLAY_NAME as Identity_displayName,
spiden.EMAIL as EmailID,
spiden.Extended1 as Employee_NUM,
spiden.Extended4 as EMP_Status
from
identityiq.spt_certification spcert
JOIN identityiq.spt_certifiers spcertifier on spcertifier.certification_id = spcert.id
JOIN identityiq.spt_identity spiden on spcertifier.certifier = spiden.name
JOIN identityiq.spt_certification_groups sptcertgroups on spcert.id = sptcertgroups.certification_id
JOIN identityiq.spt_certification_group spgrp on spgrp.id = sptcertgroups.group_id
where
spcert.COMPLETE = '0'
and spiden.Extended4 NOT IN ('Terminated')

) a1
GROUP BY
a1.Employee_NUM,
a1.EmailID,
a1.Identity_displayName,
a1.EMP_Status;]]>
</sql-query>

  <mapper description="Database Accounts and Account to Entitlement Import" accountnotinfileaction="suspend" deleteaccountentitlement="true" dateformat="date" incrementalcolumn="IMPORTDATE" systems="'AM1'">
<mapfield saviyntproperty="securitysystems.systemname" sourceproperty="securitysystem" type="character"/>
<mapfield saviyntproperty="endpoints.endpointname" sourceproperty="endpoint" type="character"/>
<mapfield saviyntproperty="accounts.name" sourceproperty="CERTIFIERUSERNAME" type="character"/>
<mapfield saviyntproperty="accounts.AccountID" sourceproperty="CERTIFIERUSERNAME" type="character"/>
<mapfield saviyntproperty="accounts.customproperty1" sourceproperty="CERTIFIEREMAIL" type="character"/>
<mapfield saviyntproperty="accounts.customproperty2" sourceproperty="CERTIFIERUSERNAME" type="character"/>
<mapfield saviyntproperty="accounts.status" sourceproperty="CERTIFIERSTATUS" type="character"/>
<mapfield saviyntproperty="accounts.customproperty3" sourceproperty="CERTIFIERSTATUS" type="character"/>
<mapfield saviyntproperty="accounts.displayname" sourceproperty="CERTIFIERDISPLAYNAME" type="character"/>
<mapfield saviyntproperty="accounts.customproperty31" sourceproperty="PENDINGCERTIFICATIONS" type="character"/>
</mapper>
</dataMapping>

Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

IAM_99
Regular Contributor II
Regular Contributor II

Please find threshold config

~~~~~~~~~~~~~~~~~

 

{
"statusColumn": "customproperty3",
"activeStatus": [
"1"
],
"deleteLinks": true,
"accountThresholdValue": 30000,
"correlateInactiveAccounts": false,
"inactivateAccountsNotInFile": false
}

 

Tried  this accountnotinfileaction="suspend" already.

incrementalcolumn="IMPORTDATE"  -- This is not applicable for us 

None of the accounts are coming then threshold limit is exceeding here "accountThresholdValue": 30000,

 

change to 1 and try one


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

IAM_99
Regular Contributor II
Regular Contributor II

Let me rephrase the issue we are facing .

Before running Account Import Job - suppose we have 40 accounts with 'Active' Status

scenario1 - Account import ran - if the Query results returns 10 records - then out of 40 , 10 become 'active' remaining all 'suspended from import service'

scenario2 - if Query results returns 0 records - then the remaining  10 should become 'suspended from import service' - which is not happening - this is the issue.

 

When no records come then status never change


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

IAM_99
Regular Contributor II
Regular Contributor II

Its bug right ?

Ideally Atleast one account should come otherwise it will mess up data due to issue in target application. There will not be any use case where no records will come.


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

IAM_99
Regular Contributor II
Regular Contributor II

Hi @rushikeshvartak  -do you know any documentation around this "ideally Atleast one account should come ........."

We have a use case - few times - based conditions no user will return from the query.

If data messup is the concern this should have been there for all connectors - if above scenario happens for REST ( no results returns from api call)  it will mark all accounts as Suspended from import service.

 

 

I agree with REST it mark suspended, can you try mention accountThreshold as 0 if it does not work then raise defect using saviynt support ticket


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.