How to Link JiraIssue to Portfolio Team field in SQL

Question

Trying to find a way to back-end update some data to support changes needed for Portfolio.  I need to find a way to get a list of issues, via SQL, that have the Team field equal to a specific value.  I can't seem to figure it out and the customFieldValue doesn't seem to be linking it as expected.  Have this so far:

SELECT * FROM jiraissue i
join customfieldvalue cfv on cfv.ISSUE = i.ID
and cfv.CUSTOMFIELD = 11900 --custom field for Team

SMAtlassianMber · Answer

I am also trying to obtain a list of Jira issues with Team Accounts/Products, but I get no results. There are 978 issues with this field.

SELECT ji.pkey,
ji.issuenum,
ji.PROJECT,
ji.issuetype,
ji.SUMMARY,
ji.DESCRIPTION,
ji.PRIORITY,
ji.CREATED,
ji.UPDATED
FROM dbo.jiraissue ji
join AO_D9132D_ASSIGNMENT_EXT assg on ji.ID = assg.ID
WHERE assg.TEAM = 'Accounts/Products'

Any suggestions?

Chander Inguva · Answer

Hey Dan,

Other way is to check the team name's using REST API

https://docs.atlassian.com/portfolio-for-jira-server/REST/2.13.0/teams/?_ga=2.221190620.511063636.1525110460-67205773.1519327224#tag-teams

Thanks

Chander

Chander Inguva · Answer

Hey Dan,

Team is a locked field and you cannot query it via SQL. Values for Portfolio will reside under Active Objects Tables

AO_D9132D_ *******

I could get these

SELECT ID from AO_D9132D_PLAN WHERE TITLE LIKE '%test Port%'

SELECT  TEAM_ID FROM AO_D9132D_PLANTEAM WHERE PLAN_ID = 29

SELECT * FROM jiraissue WHERE ID in (SELECT ISSUE FROM AO_D9132D_ASSIGNMENT_EXT ext WHERE ext.[PLAN]=29 AND ext.TEAM =96 )