By Joe Emett
In this post, I’d like to give some tips on some useful JIRA Query Language (JQL) queries. By no means exhaustive, they are things I’ve come to use and love in Jira. It will focus on getting started with JQL and is not meant as a Jira tutorial. Syntax rules are outside the boundaries of this blog. Rather than giving descriptions on JQL keywords I hope this acts as a quick-start guide for you to build your own queries.
These tips assume the user is able to navigate to a Jira project backlog and range from rookie to more seasoned advice.
1. Create your own JQL with 2 character presses
In the top right of any Jira screen is the QuickSearch text box:
When you enter my, you will redirected to the Issue Navigator and be presented with Jira issues that are currently assigned to you.
You can then tweak your JQL to refine the set of results, for example if you want just the open issues you could modify the query by including AND status = 'Open' :
You will find other smart query shortcuts here, at the very least QuickSearch offers fast access to the Issue Navigator and by entering free text into QuickSearch, this will search all Jira projects for instances of the text in these fields: Summary, Description and Comments.
This blog now assumes you are comfortable with accessing the Issue Navigator.
2. Build on what is there already
Every backlog has an associated Jira query. If you have access to a Jira project backlog, you are looking at the results of a JQL query. On every Jira Scrum board, there is a ‘Board’ dropdown list:
On pressing ‘Configure’, on the ‘General’ tab there are the filter details the board is based on:
Even though you may not have privileges to edit the query, you will be able to press View Filter Query, change the JQL query and Save as, as you wish.
Jira Reports and Gadgets on Dashboards, are other good places to look, as these are based on existing JQL queries you can dip into and change.
Look out for View in Issue Navigator to see more JQL.
3. Just created an issue and you’ve forgotten the Key?
You’ve created an issue, gone and made a cup of coffee and can’t remember the Key, and it’s not on your board.
In the Issue Navigator enter:
project = ACE and created >= startOfDay() ORDER BY Key DESC
4. Use Empty to find forgotten issues
Find issues with no fix version. In the Issue Navigator enter:
project in (ACE, MNT) AND status = Open AND fixVersion is EMPTY ORDER BY created DESC
5. Commented by a colleague
You know the project and the colleague who commented on an issue, you’re just not sure which issue. You think the comment was made in the last 30 days, but you query 60 days back, just in case: In the Issue Navigator enter:
project = ACE and issueFunction in commented('by cthompson after startOfDay(-60d)')
Please note, you require the ‘Adaptavist SciptRunner for Jira’ plug-in to include issueFunction in your query. issueFunction is a search mechanism allowing you to perform searches that you would not be able to do with ‘out of the box’ JQL.
6. Tracking Assignee
You need to locate a Jira Issue that was assigned to a member of your team, but you’re not sure who. In the Issue Navigator enter:
project = ACE AND fixVersion in versionMatch(5.1) AND assignee WAS IN (cthompson, tbagnall, vhemmert, jemett) ORDER BY Rank
You can subscribe to any filter, where Jira will run the search and email you the results. This is particularly useful when your project is in a stabilization phase and you would like daily updates containing the latest bugs. In the filter view, click on Details then New subscription:
7. Multiple teams in one project
You are sharing a project with another team, but want your own board, sprints and version control. Create some JQL that will track your Epics, Stories and Sub-Tasks. In the Issue Navigator enter:
project = SHA AND (key in (sha-847, sha-856, sha-860, sha-445) OR 'Epic Link' in (sha-847, sha-856, sha-860, sha-445)) OR issueFunction in subtasksOf('\'Epic Link\'=sha-847') OR issueFunction in subtasksOf('\'Epic Link\'=sha-856') OR issueFunction in subtasksOf('\'Epic Link\'=sha-436') OR issueFunction in subtasksOf('\'Epic Link\'=sha-445') ORDER BY Rank ASC
8. Bugs fixed after a certain date
You would like to track the bugs where status has been changed to Review or Verify after a date and want to exclude issues that have been pinged back to the Developers. In the Issue Navigator enter:
project = SHA AND fixVersion in versionMatch(5.1) AND (status changed to Review after '2015/11/29' OR status changed to Verify after '2015/11/29') AND Status != Open AND status != 'In Progress' ORDER BY priority DESC
9. More on Empty
For some fields, such as ‘Labels’ you need to explicitly query if the field is ‘Empty’ as part of your criteria, for example .. (labels !=CMM) .. will only return issues with the label field that has been populated, ignoring issues with no labels. To fully capture the set of records you’re really after use: .. (labels is empty or labels != CMM) ..
Incorporating this logic into a larger query, in the Issue Navigator enter:
project = SHA and fixVersion='5.1' and (labels is empty or labels !=CMM) and type in (Story,Task,Bug) order by rank
10. Aggregate Expressions
JQL is not great for aggregation, but I find totaling points of stories that fall into certain sprints useful. In the Issue Navigator enter:
project = SHA and sprint in('CMM Sprint 5', 'CMM Sprint 6', 'CMM Sprint 7') and issueFunction in aggregateExpression('Total points', 'storyPoints.sum()')
As elsewhere in this post, you need the ‘Adaptavist SciptRunner for Jira’ plug-in to include issueFunction in your query.
