Middle name change
Budget: $25 – $50 USD
Middle name is not an attribute which Azure AD has in place and therefore we can not map middle name when creating new users. After some digging around I feel there might be a possibility to update Middle Name for users using SQL and data from the users contact profile. The common key between the User profile and Contact employee profile is the Employee ID which is the user_username in the user profile and the Employee ID in the respective contact profile.
Possible solution
Before creating usr_staging table in the AAD sync job develop a SQL query that will dump employee ID and Middle Name from table tblIVTextData in the following format. You can run a pivot table in SQL to help provide an idea on how the table should look.
Employee ID | Middle Name
You can use this sql to help provide an idea on how the new table should be structured. Run in PROD
from
(select content_id, field_id, Field_value
from tblIVTextData, usr_new where content_id = '472073' ) as source_table
PIVOT
(Max(field_value)
for field_id in ([23701] ,[541])
)as PIVIOT_Table
Once you have table created you can then develop SQL to set the middle name in the user profile, by comparing the user_username in the tbluser and new table that will hold Employee ID|Middle Name.
Possible solution
Before creating usr_staging table in the AAD sync job develop a SQL query that will dump employee ID and Middle Name from table tblIVTextData in the following format. You can run a pivot table in SQL to help provide an idea on how the table should look.
Employee ID | Middle Name
You can use this sql to help provide an idea on how the new table should be structured. Run in PROD
from
(select content_id, field_id, Field_value
from tblIVTextData, usr_new where content_id = '472073' ) as source_table
PIVOT
(Max(field_value)
for field_id in ([23701] ,[541])
)as PIVIOT_Table
Once you have table created you can then develop SQL to set the middle name in the user profile, by comparing the user_username in the tbluser and new table that will hold Employee ID|Middle Name.