How to retrieve Insight object information from the database
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
The steps outlined on this article are provided AS-IS. This means we've had reports of them working for some customers — under certain circumstances — yet are not officially supported, nor can we guarantee they'll work for your specific scenario.
You may follow through and validate them on your own non-prod environments prior to production or fall back to supported alternatives if they don't work out.
We also invite you to reach out to our Community for matters that fall beyond Atlassian's scope of support!
Purpose
To retrieve Insight object information from the database
Solution
The queries presented in this article were written for the Postgres DB. You may need to rewrite portions of it to fit your particular database type.
Also, the queries are mostly examples and haven't been tuned for the best performance or execution plan.
To get a list of object keys of a specific object schema
The SQL query below returns all Insight objects regardless of the object schema or object type from the database with 3 columns, namely Object Key, Label and Object Type. Replace <schema_key> with the object schema key accordingly.
To view the object directly in Jira with the object key, you can use this URL directly with the Jira base URL and object key replaced accordingly: <Jira_base_URL>/secure/insight/assets/<object_key>.
select
(schema."OBJECT_SCHEMA_KEY" || '-' || object."ID") as "Object Key",
object."LABEL" as "Object Label",
type."NAME" as "Object Type"
from "AO_8542F1_IFJ_OBJ" object
join "AO_8542F1_IFJ_OBJ_TYPE" type on object."OBJECT_TYPE_ID" = type."ID"
join "AO_8542F1_IFJ_OBJ_SCHEMA" schema on type."OBJECT_SCHEMA_ID" = schema."ID"
where schema."OBJECT_SCHEMA_KEY" = '<schema_key>'
To get a list of objects created by a specific user
The SQL query below returns all Insight objects created by a specific user. Replace <username> with the desired username.
select
(schema."OBJECT_SCHEMA_KEY" || '-' || object."ID") as "Object Key",
object."ID", object."LABEL", object."CREATED", "app_user"."lower_user_name" as "Creator"
from "AO_8542F1_IFJ_OBJ" object
join "AO_8542F1_IFJ_OBJ_TYPE" type on object."OBJECT_TYPE_ID" = type."ID"
join "AO_8542F1_IFJ_OBJ_SCHEMA" schema on type."OBJECT_SCHEMA_ID" = schema."ID"
join "AO_8542F1_IFJ_OBJ_HIST" history on history."OBJECT_ID" = object."ID"
join "app_user" on history."ACTOR_USER_KEY" = "app_user"."user_key"
where history."TYPE" = 0 and "app_user"."lower_user_name" = '<username>'
To get a list of objects a specific user is watching
The SQL query below returns all Insight objects that a specific user is watching. Replace <username> with the desired username.
select
(schema."OBJECT_SCHEMA_KEY" || '-' || object."ID") as "Object Key"
from "AO_8542F1_IFJ_OBJ" object
join "AO_8542F1_IFJ_OBJ_TYPE" type on object."OBJECT_TYPE_ID" = type."ID"
join "AO_8542F1_IFJ_OBJ_SCHEMA" schema on type."OBJECT_SCHEMA_ID" = schema."ID"
join "AO_8542F1_IFJ_OBJ_WATCH" watch on object."ID" = watch."OBJECT_ID"
join "app_user" on watch."USER_KEY" = watch."USER_KEY"
where "app_user"."lower_user_name" = 'admin'