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

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