Saviynt unveils its cutting-edge Intelligence Suite products to revolutionize Identity Security!
Click HERE to see how Saviynt Intelligence is transforming the industry.
Saviynt Copilot Icon

Incremental job for DB Connector for User import

shubhamj596
Regular Contributor
Regular Contributor

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

 

16 REPLIES 16

rushikeshvartak
All-Star
All-Star

Please share logs


Regards,
Rushikesh Vartak
If this helped you move forward, click 'Kudos'. If it solved your query, select 'Accept As Solution'.

shubhamj596
Regular Contributor
Regular Contributor

Hi Rushikesh,

please find the attached and the snipped of the logs:

shubhamj596_0-1664540338248.png

Regards,

Shubham

Change below

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


Regards,
Rushikesh Vartak
If this helped you move forward, click 'Kudos'. If it solved your query, select 'Accept As Solution'.

shubhamj596
Regular Contributor
Regular Contributor

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.

shubhamj596_0-1664775085607.pngshubhamj596_1-1664775121411.png

 

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


Regards,
Rushikesh Vartak
If this helped you move forward, click 'Kudos'. If it solved your query, select 'Accept As Solution'.

shubhamj596
Regular Contributor
Regular Contributor

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

<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,
Rushikesh Vartak
If this helped you move forward, click 'Kudos'. If it solved your query, select 'Accept As Solution'.

shubhamj596
Regular Contributor
Regular Contributor

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:

shubhamj596_0-1664798405345.png

 

<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 ?

 

rushikeshvartak_2-1664802193320.png

 

 


Regards,
Rushikesh Vartak
If this helped you move forward, click 'Kudos'. If it solved your query, select 'Accept As Solution'.

@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 ?

 

 

Regards,
Avinash Chhetri

shubhamj596
Regular Contributor
Regular Contributor

Hi Avinash,

Savupdatedate are getting populated for those users.

Below is the result:

shubhamj596_0-1664859890635.png

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

Can you share error?

if its same its product issue Freshdesk 104152


Regards,
Rushikesh Vartak
If this helped you move forward, click 'Kudos'. If it solved your query, select 'Accept As Solution'.

shubhamj596
Regular Contributor
Regular Contributor

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

 

shubhamj596_1-1665066964578.png

Regards,

Shubham

if you run newQuery in dataanalyzer are you getting output?


Regards,
Rushikesh Vartak
If this helped you move forward, click 'Kudos'. If it solved your query, select 'Accept As Solution'.

shubhamj596
Regular Contributor
Regular Contributor

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.

shubhamj596_0-1665119667845.png

Regards,

Shubham

run select max(updatedate) query from logs


Regards,
Rushikesh Vartak
If this helped you move forward, click 'Kudos'. If it solved your query, select 'Accept As Solution'.