How to get all Issue-IDs with a particular custom field filled?

As we re going to update an external system with the according JIRA key (and afterwards get the vice-versa update updating the JIRA issues) we have to get a list with all issues which have a value for

select * from customfield where CFNAME = 'HP OpenView Type' OR CFNAME ='HP OpenView ID';

The export should contains these both CFNAME fields (combined primary key) and the according JIRA issue ID.

The final query will directly run against the JIRA DB, the import job from external will be done manually first.

4 answers

1 accepted

SELECT   p.pkey || '-' || ji.issuenum "JIRA-Key", CV.*
    FROM jiraissue ji,
         (SELECT nvl(cv1.issue,cv2.issue) AS "ISSUE", cv1.stringvalue AS "HP_OpenView_Type", cv2.numbervalue AS "HP_OpenView_ID"
            FROM (SELECT *
                    FROM customfieldvalue
                   WHERE customfield = 11602) cv1
                 inner JOIN
                 (SELECT *
                    FROM customfieldvalue
                   WHERE customfield = 11601) cv2 ON cv1.issue = cv2.issue
                 ) CV,
         project p
   WHERE ji.ID = CV.issue AND ji.project = p.ID

The customfieldvalue table holds the values for a certain issue (you have FK on issue and the custom fields from the SELECT you mentioned), so you can query that.

0 votes

You need to be reading customfieldvalue for the data value. That table has all of the custom field data, so you will need to limit the selection with the issue id(s) (from reading jiraissue), and the custom field id(s), which you get from your SQL already posted.

When you are writing data from external to Jira, remember that you can NOT do it with SQL.

Hi all,

thanks for your reply!
Meanwhile i found the db scheme where i tried to create an according query. As soon as we got some results i ll post it here...

Thanks & regards,

Hans-Hermann

Suggest an answer

Log in or Join to answer
Community showcase
Sarah Schuster
Posted Jan 29, 2018 in Jira

What are common themes you've seen across successful & failed Jira Software implementations?

Hey everyone! My name is Sarah Schuster, and I'm a Customer Success Manager in Atlassian specializing in Jira Software Cloud. Over the next few weeks I will be posting discussion topics (8 total) to ...

2,755 views 11 18
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you
Atlassian Team Tour

Join us on the Team Tour

We're bringing product updates and pro tips on teamwork to ten cities around the world.

Save your spot