Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
Celebration

Earn badges and make progress

You're on your way to the next level! Join the Kudos program to earn points and save your progress.

Deleted user Avatar
Deleted user

Level 1: Seed

25 / 150 points

Next: Root

Avatar

1 badge earned

Collect

Participate in fun challenges

Challenges come and go, but your rewards stay with you. Do more to earn more!

Challenges
Coins

Gift kudos to your peers

What goes around comes around! Share the love by gifting kudos to your peers.

Recognition
Ribbon

Rise up in the ranks

Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!

Leaderboard

Jira Portfolio Database Query

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.

Portolio.png

2 answers

1 accepted

1 vote
Answer accepted
Stephen Sifers
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
Dec 18, 2018

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

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
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
Dec 19, 2018

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:

 image.png

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

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.

Stephen Sifers
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
Dec 20, 2018

Hello again Şafak,

I completely understand the need for SQL querying of the data. I did review your SQL select statement and had to tweak it a bit for PostgreSQL. The output in JSON does show the start and end time in Unix time (minus the trailing 5 zeros) so you will have time tracking abilities from this. Here is what I adjusted for the PostgreSQL statement:

SELECT E.JSON_VALUE, PORTFOLIO_ISSUE.*,JI.Summary, JI.Issuenum, JI.Reporter, JI.Assignee, JI.Priority, JI.created, JI.duedate, JI.updated FROM
(SELECT CAST("ISSUE" AS INTEGER) as ISSUE, "SCENARIO" as SCENARIO FROM "AO_D9132D_ASSIGNMENT_EXT" AE GROUP BY "ISSUE", "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;

Screenshot of the output compared to the Portfolio display:

image.pngimage.png

You should be able to get what you need from your statement or the adjusted statement I provided.

When you have a finished product, I would ask you share it here so other may see what you're doing (sanitize the data before posting please). This is a really neat idea and others should be able to see what you're doing!

Regards,
Stephen Sifers

Hello @Stephen Sifers

 

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

 

Thanks,
Chauhan Raaj

Suggest an answer

Log in or Sign up to answer