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.
