Becoming a JIRA Search Ninja - The secret powers of JQL

1488465441776-8820e73c-c3fb-4242-a52a-5ff125b525fa_ (2).jpg

JIRA is a cool tool but it becomes even cooler if you know how to leverage it's powerful search features! Some of you probably already discovered the powerful JIRA Query Language (JQL) and that's what this series of blogposts is all about. In the next few posts we'll cover:

The secret powers of JQL

In Advanced searches in JIRA we covered the basics of JQL but there's much much more! We won't be able to cover (allthethings)  but I've done my best selecting the most relevant JQL features.

Filter on Users

currentUser()

What if you would like to find all issues to which you've been assigned. Basically all the work that you are expected to do.

You can use the JQL function currentUser() for this:

 

assignee = currentUser()

And the good news is, this filter will not only work for you but it'll work for others too. This filter is somehow personalized to the logged in user. In the first post of this series we've covered how to save a filter that can be used by others, so if you'd save this filter and make it available to other they'll be able to use the filter 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")

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 provided a set of functions to support dynamic date filtering as well.

startOf...()

There's a couple of functions 'startOf...'. Here's a list:

  • startOfDay()
  • startOfWeek()
  • startOfMonth()
  • startOfYear()

The following example gives you all issues that were created this year:

 

created >= startOfYear()

 

endOf...()

The same functions exist for the end of a day, week, month or year:

  • endOfDay()
  • endOfWeek()
  • endOfMonth()
  • endOfYear()

now()

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()


Many more

These are just a hand full of available functions. The complete list can be found on the official JIRA Documentation.

Questions/Problems?

Please checkout this page from Atlassian first: https://confluence.atlassian.com/jiracoreserver073/basic-searching-861257184.html?_ga=2.12645040.62589169.1494573379-798135519.1489390862#Basicsearching-troubleshootingTroubleshooting

If that doesn't help, please use the comments.

 

9 comments

Super clear. Thanks!

And how do you search for tasks on Kanban boards or scrum backlog?

Maarten Cautreels Community Champion May 17, 2017

Hi Przemyslaw, 

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 ...

Best,

Maarten

Maarten - I would like to find where assignee is a "memberof" a Portfolio Shared Team, is this possible?  How can it be acheived?

Using the syntax in this article generates the error "..group does not exist.".  Because it is not a JIRA group, but a Portfolio Shared Team.  So was hoping there is another way to utilize the Team from Portfolio without have to create & maintain JIRA groups.

Thanks,

Karen.

Hello Maarten.

Can the startofweek/endofweek/month functions cope with the dynamic dates? I have tried various ways to get startOfWeek to accept parameters like 'startOfWeek("+1w -1d")' but I always get an error:

Duration for function 'endOfWeek' should have the format (+/-)n(yMwdm), e.g -1M for 1 month earlier.

I want to find a way to query for all the Mondays in the next quarter (for example), and I was hoping I could do:

startOfWeek("1w 1d") OR startOfWeek("2w 1d") OR startOfWeek("3w 1d")...

etc

 

I'm trying to figure out how I can set up a filter where the watcher is the current user, but the assignee is not the current user.  I don't know SQL and have had a go at trying to make this work but keep hitting blocks.  Any suggestions please? 

Ooh! I figured it out (and how to use the search functions on this site!)

Maarten Cautreels Community Champion Aug 14, 2017

@Olivia Ross - Sorry for the late response. You should be able to use dynamic dates in the startOfWeek and endOfWeek functions.

You can only pass in one item (so just -1w or -2w not -2w -1d). So in your case 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).

Hope this helps.

Best,

Maarten

Comment

Log in or Sign up to comment
How to earn badges on the Atlassian Community

How to earn badges on the Atlassian Community

Badges are a great way to show off community activity, whether you’re a newbie or a Champion.

Learn more
Community showcase
Published Thursday in Jira Service Desk

How the Telegram Integration for Jira helps Sergey's team take their support efficiency to the bank

...+ reading Fantasy). The same is true for him at the bank he works for: Efficiency is key when time literally equals money. Read on to learn how Sergey makes most of the time he has by...

213 views 0 2
Read article

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