Click HERE to see how Saviynt Intelligence is transforming the industry. |
09/30/2022 02:27 AM
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
09/30/2022 02:28 AM
Please share logs
09/30/2022 05:20 AM
09/30/2022 05:39 AM
Change below
dateformat="date" And column name should be capital UPDATEDATE
10/02/2022 10:32 PM
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.
10/03/2022 01:29 AM
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
10/03/2022 02:59 AM
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
10/03/2022 04:05 AM
<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>
10/03/2022 05:00 AM
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:
10/03/2022 06:06 AM
<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 ?
10/03/2022 04:22 PM
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 ?
10/03/2022 10:07 PM
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
10/04/2022 04:23 AM
Can you share error?
if its same its product issue Freshdesk 104152
10/06/2022 07:36 AM
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
10/06/2022 07:37 AM
if you run newQuery in dataanalyzer are you getting output?
10/06/2022 10:14 PM
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
10/06/2022 10:33 PM
run select max(updatedate) query from logs