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>.

For PostgreSQL
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>'
Example result
PostgreSQL query to get object keys from object schema with key 'BOOK' and object type 'Normes'
select
(schema."OBJECT_SCHEMA_KEY" || '-' || object."ID") as "Object Key",
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" = 'BOOK' and type."NAME" = 'Normes'
Object Key |LABEL|Object Type|
-----------+-----+-----------+
BOOK-401926|Two  |Normes     |
BOOK-401925|Four |Normes     |


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.

For PostgreSQL
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>'
Example result
PostgreSQL query to retrieve all objects created by user 'admin'
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>'
Object Key|LABEL                                                 |CREATED                |Creator|
----------+------------------------------------------------------+-----------------------+-------+
CVE-339954|* (openqa)                                            |2021-07-09 14:58:11.507|admin  |
CVE-339956|* (openshift_deployer)                                |2021-07-09 14:58:11.516|admin  |
CVE-339961|* (openwsman)                                         |2021-07-09 14:58:11.551|admin  |
CVE-339964|* (orangehrm)                                         |2021-07-09 14:58:11.558|admin  |
CVE-339966|* (orion_platform)                                    |2021-07-09 14:58:11.561|admin  |
CVE-339967|* (os-vif)                                            |2021-07-09 14:58:11.566|admin  |


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.

For PostgreSQL
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'
Example result
PostgreSQL query to retrieve a list of objects that user 'admin' is watching
select
(schema."OBJECT_SCHEMA_KEY" || '-' || object."ID") as "Object Key",
object."LABEL"
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'
Object Key |LABEL   |
-----------+--------+
ASSET-98798|Downtown|
BOOK-401925|Four    |
DescriptionHow to retrieve Insight object information from the database
ProductInsight - Asset Management

Last modified on Apr 19, 2024

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.