and more in a single search tool across platforms. Read the announcement here. |
11/29/2023 01:58 PM
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/29/2023 03:47 PM
Add threshold config json in connection
11/29/2023 04:25 PM
tried already but no luck.
11/29/2023 06:08 PM
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>
11/29/2023 06:16 PM
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
11/29/2023 06:21 PM
None of the accounts are coming then threshold limit is exceeding here "accountThresholdValue": 30000,
change to 1 and try one
11/29/2023 06:27 PM - edited 11/29/2023 06:30 PM
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.
11/29/2023 06:35 PM
When no records come then status never change
11/29/2023 06:36 PM
Its bug right ?
11/29/2023 06:38 PM
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.
11/29/2023 06:47 PM
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.
11/29/2023 06:57 PM
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