Help with finding Object Username field in Insight DB

[Mark Spence]

Hello

Recently we had an accidental deletion of the Username field for 1800+ objects in our Insight (installed on Jira Service Management Server).
My plan is to restore these in the backend postgres DB by pulling the OLD_VALUE from the history table and inserting into the objects username field. The below sql gets me the OLD_VALUE that was removed however I am struggling to find where in schema/table structure the Username is stored per object. I suspect that this is more complex as its likely pulled/linked to the main Jira user tables as its a username picker. Any pointers would be appreciated please.

select a."NAME", c."OLD_VALUE" from "AO_8542F1_IFJ_OBJ" AS a inner join "AO_8542F1_IFJ_OBJ_TYPE" AS b ON a."OBJECT_TYPE_ID" = b."ID" inner join "AO_8542F1_IFJ_OBJ_HIST" AS c ON a."ID" = c."OBJECT_ID" WHERE b."NAME" = 'Computing Devices' AND c."AFFECTED_ATTRIBUTE" = 'Username';

Thank you in advance.

M

[PD Sheehan · Accepted solution]

I think you'll find that you're not likely to find a lot of people willing to suggest how you might go about inserting these attributes via a SQL statement. There are connecting tables that might need other rows inserted and unique ids generated.

I would recommend the following instead:

1. Exporting those usernames (include the object's label rather than the name as well as the key if the label can be non-unique) from the db into a CSV
2. Create an Import configuration to import the CSV back into your main objects with the appropriate mapping (hence why you need the object's label attribute)
3. Run the import just once

[Mark Spence]

Thank you Peter for your response. I can understand the concern for editing the DB in the backend. We have chosen to manually correct in the front end by sharing amongst the team to play it safe. Its been completed now.