Incremental User import from DB connection not updating identities.

[ChrisBellobuono]

We are trying to perform an incremental user update from a database ( Users Import (Incremental) from Database (UserImportIncrementalJob) ) but the job's updated user count is always zero and our test users are not being updated.

Since we do not have write access to the client's DB or their HR system, we have modified the import SQL to always return the current timestamp for the 'updatedate' column to speed up initial testing.

We have already tried a number of things:

• Used both LOCALTIMESTAMP (no TZ) and CURRENT_TIMESTAMP (with TZ) for returning the current date.
• Forcing the returned date to UTC ( SYS_EXTRACT_UTC(CURRENT_TIMESTAMP) ).
• Changing the returned column and all the references to it in USERIMPORT to updatedate (all lowercase) as the documentation/forums indicated due to a known defect.
• Changing the 'format' in the mapper header and updatedate mapfield to every combination of timestamp and date.

The attached ZIP contains the logs from the most recent run, a trimmed down version of the USERIMPORT setting, a screen shot of the user's last update date in Saviynt, how the user's record is returned from the target, and a screen shot of the results of the full import job using the same connection configuration.

One thing to note is the user was originally created via a legacy LDAP connection (15) and we are trying to update them from a new DB connection (21).  Does that matter?  Are updates limited to the original data source?

I am probably missing something obvious, so any help would be appreciated.

[pmahalle]

Hi @ChrisBellobuono ,

Did you added incremental recon configurations in User Import XML? Can you share the user import xml, you are using. Also, share the field which will hold the update date in the target DB.

[ChrisBellobuono]

If you are referring to this section of USERIMPORT:

<importsettings>
<zeroDayProvisioning>false</zeroDayProvisioning>
<generateEmail>false</generateEmail>
<userNotInFileAction>NOACTION</userNotInFileAction>
<checkRules>false</checkRules>
<buildUserMap>false</buildUserMap>
<generateSystemUsername>false</generateSystemUsername>
<userOperationsAllowed>CREATE OR UPDATE</userOperationsAllowed>
<userReconcillationField>USERNAME</userReconcillationField>
</importsettings>

Then yes, it is set.

[pmahalle]

Hi @ChrisBellobuono ,

Can you follow below steps and try.

1. Make sure, you fetching column/field (UPDATED_DATE ) from target in select statement present in user import xml.

2. dateformat and incrementalcolumn should be present as an attribute under mapper tag as below. incrementalcolumn value should be the target column holding updatedate:

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

3. Map the target field holding recent update date in updatedate column of user like below:

<mapfield saviyntproperty="updatedate" sourceproperty="UPDATED_DATE" type="date"></mapfield>

[ChrisBellobuono]

I had it that way once before except mine had the defaultrole="" in mapper.  Unfortunately, I get the same result without it.

[SB]

The incremental import woks on the last update date of the user. In case you are setting the update date as current date by default in your query, it may not be able to identify the user. Is there a field in the target DB that does store the actual update value?

[ChrisBellobuono]

Yes, there is, but as I stated we do not have write access to the client DB so to avoid constantly going to them to make changes each time we test, we forced the SQL to fake the date.  That said, we have tried this for that field as well:

to_timestamp(trim(UPDATED_DATE), 'YYYY-MM-DD HH:MI:SS') as updatedate,

Where UPDATED_DATE is the DB column name containing when each user's record was last updated.

[SB]

can you hardcode the update_date field value to a date in the past and then try your incremental import job. 

[ChrisBellobuono]

I tried both of these:

'2023-07-28 16:18:43' AS UPDATED_DATE
to_timestamp('2023-07-28 16:18:43', 'YYYY-MM-DD HH:MI:SS') as UPDATED_DATE

Neither worked.  Do the timestamps need to be in a particular format for this to work?

[rushikeshvartak]

Just give date and try

[ChrisBellobuono]

Per support's request, I am posting an updated summary of where we are.

Our current configuration:

• DB "last modified" column: UPDATED_DATE (timestamp)
• Mapper: <mapper dateformat="timestamp" incrementalcolumn="UPDATED_DATE">
• Map Field: <mapfield saviyntproperty="updatedate" sourceproperty="UPDATED_DATE" type="date"/>

When we run the incremental update job (Users Import (Incremental) from Database (UserImportIncrementalJob)) the statistics always show that all users (almost 7k) have been updated.  This happens regardless of whether or not we include the above Map Field or if it is mapped to updatedate or some random Custom Property.

We wrote a report to list all the users that have been recently updated and it always lists all users after the incremental job is run with SAVUPDATEDATE showing the current timestamp.

select u.username, u.Firstname, u.Lastname, u.CREATEDATE , u.SAVUPDATEDATE from users u where u.CUSTOMPROPERTY20 = 'XXXXXX' AND DATEDIFF(NOW() ,SAVUPDATEDATE) < 3

When we pick any of those users at random and look at their update history nothing shows up for the current date:

We are not sure what the problem is.

Also, we were on version 23.4 when we first reported the problem and are now on 23.8.

[rushikeshvartak]

Can you share full xml

[ChrisBellobuono]

The XML is attached as requested.

FYI, this problem is not limited to this connection.  We are experiencing the same problem with a newly created connection to a PeopleSoft DB.