Custom rule for user status (import from workday)

[ray]

Hello,

I need to place a rule to determine the user status of the imported users from workday. From workday i am getting the status (active or inactive only) in the attribute customproperty12.

But i have to put in place four states as below (prehire, active, inactive, and purged)

SELECT CASE

WHEN hireDate > CURRENT_DATE THEN 'prehire'

WHEN hireDate < CURRENT_DATE AND (endDate IS NULL OR endDate > CURRENT_DATE) THEN 'active'

WHEN endDate IS NOT NULL AND endDate < CURRENT_DATE AND endDate < DATEADD(year, 5, CURRENT_DATE) THEN 'inactive'

WHEN endDate IS NOT NULL AND endDate < DATEADD(year, 5, CURRENT_DATE) THEN 'purged' END AS worker_status FROM your_table_name;

i need to use the MODIFYUSERDATAJSON to put in place this rule? please give me your feedback if you had a similare case

[pmahalle]

Hi @ray ,

You need to use Saviynt for Saviynt application to update the customproperty12 value based on the conditions after user imported into Saviynt from Workday.

[ray]

Hi  @pmahalle ,

i checked documentation, from Admin ==> global configuration ==> preferences ==> check saviynt for saviynt

I will name the connection Saviynt for saviynt.

Do i need to check all the objects ?

Select objects you want to import

 Roles

 Organization

 User Groups

 SAV Roles

and once created i will see how will i update the customproperty12 value based on the conditions after user imported into Saviynt from Workday. 

[pmahalle]

@ray Not required since you need user import to update CP12. But for future purpose in case you want to allow those objects as requestable through ARS, checked them all.

[ray]

Hello @pmahalle , i tried this but i am having an error (the cp 16 stands for hire date and cp 22 for termination date, the cp42 is the field i am testing)

{
"ADDITIONALTABLES": {
"USERS": "SELECT CUSTOMPROPERTY16, CUSTOMPROPERTY22, CUSTOMPROPERTY42 FROM USERS"
},
"COMPUTEDCOLUMNS": [
"CUSTOMPROPERTY42"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET CUSTOMPROPERTY42 = CASE \
WHEN CUSTOMPROPERTY16 > DATE(sysdate()) THEN 'prehire' \
WHEN CUSTOMPROPERTY16 < DATE(sysdate()) AND (CUSTOMPROPERTY22 IS NULL OR CUSTOMPROPERTY22 > DATE(sysdate())) THEN 'active' \
WHEN CUSTOMPROPERTY22 IS NOT NULL AND CUSTOMPROPERTY22 < DATE(sysdate()) AND CUSTOMPROPERTY22 < DATE_ADD(sysdate(), INTERVAL 5 YEAR) THEN 'inactive' \
WHEN CUSTOMPROPERTY22 IS NOT NULL AND CUSTOMPROPERTY22 < DATE_ADD(sysdate(), INTERVAL 5 YEAR) THEN 'purge' \
ELSE NULL \
END"
]
}

The error i am having is 

Error in Users Import - Exception in parsing MODIFYUSERDATAJSON: Error parsing JSON

give me your feedback please,

Thank you in advance

[pmahalle · Accepted solution]

Hi @ray ,

Try below json and check once.

{
"ADDITIONALTABLES": {
"USERS": "SELECT CUSTOMPROPERTY16, CUSTOMPROPERTY22, CUSTOMPROPERTY42 FROM USERS"
},
"COMPUTEDCOLUMNS": [
"CUSTOMPROPERTY42"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET CUSTOMPROPERTY42 = CASE WHEN CUSTOMPROPERTY16 > DATE(sysdate()) THEN 'prehire' WHEN CUSTOMPROPERTY16 < DATE(sysdate()) AND (CUSTOMPROPERTY22 IS NULL OR CUSTOMPROPERTY22 > DATE(sysdate())) THEN 'active' WHEN CUSTOMPROPERTY22 IS NOT NULL AND CUSTOMPROPERTY22 < DATE(sysdate()) AND CUSTOMPROPERTY22 < DATE_ADD(sysdate(), INTERVAL 5 YEAR) THEN 'inactive' WHEN CUSTOMPROPERTY22 IS NOT NULL AND CUSTOMPROPERTY22 < DATE_ADD(sysdate(), INTERVAL 5 YEAR) THEN 'purge' ELSE NULL END"
]
}

[ray]

thank you !! it worked now, it was the \