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; } } }

Jira Portfolio Database Query

Şafak Şahin_av0lx0

Hi,

I want to access DB data for reporting. I find all active object tables but i couldn't found the Project issue in those tables.

On below screenshot, i want to find where TC-10817 issue is in portfolio tables.

Which ao(active object) should i look?

PS: TC-10817 is an Project type issue.

Accepted answers

Stephen Sifers

Hello Şafak and welcome to the Community!

Accessing the database to directly access data from Jira and Jira portfolio is not the preferred method. We would suggest you review our API to accomplish this as they are more well documented. You can find more about the APIs at the following:

Jira Rest API

APIs for Portfolio for Jira Server

As far as the database tables which are used within Jira Portfolio, they can be found here (Jira 7.12.3 w/ Portfolio 2.20.0):

Portfolio for Jira

AO_A415DF_AOSPRINT
AO_A415DF_AOWORK_ITEM
AO_A415DF_AOSKILL
AO_A415DF_AOTHEME
AO_A415DF_AOSTAGE
AO_A415DF_AOPRESENCE
AO_A415DF_AONON_WORKING_DAYS
AO_A415DF_AOTEAM
AO_A415DF_AORELEASE
AO_A415DF_AOABILITY
AO_A415DF_AOPERMISSION
AO_A415DF_AOPLAN
AO_A415DF_AOREPLANNING
AO_A415DF_AOPERSON
AO_A415DF_AOWORK_ITEM_TO_RES
AO_A415DF_AOSOLUTION_STORE
AO_A415DF_AOSTREAM_TO_TEAM
AO_A415DF_AORESOURCE
AO_A415DF_AOSTREAM
AO_A415DF_AOABSENCE
AO_A415DF_AOESTIMATE
AO_A415DF_AOCUSTOM_WORDING
AO_A415DF_AOAVAILABILITY
AO_A415DF_AODEPENDENCY
AO_A415DF_AOPLAN_CONFIGURATION
AO_A415DF_AOCONFIGURATION
AO_A415DF_AODOOR_STOP
AO_A415DF_AOEXTENSION_LINK

To check your instance for these tables, simply replace the example URL with your server name: http://JIRASERVER:PORT/plugins/servlet/active-objects/tables/list, you will need to be an admin to access this.

I hope this helps you find the data you’re looking for.

Regards,
Stephen Sifers

All comments

Stephen Sifers

Hello Şafak and welcome to the Community!

Jira Rest API

APIs for Portfolio for Jira Server

As far as the database tables which are used within Jira Portfolio, they can be found here (Jira 7.12.3 w/ Portfolio 2.20.0):

Portfolio for Jira

AO_A415DF_AOSPRINT
AO_A415DF_AOWORK_ITEM
AO_A415DF_AOSKILL
AO_A415DF_AOTHEME
AO_A415DF_AOSTAGE
AO_A415DF_AOPRESENCE
AO_A415DF_AONON_WORKING_DAYS
AO_A415DF_AOTEAM
AO_A415DF_AORELEASE
AO_A415DF_AOABILITY
AO_A415DF_AOPERMISSION
AO_A415DF_AOPLAN
AO_A415DF_AOREPLANNING
AO_A415DF_AOPERSON
AO_A415DF_AOWORK_ITEM_TO_RES
AO_A415DF_AOSOLUTION_STORE
AO_A415DF_AOSTREAM_TO_TEAM
AO_A415DF_AORESOURCE
AO_A415DF_AOSTREAM
AO_A415DF_AOABSENCE
AO_A415DF_AOESTIMATE
AO_A415DF_AOCUSTOM_WORDING
AO_A415DF_AOAVAILABILITY
AO_A415DF_AODEPENDENCY
AO_A415DF_AOPLAN_CONFIGURATION
AO_A415DF_AOCONFIGURATION
AO_A415DF_AODOOR_STOP
AO_A415DF_AOEXTENSION_LINK

I hope this helps you find the data you’re looking for.

Regards,
Stephen Sifers

Şafak Şahin_av0lx0

Hi Stephen,

Thanks for kind words and response.

One of our customer requested us a report which includes portfolio's datas.

This report includes lots of columns from lots of table. Therefore we decided easiest way to do this is quering database.

I found the answer :

SELECT JSON_VALUE(E.JSON_VALUE, '$.parent_id') AS PARENT_LINK, E.JSON_VALUE, PORTFOLIO_ISSUE.*,JI.SUMMARY FROM 
(SELECT AE.ISSUE, AE.SCENARIO FROM AO_D9132D_ASSIGNMENT_EXT AE GROUP BY AE.ISSUE, AE.SCENARIO) PORTFOLIO_ISSUE
INNER JOIN ENTITY_PROPERTY E ON E.PROPERTY_KEY = 'jpo-issue-properties' AND E.ENTITY_ID = PORTFOLIO_ISSUE.ISSUE AND JSON_EXISTS(E.JSON_VALUE, '$.parent_id')
INNER JOIN JIRAISSUE JI ON JI.ID = PORTFOLIO_ISSUE.ISSUE;

We need to parse JSON for find parent_link issue.

Is this true?

Stephen Sifers

Hello again Şafak,

Using the API would allow for easier access to the data then accessing it via SQL select statement. If you complete an upgrade to include pr remove an add-on your select statements may break. Along with requiring upkeep over time to ensure they’re accurate. The Jira Portfolio API is still being developed so not all of the data you may need may be present.

You can accomplish some of your goal of assessing Portfolio data along with included issues through API calls. Here are a few example API calls along with the returned JSON data. Along with a screenshot of the example portfolio data:

Request your Portfolio Plan data

curl -u user:password -X GET -H 'Content-Type: application/json' http://JIRA.SERVER.COM/rest/jpo-api/1.0/plan

[{"nonWorkingDays":[],"planTeams":[1],"excludedVersions":[10100,10101],"title":"PortPro","planningUnit":1,"portfolioPlanVersion":0,"id":1}]

Request included issues:

curl -u user:password-X GET -H 'Content-Type: application/json' http://JIRA.SERVER.COM/rest/jpo-api/1.0/hierarchy

[{"title":"Sub-task","issueTypeIds":["10003"],"id":0},{"title":"Story","issueTypeIds":["10002","10004","10001"],"id":1},{"title":"Epic","issueTypeIds":["10000"],"id":2}]

Source document for Jira Portfolio API: Portfolio for Jira - Public API

Additional resources: Integrating with Portfolio for Jira

I was unable to run your select statement without errors on my end. I am running the test instance on a PostgreSQL 9.5 instance. With this said, since I am unable to see results from your query I will not be able to validate any of the data within.

Regards,
Stephen Sifers

Şafak Şahin_av0lx0

Hi Stephen,

Thanks for assistance and quick responses(i think this must be one of the many reasons why atlassian is amazing ).

You're right about the using REST API. But we have limited time(at least our customer said so) and our customer insist for using db query(i have no idea why )

As for the script. It may not work on postgre because this is an pl-sql script. It has JSON_VALUE and JSON_EXIST methods that belong to pl-sql library.

You can use this script, i think it should work on your postgresql

SELECT E.JSON_VALUE AS PARENT_LINK, E.JSON_VALUE, PORTFOLIO_ISSUE.*,JI.SUMMARY FROM 
(SELECT AE.ISSUE, AE.SCENARIO FROM AO_D9132D_ASSIGNMENT_EXT AE GROUP BY AE.ISSUE, AE.SCENARIO) PORTFOLIO_ISSUE
INNER JOIN ENTITY_PROPERTY E ON E.PROPERTY_KEY = 'jpo-issue-properties' AND E.ENTITY_ID = PORTFOLIO_ISSUE.ISSUE
INNER JOIN JIRAISSUE JI ON JI.ID = PORTFOLIO_ISSUE.ISSUE;

E.JSON_VALUE has json object and some of the json objects has parent_id value for issues which has parent_link.

What i want to accomplish is list those parent issues in the excel rows and show these issues' efforts month by month. So basically i need access theese parent issues and their efforts.

Dharmraj Chauhan

Hello @Stephen Sifers

Is it possible to retrieve all the Epics/Issues associated with the plans using the REST API?

Thanks,
Chauhan Raaj

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

developer

jira-server

atl-adm

advanced-roadmaps

database

active-objects