Sav4Sav DB USERIMPORT

[jezzanuena]

Hi Everyone! Need some help on USERIMPORT and MODIFYUSERDATAJSON approach. The requirement is whenever the an admin account is created for the user, one of the attributes of the accounts needs to be stored in the User attributes. Right now, I am trying the Sav4Sav DB approach. But I am not entirely sure if how this actually works. The users in Saviynt is being imported from Workday. I created a Sav4Sav connection, but the user/account query (which by the way working in Data Analyzer) in USERIMPORT. 

Spoiler

<?xml version="1.0" encoding="UTF-8"?>
<dataMapping>
<sql-query
description="This query replace the detective technical rules"
uniquecolumnsascommaseparated="employeeid"
>
<![CDATA[
SELECT
u.username AS 'username',
u.statuskey AS 'UserStatus',
u.employeeid AS 'employeeid',
a.name AS 'AccountName',
a.status AS 'AccountStatus',
a.accountid AS 'accountid',
u.customproperty59 as 'AccountOwned',
a.customproperty13 AS 'EmployeeNumber'
FROM
securitysystems ss
INNER JOIN endpoints e ON ss.systemKey = e.securitysystemKey
INNER JOIN accounts a ON e.endpointkey = a.endpointkey
INNER JOIN user_accounts ua ON ua.accountkey = a.accountkey
INNER JOIN users u ON u.userkey = ua.userkey
WHERE
u.employeeid = a.customproperty13
AND a.customproperty13 IS NOT NULL
AND u.orgunitid != '13D'
AND u.customproperty59 IS NULL
AND e.endpointkey = 7
AND a.name like 's%'
AND a.status IN (1, 2, 'Manually Provisioned')
ORDER BY
EmployeeNumber ASC
]]>
</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="EmployeeNumber" type="character"></mapfield>
</mapper>
</dataMapping>

Then I put a JSON query in MODIFYUSERDATAJSON

Spoiler

{
"ADDITIONALTABLES": {
"USERS": "SELECT EmployeeNumber, AccountOwned, username from users u where u.customproperty59 is null",
"ACCOUNTS": "SELECT name, accountid, EmployeeNumber FROM accounts a where customproperty13 is not null and a.endpointkey = 7 and name like 's%'"
},
"COMPUTEDCOLUMNS": [
"customproperty59"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA as NU SET NU.customproperty59 = 'TEST' where NEWUSERDATA.username = 'ebfn123'"
],
"CUSTOMFUNCTIONS": {
"DOPREPROCESS": {
"FULLCLASSNAME": "com.abc123.saviynt.preprocessor.ConnectionUtilityService",
"METHODNAME": "doPreProcess"
}
}
}

My understanding is that the USERIMPORT will import the results and the MODIFYUSERDATA will then do the modification required for a certain property.  When I run the job, the job is successful but nothing is happening.

Here are the logs:

quartzScheduler_Worker-8-c84hs-DEBUG-Conflicting job group found for UserImportJob

quartzScheduler_Worker-8-c84hs-DEBUG-Checking if any of the currently running Jobs belongs to conflicting Job Group of UserImportJob

quartzScheduler_Worker-8-c84hs-DEBUG-validateRunningJobOfDifferentType result : true

quartzScheduler_Worker-8-c84hs-DEBUG-Inside allowUserImportJob

[rushikeshvartak]

If your xml is giving required result you don’t need preprocessing 

[jezzanuena]

Thank you, @rushikeshvartak. Yes, it is xml. You mean I don't need the MODIFYUSERDATAJSON  at all? Then how is the actual update of the user attribute happening? on which part? I've been trying to run the job and nothing is happening to the users in the results. 

[rushikeshvartak]

share logs if below does not works

<?xml version="1.0" encoding="UTF-8"?>
<dataMapping>
	<sql-query
description="This query replace the detective technical rules"
uniquecolumnsascommaseparated="employeeid"
>
		<![CDATA[
SELECT
u.username AS 'username',
u.statuskey AS 'UserStatus',
u.employeeid AS 'employeeid',
a.name AS 'AccountName',
a.status AS 'AccountStatus',
a.accountid AS 'accountid',
u.customproperty59 as 'AccountOwned',
a.customproperty13 AS 'EmployeeNumber'
FROM
securitysystems ss
INNER JOIN endpoints e ON ss.systemKey = e.securitysystemKey
INNER JOIN accounts a ON e.endpointkey = a.endpointkey
INNER JOIN user_accounts ua ON ua.accountkey = a.accountkey
INNER JOIN users u ON u.userkey = ua.userkey
WHERE
u.employeeid = a.customproperty13
AND a.customproperty13 IS NOT NULL
AND u.orgunitid != '13D'
AND u.customproperty59 IS NULL
AND e.endpointkey = 7
AND a.name like 's%'
AND a.status IN (1, 2, 'Manually Provisioned')
ORDER BY
EmployeeNumber ASC
]]>
	</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="EmployeeNumber" type="character"></mapfield>
		<mapfield saviyntproperty="customproperty59" sourceproperty="AccountOwned" type="character"></mapfield>
	</mapper>
</dataMapping>