This is a guest post written by Maarten Cautreels, expert Atlassian Admin, AUG leader, and Atlassian Community Champion. You can read Maarten’s articles and ask him questions on Atlassian Community. Thank you for your great advice, Maarten!
Jira Software is a cool tool but what makes it even cooler are its powerful search features. By now, you have probably already discovered Jira Query Language (JQL) and learned the basics, but here are some of my favorite tips and commands that I use all the time in JQL. What I call the “secret powers of JQL”.
Filter on users
currentUser()
What if you would like to find all issues to which you’ve been assigned? This is basically all the work that you are expected to do. Good to know, right?
You can use the JQL function currentUser() for this:
assignee = currentUser()
This filter works not only for you but it’ll work for others too if you change the value currentUser() to the user’s name. If you save this filter and make it available to others, they’ll be able to see all issues that have been assigned to them.
membersOf()
You could do the same for all members of a certain squad/team/group. The most important thing to note here is the use of IN instead of =.
assignee IN membersOf(“Collaboration Squad”)
This will show you all the tickets assigned to members of a team, so you can see how much work they have on their plates.
Date filtering
In the article about History Searches we covered many ways of filtering by using dates. Most of the examples were using static dates. But Atlassian has a set of functions to support dynamic date filtering as well.
Static dates represent a specific moment in time, like “give me all the issues created on the 1st of January 2017”. Dynamic dates represent the time period you determine, for example: “give me all the issues created 5 days ago”.
startOf…()
These functions are particularly helpful if you are querying for a certain period. For example, all issues created within this week. Where you could use a combination startOfWeek() and endOfWeek():
created > startOfWeek() and created < endOfWeek()
Or even more advanced, if you’d like only issues created in the current workweek:
created > startOfWeek(1d) and created < endOfWeek(-1d)
There’s a couple of helpful functions for ‘startOf…’.
- startOfDay()
- startOfWeek()
- startOfMonth()
- startOfYear()
The following example gives you all issues that were created this year:
created >= startOfYear()
You can also use dynamic dates in the startOfWeek and endOfWeek functions. You can only enter in one item (so just -1w or -2w not -2w -1d). So for example, startOfWeek(“-8d”) OR startOfWeek(“-15d”) OR startOfWeek(“-22d”) should be accepted.
But if you want all issues created on the past 3 mondays:
(created <= startOfWeek(“-5d”) AND created >= startOfWeek(“-6d”)) OR (created <= startOfWeek(“-12d”) AND created >= startOfWeek(“-13d”)) OR (created <= startOfWeek(“-19d”) AND created >= startOfWeek(“-20d”))
This query takes into account the fact that created contains time as well (start of day and end of day).
endOf…()
The same functions exist for the end of a day, week, month or year:
- endOfDay()
- endOfWeek()
- endOfMonth()
- endOfYear()
now()
This command will show you a list of all issues that were due up until now and not just today. Catch up on any work that may have slipped through the cracks!
The now() function refers to the current date and time and is often used for filtering on the due date:
due <= now()
lastLogin()
If you want to keep track of all the issues that have been created or updated since you last logged in, you could use this function:
created >= lastLogin() OR updated >= lastLogin()
Filter on tasks
Perhaps you want to search for tasks on Kanban boards or a scrum backlog. A kanban/scrum board itself is backed by an existing JQL Filter.
If you know the filter’s name or id you can do the following and add extra clauses to get a more specific result:
filter = 123456 AND …
or
filter = ‘Filter Name’ AND …
Many more
These are just a handful of available functions that I use all the time. To find a complete list, check out the Jira Documentation site. And, to get even more JQL training, check out our tutorial videos.
Questions/Problems?
If you haven’t yet, check out our blog series on JQL:
- JQL: the most flexible way to search Jira
- JQL: Using filters and subscriptions
- JQL: Functions, history, and sorting
- Getting the most out of JQL with Atlassian Marketplace add-ons
Or, ask me a question in the Atlassian Community.