How to retrieve Insight object information from the database

関連コンテンツ

  • 関連コンテンツがありません

お困りですか?

アトラシアン コミュニティをご利用ください。

コミュニティに質問

プラットフォームについて: Server および Data Center のみ。この記事は、Server および Data Center プラットフォームのアトラシアン製品にのみ適用されます。

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.

*Fisheye および Crucible は除く

本記事で説明している手順は、現時点でのものとなります。そのため、一部のお客様で特定の状況下で動作したという報告がありますが、正式にサポートされているわけではなく、お客様の特定のシナリオで動作することを保証するものではありません。

本番環境での実施の前に一通り非本番環境で検証し、成功しなかった場合にはサポートされている代替案にフォール バックしてください。

また、アトラシアン サポートのサポート対象外のご質問の場合には、Community もご活用ください。

目的

To retrieve Insight object information from the database

ソリューション

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>'
結果例
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>'
結果例
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'
結果例
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    |
説明 How to retrieve Insight object information from the database
製品Insight - Asset Management

最終更新日 2024 年 4 月 19 日

この内容はお役に立ちましたか?

はい
いいえ
この記事についてのフィードバックを送信する

関連コンテンツ

  • 関連コンテンツがありません
Powered by Confluence and Scroll Viewport.