Becoming a JIRA Search Ninja - The secret powers of JQL

[#1]

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:

• Basic searches in JIRA
• Advanced searches in JIRA (using JQL)
• History searches in JIRA
• The secret powers of JQL

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.

[Sara Halper]

Super clear. Thanks!

[Prem Chudzinski _extensi_]

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

[Maarten Cautreels]

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

[Karen Hayden]

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.

[Olivia Ross]

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

[Jillian Campbell]

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? 

[Jillian Campbell]

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

[Maarten Cautreels]

@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

[Petr Papousek]

see Advanced searching - functions reference for other goodies :-)

[Hollis Sosnowitz]

I'm trying to create the following search Parent Link in (filter=30760).

In other words, return the issues where the Parent Link is in the result of a predefined filter.

where Filter=30760 is: Issuetype = Initiative.

 Ideally I'd like the children, and grandchildren. This is to help me set up a portfolio plan.

Help?

[Tim Curtis]

@Maarten Cautreels thank you for this 4 page blog.  I am off and running on my JQL journey.  This was a great start.