Troubleshooting the issues in Oracle Identity Manager is a very important facet of any Oracle IDM consultant. Sometimes we need to get the old value and new value of the changed attribute of user. This information is not available from the front end GUI, and therefore it becomes necessary to connect to Oracle Identity Manager's database repository to query the old & new attribute values.

Further, from governance & compliance perspective, sometimes auditors can demand for the old value or new value of certain attributes in OIM. One such example is the email that is generated in OIM using first name and last name.

For those that do not know, we can configure Oracle Identity Manager to re-generate email address when first name or last name is changed. In such an event it may be necessary to get the old and new value.

Below query will give you the output as the all the users whose attribute has been changed with changed attribute name and also the old value and updated value of attributes in OIM.

The SQL below will help you get both the old & new values for these OIM Attributes

SELECT t3.usr_updateby_login,
t1.update_date "Updated On" ,
t1.field_name "OIM_Field",
t1.field_old_value "OIM_Field_Old_Value",
t1.field_new_value "OIM_Field_New_Value"
FROM upa_fields t1,
upa_usr t3
WHERE t1.update_date BETWEEN 'Start Time' AND 'End Time'
AND t1.FIELD_NAME IN('Users.User ID','Users.Email','Users.Display Name','Users.Middle Name','Users.First Name','Users.Status','Users.Last Name')
AND t1.UPA_USR_KEY =t3.UPA_USR_KEY;

Animesh Anand

Add comment