ModifyUserData JSON clearing out email for a very small number of users

[BrandonLucas_BF]

Hi -

I have a really strange issue on user import with user pre-processing that feels like either a bug or logic error and I can't quite figure it out.

Under typical conditions, when we import new users we allow Saviynt to generate the email address for the new hire. However, for a few users from a specific department value, we override their email value with the value from secondary email.

I wrote the following modify user data json, which works most of the time:

{

    "ADDITIONALTABLES": {

    "USERS": "SELECT username,secondaryemail, email, departmentname FROM USERS"

    },

    "COMPUTEDCOLUMNS": [

        "email"

    ],

    "TABLEINDEXES": {

        "currentusers": [

            "email"

    ] },

    "PREPROCESSQUERIES": [

        "UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.USERNAME = CURRENTUSERS.USERNAME SET NEWUSERDATA.EMAIL = CASE WHEN NEWUSERDATA.departmentname in ('XYZ') THEN NEWUSERDATA.SECONDARYEMAIL WHEN NEWUSERDATA.departmentname not in ('XYZ') then (select email from currentusers where username = newuserdata.username) end"

       ]

}

Basically, if you match the department, use the secondaryemail being mapped on import, otherwise use whatever Saviynt already has stored for the email value. We are seeing for a small number of users, this results in an emptied email field when they leave the company specifically (which clears out many values, including the department field).

Is it related to the LEFT JOIN syntax? I just used the sample documentation, but I'm wondering if it should be re-written so currentusers is the left table:

UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON CURRENTUSERS.USERNAME = NEWUSERDATA.USERNAME SET NEWUSERDATA.EMAIL = CASE WHEN NEWUSERDATA.departmentname in ('XYZ') THEN NEWUSERDATA.SECONDARYEMAIL WHEN NEWUSERDATA.departmentname not in ('XYZ') then (select email from currentusers where username = newuserdata.username) end

[RakeshMG]

Could you please confirm if the values which are getting cleared, are part of import and getting set in Saviynt for Inactive users as well or the values are coming as blank or any other value which is not mentioned in the condition.

Also you try the mentioned updated query as it will validate the users which is part of import only.

[BrandonLucas_BF]

I believe the modifyuserdatajson I posted has nothing to do with this issue.

We do not import the email value from any source. It is always generated by Saviynt. Even during user separation, we do not blank it out or overwrite it.  The only area it can be manipulated is the json above (also included below).

I have found 3 sample users in our environment who are onboarding and for some reason their email value was completely removed in a subsequent update. Let me provide more specifics:

• User imported from Workday on March 13th and email address is generated and stored in user record. This job is configured to generate email on import, so it always creates one using our naming rules.
• Later that day, an update to her Workday record is detected by an incremental job (different from the user creation job). This job is not configured to generate email. It does not map email either. During the update, only two values changed: customproperty25 went from null to containing a date (unrelated to this issue). Email went from being populated to being null. 

Here is the user import mapping and modify json from that job:

UserImportMapping:

{

    "ImportType": "SOAP",

    "ResponsePath": "Body.Get_Workers_Response.Response_Data.Worker",

    "ImportMapping": {

        "USERNAME": "Worker_Data.Worker_ID",

        "FIRSTNAME": "Worker_Data.Personal_Data.Name_Data.Legal_Name_Data.Name_Detail_Data.First_Name",

        "LASTNAME": "Worker_Data.Personal_Data.Name_Data.Legal_Name_Data.Name_Detail_Data.Last_Name",

        "PREFEREDFIRSTNAME": "Worker_Data.Personal_Data.Name_Data.Preferred_Name_Data.Name_Detail_Data.First_Name",

        "LOCATION": "Worker_Data.Employment_Data.Worker_Job_Data.Position_Data.Business_Site_Summary_Data.Name",

        "SECONDARYPHONE": "Worker_Data.Personal_Data.Contact_Data.Phone_Data(Usage_Data->Type_Data->Type_Reference->ID=='WORK' && Phone_Device_Type_Reference->ID=='BF Managed Mobile').@Tenant_Formatted_Phone",

        "TITLE": "Worker_Data.Employment_Data.Worker_Job_Data.Position_Data.Position_Title",

        "STARTDATE": "Worker_Data.Employment_Data.Worker_Status_Data.Hire_Date",

        "PHONENUMBER": "Worker_Data.Personal_Data.Contact_Data.Phone_Data(Usage_Data->Type_Data->Type_Reference->ID=='WORK' && Phone_Device_Type_Reference->ID=='Telephone').@Tenant_Formatted_Phone",

        "EMPLOYEEID": "Worker_Data.Worker_ID",

        "COUNTRY": "Worker_Data.Personal_Data.Name_Data.Legal_Name_Data.Name_Detail_Data.Country_Reference.ID(@type=='ISO_3166-1_Alpha-2_Code')",

        "SECONDARYEMAIL": "Worker_Data.Personal_Data.Contact_Data.Email_Address_Data(Usage_Data->Type_Data->Type_Reference->ID=='HOME').Email_Address",

        "CITY": "Worker_Data.Personal_Data.Contact_Data.Address_Data(Usage_Data->Type_Data->Type_Reference->ID=='WORK').Municipality",

        "STREET": "Worker_Data.Personal_Data.Contact_Data.Address_Data(Usage_Data->Type_Data->Type_Reference->ID=='WORK').Address_Line_Data(@Type=='ADDRESS_LINE_1')",

        "STATE": "Worker_Data.Personal_Data.Contact_Data.Address_Data(Usage_Data->Type_Data->Type_Reference->ID=='WORK').Country_Region_Descriptor",

        "ENDDATE": "Worker_Data.Employment_Data.Worker_Contract_Data.Contract_End_Date",

        "COSTCENTER": "Worker_Data.Integration_Field_Override_Data(Field_Reference->ID=='Cost Center').Value",

        "DEPARTMENTNUMBER": "Worker_Data.Integration_Field_Override_Data(Field_Reference->ID=='Department').Value",

        "REGION": "Worker_Data.Integration_Field_Override_Data(Field_Reference->ID=='Region').Value",

        "REGIONCODE": "Worker_Data.Integration_Field_Override_Data(Field_Reference->ID=='Work State ISO Code').Value",

        "EMPLOYEETYPE": "Worker_Data.Integration_Field_Override_Data(Field_Reference->ID=='Employee Type').Value",

        "EMPLOYEECLASS": "Worker_Data.Employment_Data.Worker_Job_Data.Position_Data.Worker_Type_Reference.ID(@type=='Contingent_Worker_Type_ID')",

        "MANAGER": "Worker_Data.Employment_Data.Worker_Job_Data.Position_Organizations_Data.Position_Organization_Data.Organization_Data.Organization_Support_Role_Data.Organization_Support_Role(Organization_Role_Reference->ID=='Manager').Organization_Role_Data.Worker_Reference.ID(@type=='Employee_ID')",

        "STATUSKEY": "Worker_Data.Employment_Data.Worker_Status_Data.Active",

        "DEPARTMENTNAME": "Worker_Data.Employment_Data.Worker_Job_Data.Position_Organizations_Data.Position_Organization_Data.Organization_Data(Organization_Type_Reference->ID=='SUPERVISORY').Organization_Name",

        "CUSTOMPROPERTY2": "Worker_Data.Employment_Data.Worker_Job_Data(@Primary_Job=='1').Position_Data.@Effective_Date",

        "CUSTOMPROPERTY4": "Worker_Data.Integration_Field_Override_Data(Field_Reference->ID=='Fax').Value",

        "CUSTOMPROPERTY5": "Worker_Data.Integration_Field_Override_Data(Field_Reference->ID=='Company Code').Value",

        "CUSTOMPROPERTY6": "Worker_Data.Integration_Field_Override_Data(Field_Reference->ID=='Business Area').Value",

        "CUSTOMPROPERTY7": "Worker_Data.Integration_Field_Override_Data(Field_Reference->ID=='Cost Center Hierarchy').Value",

        "CUSTOMPROPERTY8": "Worker_Data.Employment_Data.Worker_Job_Data.Position_Data.Business_Site_Summary_Data.Name",

        "CUSTOMPROPERTY9": "Worker_Data.Personal_Data.Contact_Data.Address_Data(Usage_Data->Type_Data->Type_Reference->ID=='WORK').Address_Line_Data(@Type=='ADDRESS_LINE_2')",

        "CUSTOMPROPERTY10": "Worker_Data.Personal_Data.Contact_Data.Address_Data(Usage_Data->Type_Data->Type_Reference->ID=='WORK').Postal_Code",

        "CUSTOMPROPERTY11": "Worker_Data.Employment_Data.Worker_Job_Data.Position_Data.Position_ID",

        "CUSTOMPROPERTY13": "Worker_Data.Integration_Field_Override_Data(Field_Reference->ID=='Org Key').Value",

        "CUSTOMPROPERTY14": "Worker_Data.Employment_Data.Worker_Job_Data.Position_Data.Pay_Rate_Type_Reference.ID(@type=='Pay_Rate_Type_ID')",

        "CUSTOMPROPERTY15": "Worker_Data.Employment_Data.Worker_Status_Data.Original_Hire_Date",

        "CUSTOMPROPERTY16": "Worker_Data.Personal_Data.Contact_Data.Address_Data(Usage_Data->Type_Data->Type_Reference->ID=='HOME').Postal_Code",

        "CUSTOMPROPERTY17": "Worker_Data.Integration_Field_Override_Data(Field_Reference->ID=='Work Space Location').Value",

        "CUSTOMPROPERTY18": "Worker_Data.Integration_Field_Override_Data(Field_Reference->ID=='Self-Max Approval Limit').Value",

        "CUSTOMPROPERTY19": "Worker_Data.Integration_Field_Override_Data(Field_Reference->ID=='Locale').Value",

        "CUSTOMPROPERTY20": "Worker_Data.Integration_Field_Override_Data(Field_Reference->ID=='Language').Value",

        "CUSTOMPROPERTY21": "Worker_Data.Integration_Field_Override_Data(Field_Reference->ID=='Currency').Value",

        "CUSTOMPROPERTY22": "Worker_Data.Integration_Field_Override_Data(Field_Reference->ID=='Time Zone').Value",

        "CUSTOMPROPERTY25": "Worker_Data.Employment_Data.Worker_Status_Data.Continuous_Service_Date",

        "CUSTOMPROPERTY29": "Worker_Data.Personal_Data.Birth_Date",

        "CUSTOMPROPERTY30": "Worker_Data.Personal_Data.Name_Data.Preferred_Name_Data.Name_Detail_Data.Last_Name"

    }

}

ModifyUserDataJSON:

{

    "ADDITIONALTABLES": {

    "USERS": "SELECT username,startdate,customproperty1,CUSTOMPROPERTY30,preferedfirstname,employeeclass,employeetype,customproperty12,secondaryemail, email, departmentname FROM USERS"

    },

    "COMPUTEDCOLUMNS": [

        "startdate",

        "customproperty1",

        "employeeclass",

        "SECONDARYMANAGER",

        "email",

        "customproperty12",

        "displayname"

    ],

    "TABLEINDEXES": {

        "currentusers": [

            "email"

    ] },

    "PREPROCESSQUERIES": [

        "UPDATE NEWUSERDATA SET DISPLAYNAME = CASE WHEN employeeclass is null THEN CONCAT(CUSTOMPROPERTY30,', ',preferedfirstname) ELSE CONCAT(CUSTOMPROPERTY30,', ',preferedfirstname,' ','(Consultant)') END",

        "UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.USERNAME = CURRENTUSERS.USERNAME SET NEWUSERDATA.EMAIL = CASE WHEN NEWUSERDATA.departmentname in ('DepartmentA','DepartmentB','Hive') THEN NEWUSERDATA.SECONDARYEMAIL WHEN NEWUSERDATA.departmentname not in ('DepartmentA','DepartmentB') then (select email from currentusers where username = newuserdata.username) end",

        "UPDATE NEWUSERDATA SET CUSTOMPROPERTY1 = CASE WHEN employeeclass is null and employeetype is not null THEN 'IsEmployee' WHEN employeeclass is not null and employeetype is null THEN 'IsConsultant' ELSE '' END",

        "UPDATE NEWUSERDATA SET SECONDARYMANAGER = SUBSTRING_INDEX(SUBSTRING_INDEX(customproperty12, '(', -1), ')', 1)",

        "DELETE N FROM NEWUSERDATA N JOIN CURRENTUSERS U ON N.USERNAME=U.USERNAME WHERE N.STARTDATE < U.STARTDATE OR N.STARTDATE is NULL",

        "DELETE N FROM NEWUSERDATA N JOIN CURRENTUSERS U ON N.USERNAME=U.USERNAME WHERE U.CUSTOMPROPERTY1='IsConsultant' AND (N.STARTDATE > DATE(NOW()) OR N.STARTDATE is NULL)",

        "DELETE N FROM NEWUSERDATA N JOIN CURRENTUSERS U ON N.USERNAME=U.USERNAME WHERE U.CUSTOMPROPERTY1='IsEmployee' AND (N.STARTDATE > DATE(NOW()) OR N.STARTDATE is NULL)"

    ]

}

Given this code, the only way her email could update would be if her department matches any in the list above in the modifyuserdatajson. This record does not match the criteria. In fact, her departmentname value is null.

[BrandonLucas_BF]

After further testing, it appears to be that if the departmentname field is NULL then it is getting wiped out even though it looks like it should in that case use currentuser.email which does have a value.

Am I misunderstanding this portion of the ModifyUserDataJSON?