Find BitBucket Commits, PRs, and Reviews per Issue via SQL

[Tom Svitak]

From Jira, I can see within the Developemnt panel that there are a certain number of commits, pull requests, and reviews for a particular issue. How do I query that within SQL so we can start to report on it in a dashboard outside of Jira?

[Laurie Sciutti]

Hey @Tom Svitak and welcome to the Community!  Have a look here.  HTH!

[Tom Svitak]

@Laurie Sciutti Terrific place to start! Thank you so much for the timely reply.

I should have done a better job clarifying what I'm trying to do.  I'm trying to find details (i.e. time stamps, environments, etc.) about the particular commits and pull requests, etc. For example, if I clicked on those 32 commits, I'd see when they were done, by whom, etc. Same with the reviews and PRs. Presumably a PR might be rejected by QA and I'm trying to see how frequently we have rework. 

The end result is ultimately to statistics that answer a question like "Which issues have the most re-work by the software team?"

[Laurie Sciutti]

Hm.  Maybe something like below (change your status values accordingly)?  This assumes that you have incorporated something in your workflow (maybe triggers for this to happen automatically) or process that indicates a Story should move from some development review status back to an "in development" status.  Then report on whatever fields you'd like to have included (i.e. environment, assignee).  Another alternative would be to run this query directly in BitBucket ~ it might be more accurate...

type = Story AND status changed FROM "Awaiting Code Review" TO "In Progress" and development[pullrequests].declined > 1