We are delighted to share our new EIC Delivery Methodology for efficiently managing Saviynt Implementations and delivering quick time to value. CLICK HERE.

User import from DB connection not updating identities.

ChrisBellobuono
New Contributor III
New Contributor III

We have existing identities which were loaded/managed via a legacy LDAP connection and want to begin managing them via a new DB connection.  The connection test to the DB is working and USERIMPORT and MODIFYUSERDATAJSON are the only configuration properties that have been set.  When the full import job runs it says it was successful, but none of the identity attributes are updated.  The query in USERIMPORT has been verified as returning the expected results using a standard DB viewing tool.  The setup of MODIFYUSERDATAJSON was copied from the legacy connection since the same processing is needed here as well.  I cannot find anything in the logs indicating why updates are not being applied or if a problem was encountered.

USERIMPORT:

<dataMapping>
<before-import>
</before-import>

<sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="USER_GUID">
<![CDATA[
select 
EMPLOYEEID,
'VJ' || lpad(EMPLOYEEID, 6, '0') as USER_GUID,
(case
when trim(LASTNAME2) is null
then LASTNAME
else
LASTNAME || '-' || LASTNAME2
end) AS LASTNAME,
USERNAME as FIRSTNAME,
DISPLAYNAME,
to_date(trim(STARTDATE), 'YYYY-MM-DD HH:MI:SS') as STARTDATE,
STARTDATE as STARTDATE_ORIG,
to_date(trim(ENDDATE), 'YYYY-MM-DD HH:MI:SS') as ENDDATE,
ENDDATE as ENDDATE_ORIG,
DEPARTMENTNUMBER,
DEPARTMENTNAME,
(case
when lower(CUSTOMPROPERTY4) = 'inactive'
then 0
else
1
end) as STATUSKEY,
LOCATION,
JOBCODE,
TITLE,
(case
when MANAGER = 0
then null
else
'VJ' || lpad(MANAGER, 6, '0')
end) as MANAGER_GUID,
MANAGER as MANAGER_ORIG,
(case
when SUPERVISOR = 0
then null
else
'VJ' || lpad(SUPERVISOR, 6, '0')
end) as SUPERVISOR_GUID,
SUPERVISOR as SUPERVISOR_ORIG,
to_timestamp(trim(CREATED_DATE), 'YYYY-MM-DD HH:MI:SS') as CREATED_DATE,
CREATED_DATE as CREATED_DATE_ORIG,
to_timestamp(trim(UPDATED_DATE), 'YYYY-MM-DD HH:MI:SS') as UPDATED_DATE,
UPDATED_DATE as UPDATED_DATE_ORIG,
initcap(
USERNAME ||
(case
when trim(LASTNAME2) is null
then ''
else
' ' || substr(trim(LASTNAME2), 1, 1)
end) ||
' ' || LASTNAME
) as CN,
'Associate' as EMPLOYEETYPE,
'ABC' as ACMECOMPANYABBRV,
'DEF' as ACMECOMPANYCODE,
'Acme International, LLC' as ACMECOMPANYNAME,
'HR' || lpad(EMPLOYEEID, 6, '0') as ACMEHRUNIQUEEMPLOYEENUMBER,
'Acme HR System' as ACMEIDENTITYDATASOURCE,
'ENG' as PREFERREDLANGUAGE,
(case
when lower(CUSTOMPROPERTY4) = 'retired'
then '123'
else
'456'
end) as ACMEAUTHENTICATIONSOURCEEAI
from
ACME.USER_HR
where
lower(CUSTOMPROPERTY4) <> 'inactive'
and EMPLOYEEID = 4
]]>
</sql-query>

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

<mapper description="This is the mapping field for Saviynt Field name" defaultrole="" dateformat="timestamp">
<mapfield saviyntproperty="EMPLOYEEID" sourceproperty="EMPLOYEEID" type="number"/>
<mapfield saviyntproperty="USERNAME" sourceproperty="USER_GUID" type="character"/>
<mapfield saviyntproperty="SYSTEMUSERNAME" sourceproperty="USER_GUID" type="character"/>
<mapfield saviyntproperty="LASTNAME" sourceproperty="LASTNAME" type="character"/>
<mapfield saviyntproperty="FIRSTNAME" sourceproperty="FIRSTNAME" type="character"/>
<mapfield saviyntproperty="DISPLAYNAME" sourceproperty="DISPLAYNAME" type="character"/>
<mapfield saviyntproperty="STARTDATE" sourceproperty="STARTDATE" type="date"/>
<mapfield saviyntproperty="ENDDATE" sourceproperty="ENDDATE" type="date"/>
<mapfield saviyntproperty="DEPARTMENTNUMBER" sourceproperty="DEPARTMENTNUMBER" type="character"/>
<mapfield saviyntproperty="DEPARTMENTNAME" sourceproperty="DEPARTMENTNAME" type="character"/>
<mapfield saviyntproperty="STATUSKEY" sourceproperty="STATUSKEY" type="number"/>
<mapfield saviyntproperty="LOCATIONDESC" sourceproperty="LOCATION" type="character"/>
<mapfield saviyntproperty="JOBCODE" sourceproperty="JOBCODE" type="character"/>
<mapfield saviyntproperty="TITLE" sourceproperty="TITLE" type="character"/>
<mapfield saviyntproperty="CUSTOMPROPERTY16" sourceproperty="MANAGER_GUID" type="character"/>
<mapfield saviyntproperty="CUSTOMPROPERTY17" sourceproperty="SUPERVISOR_GUID" type="character"/>
<mapfield saviyntproperty="CUSTOMPROPERTY1" sourceproperty="CN" type="character"/>
<mapfield saviyntproperty="EMPLOYEETYPE" sourceproperty="EMPLOYEETYPE" type="character"/>
<mapfield saviyntproperty="CUSTOMPROPERTY8" sourceproperty="ACMECOMPANYABBRV" type="character"/>
<mapfield saviyntproperty="CUSTOMPROPERTY7" sourceproperty="ACMECOMPANYCODE" type="character"/>
<mapfield saviyntproperty="COMPANYNAME" sourceproperty="ACMECOMPANYNAME" type="character"/>
<mapfield saviyntproperty="CUSTOMPROPERTY5" sourceproperty="ACMEHRUNIQUEEMPLOYEENUMBER" type="character"/>
<mapfield saviyntproperty="CUSTOMPROPERTY20" sourceproperty="ACMEIDENTITYDATASOURCE" type="character"/>
<mapfield saviyntproperty="CUSTOMPROPERTY3" sourceproperty="PREFERREDLANGUAGE" type="character"/>
<mapfield saviyntproperty="CUSTOMPROPERTY21" sourceproperty="ACMEAUTHENTICATIONSOURCEEAI" type="character"/>
<mapfield saviyntproperty="UPDATEDATE" sourceproperty="UPDATED_DATE" type="date"></mapfield>
</mapper>

<after-import description="EMAIL,BATCH,SQL">
</after-import>
</dataMapping>

MODIFYUSERDATAJSON:

{
"ADDITIONALTABLES": {
"USERS": "select USERKEY, USERNAME, CUSTOMER, CUSTOMPROPERTY57 from USERS",
"CUSTOMER": "select CUSTOMERKEY, CUSTOMERNAME, CUSTOMPROPERTY1, CUSTOMPROPERTY2, CUSTOMPROPERTY3, STATUS from CUSTOMER"
},

"COMPUTEDCOLUMNS": [
"CUSTOMER",
"MANAGER",
"SECONDARYMANAGER"
],

"PREPROCESSQUERIES": [
"update NEWUSERDATA set CUSTOMER = ( select CURRENTCUSTOMER.CUSTOMERNAME from CURRENTCUSTOMER where CURRENTCUSTOMER.CUSTOMPROPERTY1 like CONCAT('%|', NEWUSERDATA.EMPLOYEETYPE, '|%') and ( NEWUSERDATA.STATUSKEY = 0 or CURRENTCUSTOMER.CUSTOMPROPERTY2 like CONCAT('%|', NEWUSERDATA.CUSTOMPROPERTY20, '|%') ) and CURRENTCUSTOMER.CUSTOMPROPERTY3 = NEWUSERDATA.STATUSKEY and CURRENTCUSTOMER.STATUS = 1 )",
"update NEWUSERDATA set MANAGER = ( select CURRENTUSERS.USERNAME from CURRENTUSERS where NEWUSERDATA.CUSTOMPROPERTY17 = CURRENTUSERS.USERNAME )",
"update NEWUSERDATA set SECONDARYMANAGER = ( select CURRENTUSERS.USERNAME from CURRENTUSERS where NEWUSERDATA.CUSTOMPROPERTY16 = CURRENTUSERS.USERNAME )",
]
}
 
If anyone can offer an idea as to what is going on or what we are doing wrong, it would be greatly appreciated.
 
Thanks.
5 REPLIES 5

SB
Saviynt Employee
Saviynt Employee

Can you please share the below information 

1. Output of the above query for a single user.

2. Can you run the import job for just 1 user (used in above query) and share the import logs.

3. The xml looks fine and I tested the import with the attached xml I used on my env and it did work for me.


Regards,
Sahil

ChrisBellobuono
New Contributor III
New Contributor III

Here are the files.  I had to mask some of the client's data.

SB
Saviynt Employee
Saviynt Employee

Can you also share a screenshot of the trigger. Click on edit trigger button and then capture the screenshot.

In the logs I do not even see the application trying to execute the query


Regards,
Sahil

ChrisBellobuono
New Contributor III
New Contributor III

We are all set, it is working now.  I had the wrong User Import job type assigned to the trigger.  Switching to Users Import (Complete) from Database (UserImportFullJob) fixed it.

That sounds great, I was also suspecting the same cause.


Regards,
Sahil