Incremental job for DB Connector for User import

[shubhamj596]

Hi everyone,

We want to implement incremental job for user import with Saviynt4saviynt connector.

The userimport jsob we are using is:

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

<![CDATA[
select u.savupdatedate as updatedate, u.username, GROUP_CONCAT(r.role_name) as customproperty18 from role_user_account ru, users u, roles r where ru.userkey = u.userkey and ru.rolekey = r.rolekey group by u.username
]]>

</sql-query>
<importsettings>
<zeroDayProvisioning>false</zeroDayProvisioning>
<userNotInFileAction>NOACTION</userNotInFileAction>
<checkRules>true</checkRules>
<buildUserMap>false</buildUserMap>
<generateSystemUsername>false</generateSystemUsername>
<userReconcillationField>username</userReconcillationField>
</importsettings>
<mapper description="This is the mapping field for Saviynt Field name" dateformat="timestamp" incrementalcolumn="updatedate">
<mapfield saviyntproperty="username" sourceproperty="username" type="character"></mapfield>
<mapfield saviyntproperty="customproperty18" sourceproperty="customproperty18" type="character"></mapfield>
<mapfield saviyntproperty="updatedate" sourceproperty="updatedate" type="character"></mapfield>

</mapper>
</dataMapping>

Currently the query returns 13 users with the full import as well as with the incremental.

Please check if anything is missed.

Regards,

Shubham

[rushikeshvartak]

Please share logs

[shubhamj596]

Hi Rushikesh,

please find the attached and the snipped of the logs:

Regards,

Shubham

[rushikeshvartak]

Change below

dateformat="date" And column name should be capital UPDATEDATE

[shubhamj596]

Changed to <mapper description="This is the mapping field for Saviynt Field name" dateformat="timestamp" incrementalcolumn="updatedate">

Still it is triggering for all the users.

[rushikeshvartak]

updatedate should be uppercase
Can you share logs when import job start

fyi _ There is open ticket with product that db incremental job not working if column name is not updatedate

[shubhamj596]

It was ran with the updatedate in uppercase before.
I am sharing the logs again.

And the column name we are using in the sql query is Savupdatedate which we are using updatedate as an alias.

Can you confirm which column should be changed to get it working?

Import JSON used:

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

<![CDATA[
select u.savupdatedate as updatedate, u.username, GROUP_CONCAT(r.role_name) as customproperty18 from role_user_account ru, users u, roles r where ru.userkey = u.userkey and ru.rolekey = r.rolekey group by u.username
]]>

</sql-query>
<importsettings>
<zeroDayProvisioning>false</zeroDayProvisioning>
<userNotInFileAction>NOACTION</userNotInFileAction>
<checkRules>true</checkRules>
<buildUserMap>false</buildUserMap>
<generateSystemUsername>false</generateSystemUsername>
<userReconcillationField>username</userReconcillationField>
</importsettings>
<mapper description="This is the mapping field for Saviynt Field name" dateformat="date" incrementalcolumn="UPDATEDATE">
<mapfield saviyntproperty="username" sourceproperty="username" type="character"></mapfield>
<mapfield saviyntproperty="customproperty18" sourceproperty="customproperty18" type="character"></mapfield>
<mapfield saviyntproperty="UPDATEDATE" sourceproperty="updatedate" type="character"></mapfield>

</mapper>
</dataMapping>

Regards,

Shubham

[rushikeshvartak]

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

<![CDATA[
select u.savupdatedate as UPDATEDATE, u.username, GROUP_CONCAT(r.role_name) as customproperty18 from role_user_account ru, users u, roles r where ru.userkey = u.userkey and ru.rolekey = r.rolekey group by u.username
]]>

</sql-query>
<importsettings>
<zeroDayProvisioning>false</zeroDayProvisioning>
<userNotInFileAction>NOACTION</userNotInFileAction>
<checkRules>true</checkRules>
<buildUserMap>false</buildUserMap>
<generateSystemUsername>false</generateSystemUsername>
<userReconcillationField>username</userReconcillationField>
</importsettings>
<mapper description="This is the mapping field for Saviynt Field name" dateformat="date" incrementalcolumn="UPDATEDATE">
<mapfield saviyntproperty="username" sourceproperty="username" type="character"></mapfield>
<mapfield saviyntproperty="customproperty18" sourceproperty="customproperty18" type="character"></mapfield>
<mapfield saviyntproperty="UPDATEDATE" sourceproperty="UPDATEDATE" type="character"></mapfield>

</mapper>
</dataMapping>

[shubhamj596]

With this also it is triggering for all the users.

Json used:

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

<![CDATA[
select u.savupdatedate as UPDATEDATE, u.username, GROUP_CONCAT(r.role_name) as customproperty18 from role_user_account ru, users u, roles r where ru.userkey = u.userkey and ru.rolekey = r.rolekey group by u.username
]]>

</sql-query>
<importsettings>
<zeroDayProvisioning>false</zeroDayProvisioning>
<userNotInFileAction>NOACTION</userNotInFileAction>
<checkRules>true</checkRules>
<buildUserMap>false</buildUserMap>
<generateSystemUsername>false</generateSystemUsername>
<userReconcillationField>username</userReconcillationField>
</importsettings>
<mapper description="This is the mapping field for Saviynt Field name" dateformat="date" incrementalcolumn="UPDATEDATE">
<mapfield saviyntproperty="username" sourceproperty="username" type="character"></mapfield>
<mapfield saviyntproperty="customproperty18" sourceproperty="customproperty18" type="character"></mapfield>
<mapfield saviyntproperty="UPDATEDATE" sourceproperty="UPDATEDATE" type="character"></mapfield>

</mapper>
</dataMapping>

Logs attached:

[rushikeshvartak]

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

<![CDATA[
select u.savupdatedate as UPDATEDATE, u.username, GROUP_CONCAT(r.role_name) as customproperty18 from role_user_account ru, users u, roles r where ru.userkey = u.userkey and ru.rolekey = r.rolekey group by u.username
]]>

</sql-query>

<importsettings>
<zeroDayProvisioning>false</zeroDayProvisioning>
<userNotInFileAction>NOACTION</userNotInFileAction>
<checkRules>true</checkRules>
<buildUserMap>false</buildUserMap>
<generateSystemUsername>false</generateSystemUsername>
<userReconcillationField>username</userReconcillationField>
</importsettings>
<mapper description="This is the mapping field for Saviynt Field name" dateformat="date" incrementalcolumn="UPDATEDATE">
<mapfield saviyntproperty="username" sourceproperty="username" type="character"></mapfield>
<mapfield saviyntproperty="customproperty18" sourceproperty="customproperty18" type="character"></mapfield>
<mapfield saviyntproperty="UPDATEDATE" sourceproperty="UPDATEDATE" type="date"></mapfield>

</mapper>
</dataMapping>

Issue : FD 104152

Are you getting same thing in logs ?

[avinashchhetri]

@shubhamj596,

Can you check if the savupdatedate is getting populated for the users you want to import ?

Can you run the query configured in your db connector in the Data Analyzer and share the results ?

[shubhamj596]

Hi Avinash,

Savupdatedate are getting populated for those users.

Below is the result:

It is actually updating savupdatedate for all the users which are returned from the query.

@rushikeshvartak i am seeing any error in my logs which you have shown in your logs snip.

Regards,

Shubham

[rushikeshvartak]

Can you share error?

if its same its product issue Freshdesk 104152

[shubhamj596]

There's no error we are getting.

Its just it is checking the incremental column and then running the import for all the users.

It also says : updateDateTemp -- null

Regards,

Shubham

[rushikeshvartak]

if you run newQuery in dataanalyzer are you getting output?

[shubhamj596]

Yes, the query is working fine.

As we ran the import last time, savupdatedate has already been changed for all the users.

But it is returning 14 entries in total.

Regards,

Shubham

[rushikeshvartak]

run select max(updatedate) query from logs