nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

SQL to query Custom Fields linked to Insight object schemas

Daz_xm53mr

How might I use SQL to query Jira to identify all Custom Fields in the instance (all projects) that have some linkage to an Insight object schema.

I have access to the Jira SQL Database and can see tables. I can find custom fields but I do not know where or how the Insight linkages are stored in the database.

The exam question put to the team was "Can you tell us which if any Custom Fields throughout our instance , which are linked to Insight objects, are not using the XYA object schema in Insight?"

Anyone know how

Accepted answers

Uday Kiran Bhaviri

Hi Daz,

Definitely yes... there is a way to achieve this using the SQL. You need to look into the 'AO_8542F1_IFJ_OBJ_SCHEMA' table and check how it is linked to the custom fields. Hope this will be helpful.

All comments

Ravi Sagar _Sparxsys_

Hi @Daz

Why do you need to access it from database. You can just look at your custom fields list to check those that are provided by Insight.

Ravi

Daz_xm53mr

Thanks @Ravi Sagar _Sparxsys_ I am new to the two products. I am not new to SQL. So how would I find all custom fields in the instance (all projects) and find which are provided by Insight and what object schema in Insight they come from?

Please.

I can go into Administration->Issues and search for all Type that are Insight objects but that doesnt do what I want. There is no way to query what Insight object schema they come from...

How else might it be done

Daz_xm53mr

anyone?

Uday Kiran Bhaviri

Hi Daz,

Definitely yes... there is a way to achieve this using the SQL. You need to look into the 'AO_8542F1_IFJ_OBJ_SCHEMA' table and check how it is linked to the custom fields. Hope this will be helpful.

Iorio Cristian

Today I also encountered this problem and, after some verification, I was able to develop this query:

SELECT*
FROM AO_8542F1_IFJ_OBJ_JIRAISSUE
WHERE OBJECT_ID IN
( SELECT ID
FROM AO_8542F1_IFJ_OBJ
WHERE OBJECT_TYPE_ID IN
(SELECT ID
FROM AO_8542F1_IFJ_OBJ_TYPE
WHERE ID = <enter the object type ID>))

Step by step:

I query the table AO_8542F1_IFJ_OBJ_JIRAISSUE, which tells me in which custom fields and for which issue a particular object is used;
I narrow down the selection to those objects belonging to a specific object type (potentially, I could further restrict this selection by filtering on the object schema, attaching the query below).

Then, with the appropriate elaborations on the SELECT, I get the information I need.

SELECT*
FROM AO_8542F1_IFJ_OBJ
WHERE OBJECT_TYPE_ID IN
(SELECT ID
FROM AO_8542F1_IFJ_OBJ_TYPE
WHERE OBJECT_SCHEMA ID IN
(SELECT ID
FROM AO_8542F1_IFJ_OBJ_SCHEMA
WHERE ID = <enter the object schema ID>))

Hope this helps!

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

server

sql

jira-software

jira-software-server

atl-gruland

insight