Forums

Articles
Create
cancel
Showing results for 
Search instead for 
Did you mean: 

Help with finding Object Username field in Insight DB

Mark Spence
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
July 19, 2022

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

1 answer

1 accepted

0 votes
Answer accepted
PD Sheehan
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
July 19, 2022

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
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
July 21, 2022

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.

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events