How to retrive list of bugs associated with a component in JIRA project (SQL Query)

Hi

I am trying to retrieve list of bugs associated with a component in JIRA project, can someone help me with query. I have been able to extract component from JIRA project, just need some help with list of bugs associated with component.

Thanks

3 answers

1 vote
Dieter Community Champion May 02, 2012

Here is the solution to search for isssue with Component 'My component' in project with key 'JRA'

select jiraissue.* from jiraissue,project,issuetype,nodeassociation,component 
where 
component.cname = 'My component' and 
project.pkey = 'JRA' and 
issuetype.pname = 'Bug' and
jiraissue.project = project.id and 
jiraissue.issuetype = issuetype.id and 
nodeassociation.association_type = 'IssueComponent' and
nodeassociation.source_node_entity = 'Issue'  and
nodeassociation.source_node_id = jiraissue.id  and
nodeassociation.sink_node_entity = 'Component'  and
nodeassociation.sink_node_id = component.id

Dieter, Thanks for the response , appears query is returning 0 rows. I believe component is a unique value in entire JIRA project why do we need to associate project(.pkey) with it?

The ID is unique but not the name. If you want to query by component ID alone you wouldn't need to join on project.

Dieter Community Champion May 03, 2012
If you have the component ID you could use this

select jiraissue.* from jiraissue,issuetype,nodeassociation,component 
where 
component.id = 12345 and 
issuetype.pname = 'Bug' and
jiraissue.issuetype = issuetype.id and 
nodeassociation.association_type = 'IssueComponent' and
nodeassociation.source_node_entity = 'Issue'  and
nodeassociation.source_node_id = jiraissue.id  and
nodeassociation.sink_node_entity = 'Component'  and
nodeassociation.sink_node_id = component.id

Dieter Community Champion May 03, 2012
Or completely with id's

select jiraissue.* from jiraissue,nodeassociation
where 
jiraissue.issuetype = 3 and 
nodeassociation.association_type = 'IssueComponent' and
nodeassociation.source_node_entity = 'Issue'  and
nodeassociation.source_node_id = jiraissue.id  and
nodeassociation.sink_node_entity = 'Component'  and
nodeassociation.sink_node_id = 12345

just this looks less readable at first ...

Thank you it works :)

0 vote

Hi Alok,

Try using the following query in the advanced search (JQL)

project = ProjectName and issuetype = Bug and component = "Your Component"

If you don't care what the project is or if it's cross project, you can just do

issuetype = Bug and component = "Your Component"

Replace "Your Component" with the component name you'd like to search for...

Hi Adam,

Thank you for response, I will give it a try. However, I am looking for SQL query, I hope someone will post the query :)

Ah! Sorry. Misread the title of the post.

0 vote
Dieter Community Champion May 02, 2012

If the project key is 'JRA' , the name of the component is 'My Component and the name of the issue type is 'Bug' you could use this:

select jiraissue.* from jiraissue,project,issuetype,nodeassociation,component 
where 
component.cname = 'My Component' and 
project.pkey = 'JRA' and 
issuetype.pname = 'Bug' and
jiraissue.project = project.id and 
jiraissue.issuetype = issuetype.id and 
nodeassociation.association_type = 'IssueComponent' and
nodeassociation.source_node_entity = 'Issue'  and
nodeassociation.source_node_id = jiraissue.id  and
nodeassociation.sink_node_entity = 'Component'  and
nodeassociation.sink_node_id = component.id;

Suggest an answer

Log in or Sign up to answer
Atlassian Community Anniversary

Happy Anniversary, Atlassian Community!

This community is celebrating its one-year anniversary and Atlassian co-founder Mike Cannon-Brookes has all the feels.

Read more
Community showcase
Julia Dillon
Posted Tuesday in Jira

Tell us how your team runs on Jira!

Hey Atlassian Community! Today we are launching a bunch of customer stories about the amazing work teams, like Dropbox and Twilio, are doing with Jira. You can check out the stories here. The thi...

200 views 1 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