Here is the UserImport JSON for the Sav4Sav DB connection we are using:
<dataMapping>
<sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="employeeid">
<![CDATA[SELECT u.employeeid, max(uh.updatedate) as latesthistoryupdatedate
from users u, usershistory uh
WHERE u.userkey = uh.userkey and uh.updatedate > u.updatedate
group by u.userkey
]]>
</sql-query>
<importsettings>
<zeroDayProvisioning>false</zeroDayProvisioning>
<generateEmail>false</generateEmail>
<userNotInFileAction>NOACTION</userNotInFileAction>
<checkRules>false</checkRules>
<buildUserMap>false</buildUserMap>
<generateSystemUsername>false</generateSystemUsername>
<userOperationsAllowed>UPDATE</userOperationsAllowed>
<userReconcillationField>employeeid</userReconcillationField>
</importsettings>
<mapper description="This is the mapping field for Saviynt Field name">
<mapfield saviyntproperty="employeeid" sourceproperty="employeeid" type="character"/>
<mapfield saviyntproperty="updatedate" sourceproperty="latesthistoryupdatedate" type="date"></mapfield>
</mapper>
</dataMapping>
For clarity, here is the embedded SQL:
SELECT u.employeeid, MAX(uh.updatedate) AS latesthistoryupdatedate
FROM users u, usershistory uh
WHERE u.userkey = uh.userkey
AND uh.updatedate > u.updatedate
GROUP BY u.userkey;
The logic is that when this runs, after the HR data import, we take the latest date in the User History table for each user where that date is later than the user update date. And we udpate those user records.
I still can get my head around why Saviynt would not do this OOTB - it's importing from the source of truth so the update date on the target record should be modified to reflect changes from that source of truth. Seems like it was not designed with incremental updates in mind.
My ideas to fix this are https://ideas.saviynt.com/ideas/EIC-I-5641 (for the stagnant UpdateDate issue) and https://ideas.saviynt.com/ideas/EIC-I-5642. (for the ServiceNow incremental import issue).
Hope that helps.
