Any one know how to do queries from Jira as like from a database?
It looks like: The actual data form is not open, and for good reason. But, the API may be able to pull out data through JQL. If that's true, then we could do pulls like:
Select P.Name, count(*)
from Projects as p
left join Issues as I on p.projectID = I.IssueID
where I.Status in ('Open')
Group by P.Name
order by 2 desc
That's what I want to get to. Obviously, I don't really care about the table names and don't know the underlying structure.
Any help?
Subscribe to:
Post Comments (Atom)
My Mrs loves Jira. She says: This is certainly doable; if you are looking to pull out all the open issues though, you can just do that on the dashboard in Jira. If though you want to do more complex data selections like in SQL, JQL can certainly do that in much the same way, but possibly with slightly different syntax. There should be plenty of online guides if you need them.
ReplyDeleteBrian Ashford The internet suggests the Mrs is incorrect; while JQL is great for pulling back isues matching criteria (even fairly complicated criteria), it's doesn't have built-in aggregates like Group By / Count / etc.
ReplyDeleteThat's what I need; otherwise, we're going to do a full pull of all issues and plop it into a simple database to do reporting for management. I want to avoid that.
She says "You should be able to use a basic JQL filter in the reports available through the dashboard which will generate a report something like what you are looking for."
ReplyDeleteAre you able to add plugins to your Jira? Look up "JQL tricks" or "custom something" or the "issue statistics" one.
My reporting dude has a complete pull set up and then imports into his own access db...
ReplyDeleteAFAIK, the actual way to get SQL access is to write a JIRA plugin. Boo.
ReplyDeleteWe use groovy scripts to do exports for anything that JQL won't let us do.
I'm not sure what p.projectId = I.IssueID is all about—I assumed you wanted a count, by project, of open issues. But that clause looks fishy to me.
If all you want is open issue count by project, you can do that with a pie chart based on an open issue filter. That's not tabular data, though, so that's annoying.
There is SOME limited aggregation you can do using JQL clauses like:
issueFunction in aggregateExpression("...")
I use that all the time to sum up story points for JQL queries.
..but that's not grouping, only summing.
Michael Prescott Was a typo. Should be ProjectID = ProjectID. Doy.
ReplyDelete