Jira Portfolio Database Query

[Şafak Şahin]

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.

[Stephen Sifers · Accepted solution]

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

[Şafak Şahin]

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]

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]

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:

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

[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