Owner on Terminate

[ruchika]

How can we set owner on terminate to all the employees of the organization.

It looks like a Saviynt internal attribute and not coming in feed from Workday.

Can someone please let me know the options available to set this for every user.

[rushikeshvartak]

You can defined same in pre-processor logic as manager will be owneronterminate

[ruchika]

Would a sample logic look like this?

"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET OWNERONTERMINATE = user.manager"]

and how can we update the logic to skip VP and SVPs, if the user is VP(identifying attribute by job_function) then the owner on terminate should be saviynt admin

[itinjic]

To update the logic to skip VPs and SVPs and set the owner on terminate as Saviynt admin for VPs, you can use the following sample logic:

"PREPROCESSQUERIES": [ "UPDATE NEWUSERDATA n SET n.OWNERONTERMINATE = (CASE WHEN n.JOB_FUNCTION = 'VP' THEN 'saviyntadmin' WHEN n.JOB_FUNCTION = 'SVP' THEN 'saviyntadmin' ELSE n.MANAGER END)" ] In this logic, the "JOB_FUNCTION"

attribute is used to identify if the user is a VP or SVP. If the user has the job function as VP, the owner on terminate is set as "saviyntadmin". For any other users, the owner on terminate is set as their manager.

[rushikeshvartak]

{
"ADDITIONALTABLES": {
"USERS": "SELECT USERNAME, FIRSTNAME, LASTNAME, STATUSKEY, MANAGER, JOBCODEDESC, DEPARTMENTNAME, SECONDARYMANAGER, OWNER FROM USERS"
},
"COMPUTEDCOLUMNS": [
"OWNERONTERMINATE"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET OWNERONTERMINATE = OWNER"
]
}

Refer https://forums.saviynt.com/t5/identity-governance/owner-on-terminate-mapping-file-upload-or-import-f... 

[ruchika]

Error in Users Import - Error while processing data: Unknown column 'nu.OWNERONTERMINATE' in 'field list'

getting this error while running the query: "UPDATE newuserdata nu SET nu.owneronterminate = (CASE WHEN nu.employeeid = '276' THEN 'saviyntadmin' ELSE END)",

[rushikeshvartak]

UPDATE newuserdata nu
SET nu.OWNERONTERMINATE = (CASE WHEN nu.employeeid = '276' THEN 'saviyntadmin' ELSE nu.OWNERONTERMINATE END);

[ruchika]

with both OWNERONTERMINATE and owneronterminate we are getting the same error. We even removed the case and used the below query, still same issue.

"UPDATE newuserdata nu SET nu.OWNERONTERMINATE = 'saviyntadmin' WHERE nu.employeeid = '274906'",

[rushikeshvartak]

Please share full json

[Saathvik]

@ruchika : Did you include OWNERONTERMINATE in select statement under ADDITIONALTABLES section?

{
"ADDITIONALTABLES": {
"USERS": "SELECT USERNAME, FIRSTNAME, LASTNAME, STATUSKEY, MANAGER, JOBCODEDESC, DEPARTMENTNAME, SECONDARYMANAGER, OWNERONTERMINATE, OWNER FROM USERS"
}

[ruchika]

yes, tried that too but got the same error

[Saathvik]

@ruchika : Like Rushi asked pls share updated full MODIFYUSERDATAJSON

[ruchika]

{
"ADDITIONALTABLES": {
"USERS": "SELECT username, employeeid, systemusername, customproperty11, customproperty28, lastname, statuskey, employeeType, displayname, firstname, locationnumber, locationdesc, state, street, title, middlename, owner, comments, preferedFirstName, customproperty2, customproperty5, customproperty13, jobcode, customproperty14, customproperty4, job_function, customproperty1, customproperty15, customproperty6, customproperty7, customproperty9, customproperty8, customproperty10, customproperty12, customproperty16, customproperty17, customproperty18, customproperty19, customproperty21, customproperty22, customproperty3, costcenter, orgunitid, departmentNumber, departmentname, customproperty23, phonenumber, secondaryPhone, customproperty26, customproperty25, startdate, customproperty24, enddate, regioncode, entity, companyname, city, country, region, owneronterminate LOCALAUTHENABLED FROM USERS"
},
"TABLEINDEXES": {
"currentusers": [
"employeeid",
"preferedfirstname",
"firstname",
"lastname",
"systemusername",
"customproperty11",
"displayname",
"statuskey",
"startdate",
"username"
],
"newuserdata": [
"preferedfirstname",
"firstname",
"lastname",
"systemusername",
"customproperty11",
"displayname",
"statuskey",
"employeeid"
]
},
"COMPUTEDCOLUMNS": [
"USERNAME",
"CUSTOMPROPERTY11",
"DISPLAYNAME",
"SYSTEMUSERNAME"
],
"PREPROCESSQUERIES": [
"UPDATE newuserdata nu SET nu.owner = (SELECT username FROM currentusers cu WHERE cu.employeeid = nu.owner)",
"UPDATE newuserdata nu SET nu.statuskey = '1' WHERE nu.customproperty5 IN ('A', 'L', 'P', 'S', 'F')",
"UPDATE newuserdata nu SET nu.statuskey = '0' WHERE nu.customproperty5 NOT IN ('A', 'L', 'P', 'S', 'F')",
"UPDATE newuserdata nu SET nu.title = SUBSTRING(nu.title, 1, 64) WHERE LENGTH(nu.title) > 64",

"UPDATE newuserdata nu SET nu.owneronterminate = (CASE WHEN nu.employeeid = '276' THEN 'saviyntadmin' END)",
"CUSTOMFUNCTION###DOPREPROCESS",
"INSERT INTO newuserdata(username, employeeid, systemusername, customproperty28, lastname, statuskey, employeeType, displayname, firstname, locationnumber, locationdesc, state, street, title, middlename, owner, comments, preferedFirstName, customproperty2, customproperty5, customproperty13, jobcode, customproperty14, customproperty4, job_function, customproperty1, customproperty15, customproperty6, customproperty7, customproperty9, customproperty8, customproperty10, customproperty12, customproperty16, customproperty17, customproperty18, customproperty19, customproperty21, customproperty22, customproperty3, costcenter, orgunitid, departmentNumber, departmentname, customproperty23, phonenumber, secondaryPhone, customproperty26, customproperty25, startdate, customproperty24, enddate, regioncode, entity, companyname, city, country, region) SELECT cu.username, cu.employeeid, cu.systemusername, cu.customproperty28, cu.lastname, 0 as statuskey, cu.employeeType, cu.displayname, cu.firstname, cu.locationnumber, cu.locationdesc, cu.state, cu.street, cu.title, cu.middlename, cu.owner, cu.comments, cu.preferedFirstName, cu.customproperty2, cu.customproperty5, cu.customproperty13, cu.jobcode, cu.customproperty14, cu.customproperty4, cu.job_function, cu.customproperty1, cu.customproperty15, cu.customproperty6, cu.customproperty7, cu.customproperty9, cu.customproperty8, cu.customproperty10, cu.customproperty12, cu.customproperty16, cu.customproperty17, cu.customproperty18, cu.customproperty19, cu.customproperty21, cu.customproperty22, cu.customproperty3, cu.costcenter, cu.orgunitid, cu.departmentNumber, cu.departmentname, cu.customproperty23, cu.phonenumber, cu.secondaryPhone, cu.customproperty26, cu.customproperty25, cu.startdate, cu.customproperty24, sysdate() as enddate, cu.regioncode, cu.entity, cu.companyname, cu.city, cu.country, cu.region FROM currentusers cu WHERE cu.statuskey = 1 AND cu.employeeid IS NOT NULL AND LENGTH(TRIM(cu.employeeid))>0 AND cu.customproperty28 IS NOT NULL AND LENGTH(TRIM(cu.customproperty28))>0 AND cu.startdate IS NOT NULL AND (DATE(cu.startdate) < SUBDATE(DATE(sysdate()),INTERVAL 1 DAY)) AND cu.employeeid IN ('0000033','0000058','0000107','0000279','0000280','0000281') AND cu.LOCALAUTHENABLED != '1' AND cu.employeeid NOT IN (SELECT nu.employeeid FROM newuserdata nu) AND (SELECT count(*) FROM CURRENTUSERS cu1 WHERE cu1.statuskey = 1 AND cu1.employeeid IS NOT NULL AND LENGTH(TRIM(cu1.employeeid))>0 AND cu1.customproperty28 IS NOT NULL AND LENGTH(TRIM(cu1.customproperty28))>0 AND cu1.startdate IS NOT NULL AND (DATE(cu1.startdate) < SUBDATE(DATE(sysdate()),INTERVAL 1 DAY)) AND cu1.employeeid IN ('0000033','0000058','0000107','0000279','0000280','0000281') AND cu1.LOCALAUTHENABLED != '1' AND cu1.employeeid NOT IN (SELECT nu1.employeeid FROM newuserdata nu1)) < 10"
],
"CUSTOMFUNCTIONS": {
"DOPREPROCESS": {
"FULLCLASSNAME": "com.mckesson.saviynt.preprocessor.ConnectionUtilityService",
"METHODNAME": "doPreProcess"
}
}
}

[rushikeshvartak]

"UPDATE newuserdata nu SET nu.owneronterminate = 'saviyntadmin' where nu.employeeid = '276'"

[ruchika]

As mentioned earlier this is also giving error as owneronterminate attribute is showing up as unknown column. And we would need case statement for our original query, currently testing with one user only hence using it like you mentioned using where but that gives error too

[Saathvik]

@ruchika : Please include OWNERONTERMINATE column in computed columns section and see if that works

[ruchika]

modified the data as per below, but the owneronterminate attribute is not getting set for user. The jobs are running successfully while we run them. Is there any change expected in user account on workday level to trigger the condition?

{
"ADDITIONALTABLES": {
"USERS": "SELECT username, employeeid, systemusername, customproperty11, customproperty28, lastname, statuskey, employeeType, displayname, firstname, locationnumber, locationdesc, state, street, title, middlename, owner, comments, preferedFirstName, customproperty2, customproperty5, customproperty13, jobcode, customproperty14, customproperty4, job_function, customproperty1, customproperty15, customproperty6, customproperty7, customproperty9, customproperty8, customproperty10, customproperty12, customproperty16, customproperty17, customproperty18, customproperty19, customproperty21, customproperty22, customproperty3, costcenter, orgunitid, departmentNumber, departmentname, customproperty23, phonenumber, secondaryPhone, customproperty26, customproperty25, startdate, customproperty24, enddate, regioncode, entity, companyname, city, country, region, owneronterminate, LOCALAUTHENABLED FROM USERS"
},
"TABLEINDEXES": {
"currentusers": [
"employeeid",
"preferedfirstname",
"firstname",
"lastname",
"systemusername",
"customproperty11",
"displayname",
"statuskey",
"startdate",
"username"
],
"newuserdata": [
"preferedfirstname",
"firstname",
"lastname",
"systemusername",
"customproperty11",
"displayname",
"statuskey",
"employeeid"
]
},
"COMPUTEDCOLUMNS": [
"USERNAME",
"CUSTOMPROPERTY11",
"DISPLAYNAME",
"SYSTEMUSERNAME",
"OWNERONTERMINATE"
],
"PREPROCESSQUERIES": [
"UPDATE newuserdata nu SET nu.owner = (SELECT username FROM currentusers cu WHERE cu.employeeid = nu.owner)",
"UPDATE newuserdata nu SET nu.statuskey = '1' WHERE nu.customproperty5 IN ('A', 'L', 'P', 'S', 'F')",
"UPDATE newuserdata nu SET nu.statuskey = '0' WHERE nu.customproperty5 NOT IN ('A', 'L', 'P', 'S', 'F')",
"UPDATE newuserdata nu SET nu.title = SUBSTRING(nu.title, 1, 64) WHERE LENGTH(nu.title) > 64",
"UPDATE newuserdata nu SET nu.owneronterminate = '1' where nu.employeeid = '274906'",
"CUSTOMFUNCTION###DOPREPROCESS",
"INSERT INTO newuserdata(username, employeeid, systemusername, customproperty28, lastname, statuskey, employeeType, displayname, firstname, locationnumber, locationdesc, state, street, title, middlename, owner, comments, preferedFirstName, customproperty2, customproperty5, customproperty13, jobcode, customproperty14, customproperty4, job_function, customproperty1, customproperty15, customproperty6, customproperty7, customproperty9, customproperty8, customproperty10, customproperty12, customproperty16, customproperty17, customproperty18, customproperty19, customproperty21, customproperty22, customproperty3, costcenter, orgunitid, departmentNumber, departmentname, customproperty23, phonenumber, secondaryPhone, customproperty26, customproperty25, startdate, customproperty24, enddate, regioncode, entity, companyname, city, country, region) SELECT cu.username, cu.employeeid, cu.systemusername, cu.customproperty28, cu.lastname, 0 as statuskey, cu.employeeType, cu.displayname, cu.firstname, cu.locationnumber, cu.locationdesc, cu.state, cu.street, cu.title, cu.middlename, cu.owner, cu.comments, cu.preferedFirstName, cu.customproperty2, cu.customproperty5, cu.customproperty13, cu.jobcode, cu.customproperty14, cu.customproperty4, cu.job_function, cu.customproperty1, cu.customproperty15, cu.customproperty6, cu.customproperty7, cu.customproperty9, cu.customproperty8, cu.customproperty10, cu.customproperty12, cu.customproperty16, cu.customproperty17, cu.customproperty18, cu.customproperty19, cu.customproperty21, cu.customproperty22, cu.customproperty3, cu.costcenter, cu.orgunitid, cu.departmentNumber, cu.departmentname, cu.customproperty23, cu.phonenumber, cu.secondaryPhone, cu.customproperty26, cu.customproperty25, cu.startdate, cu.customproperty24, sysdate() as enddate, cu.regioncode, cu.entity, cu.companyname, cu.city, cu.country, cu.region FROM currentusers cu WHERE cu.statuskey = 1 AND cu.employeeid IS NOT NULL AND LENGTH(TRIM(cu.employeeid))>0 AND cu.customproperty28 IS NOT NULL AND LENGTH(TRIM(cu.customproperty28))>0 AND cu.startdate IS NOT NULL AND (DATE(cu.startdate) < SUBDATE(DATE(sysdate()),INTERVAL 1 DAY)) AND cu.employeeid IN ('0000033','0000058','0000107','0000279','0000280','0000281') AND cu.LOCALAUTHENABLED != '1' AND cu.employeeid NOT IN (SELECT nu.employeeid FROM newuserdata nu) AND (SELECT count(*) FROM CURRENTUSERS cu1 WHERE cu1.statuskey = 1 AND cu1.employeeid IS NOT NULL AND LENGTH(TRIM(cu1.employeeid))>0 AND cu1.customproperty28 IS NOT NULL AND LENGTH(TRIM(cu1.customproperty28))>0 AND cu1.startdate IS NOT NULL AND (DATE(cu1.startdate) < SUBDATE(DATE(sysdate()),INTERVAL 1 DAY)) AND cu1.employeeid IN ('0000033','0000058','0000107','0000279','0000280','0000281') AND cu1.LOCALAUTHENABLED != '1' AND cu1.employeeid NOT IN (SELECT nu1.employeeid FROM newuserdata nu1)) < 10"
],
"CUSTOMFUNCTIONS": {
"DOPREPROCESS": {
"FULLCLASSNAME": "com.mckesson.saviynt.preprocessor.ConnectionUtilityService",
"METHODNAME": "doPreProcess"
}
}
}