Using JQL in Jira

Summary

JQL (Jira Query Language) is a powerful tool to help you search for, filter, and sort tickets. Here we'll go over some of the more complex uses for searches and filters your team might have.  You can reference these Jira pages for a full run down of JQL.

Fields

The first part of any query is going to be the Jira Field that you're querying. Examples of Fields are: Reporter, Project, Assignee, Due, Labels, Parent, etc. You can also query custom fields with their Custom Field ID, which your Manager or a Jira Administrator can help you find.

Operators

There are about 15 operators that can be used in JQL but each field may only support a subset of them. Here is a list of fields and their supported operators. Here are the most commonly used operators and what they mean:

The "=" operator is used to find an EXACT match in the specified field. See "~" for text fields.

=
Reporter = JSmith

The "!=" operator is used to find anything that is not an exact match in the specified field. See "~" for text fields.

!=
Reporter != JSmith

The "IN" operator takes the place of multiple "=" so as to find an exact match from a list of values in the specified field.

IN
Reporter IN (JSmith, JAppleseed, GWashington)

The "IN" operator takes the place of multiple "!=" so as to exclude any exact matches from a list of values in the specified field.

NOT IN
Reporter NOT IN (JSmith, JAppleseed, GWashington)

The "IS" operator is only used to find if a field is Empty or Null.

IS
Assignee IS Empty

The "IS NOT" operator is only used to find if a field is not Empty or Null.

IS NOT
Assignee IS NOT Empty

The "~" operator is used to find if a field contains text. No quotes searches for a single word, quotes means they will search for all words inside the quotes without needing them in the order entered and "\"short phrase\"" means it will find text that is an exact match to the entered phrase.

~
Summary ~ change
Summary ~ "change password"
Summary ~ "\"change password\""

Creating Complex Queries

Now that you've learned about how to search within a field, we can chain them together using these keywords to make them much more powerful. These are the two most commonly used keywords but the full list is here.

The keyword “AND” combines multiple search parameters or clauses together so that each one has to be true to return a result.

AND

Returns all tickets with IT-Team label that are in any kind of Open status.

labels = IT-Team AND status not in (Closed, Canceled, Resolved, Completed, Done)

Returns all tickets with IT-Team label that have work logged on them.

labels = IT-Team AND timespent is not EMPTY

The keyword “OR” separates multiple search parameters so that each one can be true to return a result.

OR

Returns all tickets with either a Time to First Response SLA time with < 1 hour remaining OR a Time to Resolution SLA time with < 4 hours remaining.

"Time to first response" <= remaining("1h") OR "Time to resolution" <= remaining("4h")

Parentheses 

Parentheses “()” can be used to set the order of operations or allow parts of the query to be grouped. 

The parenteses in this case simplifies the logic as you can see the first example is much shorter and easier to understand than the second line.

Project = Tech And (Status = Escalated OR timespent IS NOT EMPTY)

Project = Tech And Status = Escalated OR Project = Tech And timespent IS NOT EMPTY

Using Complex Queries

Showing all Open IT Tickets while excluding sub-tasks.

status not in (closed, Canceled, Resolved, done, completed, Done) AND issuetype != sub-task AND labels = IT-Team

Showing all Resolved IT Tickets with a Customer Satisfaction Rating.

filter = "All IT Tickets" AND status in (closed, resolved, completed) AND Satisfaction is not EMPTY ORDER BY updated DESC

Building On Other Filters

Reusing the "All Open IT Tickets" filter so that we don't need to rebuild that logic and adding the specification to show a subset of that filter with less than 1 hour in the Time to First Response SLA.

filter = 10031 AND "Time to first response" <= remaining("1h")

Using Filters to Build a Team Dashboard

Building a Dashboard is a way to view multiple filters and other data in one place. 

To Create or Not To Create Documentation

Documentation is an extremely important part of any organizations tech support system. You need to have good documentation on how things work and tie together so that you can efficiently and effectively troubleshoot. But something often overlooked and underutilized is user facing documentation. Should you create a “how to” document for each piece of software or process in your organization? Where to draw the line is a grey area.

This grey area is the middle of a spectrum from waaaay too much -> waaaay too little information provided for your users. It’s important for you to give your users something that they can use as a starting point for software or processes but you will drive yourself crazy if you try to document everything. The loose rules I like to use for if I need to document something for a user are as follows:

• Is this going to require more than 5 clicks?

• Are more than 25% users going to use this more than once month?

  • Double the percentage of users that will use this and that’s how certain to be you need a document. I.e. 30% equals 60% certainty and 75% means 150% certainty :wink:

• How often do we (the support team) need to help a user with this?

  • If it’s more than twice a week you should probably have a document for it.

Those super loose rules aside documentation is meant to the user AND to help you have more time to focus on other tasks. Use this XKCD comic as a good metric for time savings but also remember that documentation is also to help users, not just save you time.