JQL for Jira admins [Atlassian University Live webinar]

Jaime Netzer
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
March 15, 2022

AtlassianUniversityLive-and-JQL-forJira-Admins-1280x500.png

As part of an ongoing series, Atlassian University offered this free webinar to strengthen your Atlassian product skills and learn directly from Atlassian experts. In this webinar, we covered when to use JQL and how to search for the exact issues you need using advanced JQL statements.

Together we:

  • Discovered where and why JQL is used
  • Dived deep into JQL syntax and structure
  • Answered your questions, live!

Speaking of questions: we've gathered the top 10 we fielded yesterday. Drumroll, please:

 

Question

Answer

10. How to search by Sprint name or ID?

Start typing a field into the JQL query line, as shown:

jira screenshot.png

When you choose the value, it will convert to the ID

Sprint = 17

But you can put in the actual fullname, too.

Sprint = "JTSCRUM Sprint 1"

 

To find the ID of a sprint, click on the ellipsis for the sprint in the backlog view. The ID of the sprint will be in the URL displayed in the lower left-hand corner of the screen. For example sprintID = 21.

9. How to do searches for epic-story-sub-task relationships (use of Marketplace apps)

Jira offers a few ways to search for relationships between issues, including the following:

  • Find subtasks that are linked to a particular epic:
    linkedissue = epicKey-123

  • Find issues that are linked to a particular issue via a particular type of link:
    issue in linkedIssues(ABC-123,"is duplicated by")

  • Find issues and sub-tasks in the epic
    parentEpic = DEMO 123

You can also search for issues by type:

  • Find issues of a particular issue type:
    type in (Epic, Story)

  • Find issues that are subtasks (i.e. issues whose Issue Type is a subtask issue type):
    issuetype in subtaskIssueTypes()

  • Find issues that are not subtasks (i.e. issues whose Issue Type is a standard issue type, not a subtask issue type):
    issuetype in standardIssueTypes()

To do deeper searches, especially with nested queries, you should check out the many JQL-related apps in the Atlassian Marketplace.

8. How to do historical searches on data?

Jira offers 5 operators: WAS, WAS IN, WAS NOT IN, WAS NOT, CHANGED

With those operators, you can search on 6 fields: Assignee, Fix Version, Priority, Reporter, Resolution, Status.

WAS can take 5 optional predicates: AFTER, BEFORE, BY, DURING, ON.

CHANGED take 7 optional predicates: the 5 above plus FROM, TO.

Example queries are shown below:

status WAS "Resolved" BY jsmith DURING ("2010/01/01","2011/01/01")

priority CHANGED BY freddo BEFORE endOfWeek() AFTER startOfWeek()

The main difference between the two operators (WAS and CHANGED) is that WAS searches for values that were and possibly still are in the specified value. And it lets you find values that never changed; namely, the issue was initially created with the specified value and never updated after creation.

CHANGED, on the other hand, searches the change history table, so it means a value had to have been something else initially and then updated afterwards.

7. How do I find the ID of a custom field?

A Jira admin can find the ID of a custom field by clicking the Edit button for the field. The ID will be listed in the URL of the Edit Custom Field Details page for the custom field.

6. What is the difference between reporter and creator?

The reporter is the user that is listed in the Reporter field on an issue. This value can be changed, if the user has the “Modify Reporter” project permission. The creator is the user who initially created the issue, and is not necessarily the same user as the reporter. Both Creator and Reporter can be searched for via JQL and both can be added as columns on filter results.

5. How do I search for issue events using a date range?

You can enter a start and end date in a JQL and use the >, >=, <, <= operators with the date range. For example, to find all issues created between January 15, 2022 and February 15, 2022, the following JQL clauses could be used:
created >= "2022/01/15" AND created <= "2022/02/15"

4. How can I search for original estimate, time spent and remaining estimate?

To search for the original estimate of an issue, use the originalEstimate field.

To search for how much time is left on an issue, use the remainingEstimate field.

To search for how much time has been logged on an issue use the timespent field.

All three of these fields accept these operators: '=', '!=', '<', '>', '<=', '>=', '~', '!~', 'IN', 'NOT IN', 'IS' and 'IS NOT'.

To compare the original estimate to the time spent, use the workratio field.

To find issues that were overestimated (original estimate is greater than time spent), use this JQL: statusCategory = done AND workratio < 100

To find issues that were underestimated (original estimate is less than time spent), use this JQL: statusCategory = done AND workratio > 100

Adding the workratio field as a column on filter results will show the delta as a percentage.

3. What is the difference between “=”, “IN” and “IS”?

The “=” operator is used to search for issues where the value of the specified field exactly matches the specified value.
Example: assignee = jsmith

The “IN” operator is used to search for issues where the value of the specified field is one of multiple specified values.
Example: assignee IN (jsmith, bdavis, rlee)

The “IS” operator can only be used to find issues that have or do not have a value for a field. This is used with EMPTY or NULL.
Example: assignee IS EMPTY

2. How can I find issues created during the weekend?

Use this JQL to find issues created on the prior Saturday and Sunday:

created > startOfWeek(-2d) and created < endOfWeek(-5d)

1. Where can I find a list of all fields, operators, keywords and functions?

Start with this page Use advanced search with Jira Query Language (JQL) and all its linked pages, functions, fields, keywords and operators.

 

What further questions do you have for @Marie Kent and @Joanna Thurmann?

Watch the recording and ask them in the comments below!

This webinar is intended for scrum teams, power users and project administrators, and those preparing for a Jira certification exam. Topics will be relevant to Jira Cloud, Data Center, and Server.

16 comments

Comment

Log in or Sign up to comment
Alexander Bondarev
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
March 15, 2022

Sounds great! Thanks!

Like Jaime Netzer likes this
Hermes March 22, 2022

Thanks

Like Jaime Netzer likes this
Abel Batista de Oliveira March 22, 2022

Thanks for the webinar!
Very enlightening

Like Jaime Netzer likes this
lightmnd March 22, 2022

Thanks for the webinar! 🙏

Like Jaime Netzer likes this
PJ March 22, 2022

Thank you for the webinar!

Like Jaime Netzer likes this
Joe Montalbano March 22, 2022

Great content - thanks for the webinar! 

Like Jaime Netzer likes this
Sandeep Mehrotra March 22, 2022

Thanks for the Webinar.. It was enlightening!!

 

Keep up the great work Team Atlassian

Like Jaime Netzer likes this
Bill Goetz March 22, 2022

Thanks for a great session!

Like Jaime Netzer likes this
Ordejon Mateos, Ana María March 22, 2022

Great webinar, thanks!!

Like Jaime Netzer likes this
Ankit March 22, 2022

Thanks for webinar!!

Like Jaime Netzer likes this
Magdalena Zhisheva
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
March 22, 2022

Hi all, 

Thanks for the webinar!

I have one very important question for my daily work as a Jira Admin.

Let's say we have a status, for example "UAT" and it's included in JQL Saved Filter as status name not ID (project = XXX AND issuetype in (Improvement, Story) AND status in (Blocked, Done, "Ready for UAT", UAT) ORDER BY Rank ASC). What will happen if we rename the status to "UAT In Progress" . Do we need to change the JQL or it will be updated automatically?

 

Your answer on this will be highly appreciated!

Marie Kent
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
March 24, 2022

Magdalena Zhisheva,

Yes, you will need to update the filter with the new name of the status.

Also note that changing the name of a status will change the name in all workflows that use the status. So be sure to check in case this will impact other teams. 

Thanks,
Marie 

Like Magdalena Zhisheva likes this
Andre Ullmann March 23, 2022

Hi there

Where can I find the recording. Is there a link shared somewhere?

Like Stefano Vidonis likes this
PJ March 23, 2022

presentor advised link will be sent to all attendees within 24hrs, hope this helps

Andre Ullmann March 23, 2022

ah ok only to the attendees. Thx

Jaime Netzer
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
March 23, 2022

Hi @Andre Ullmann check the post above; we're now live with a recording on the page for all to see! Thanks so much for your interest.

Ordejon Mateos, Ana María March 23, 2022

Hi all,

Is there any way to access the questions and answers given during the webinar?

It would be very helpful to read them again.

Thanks.

Jaime Netzer
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
March 23, 2022

Hi @Ordejon Mateos, Ana María ! We have collected & edited the top 10 questions and answers asked from yesterday — they're now in the body of this post.

Thank you for attending!

Jaime

Taranjeet Singh
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
March 28, 2022

I missed this webinar! :-( Is it scheduled to repeat any time in the near future?

Jaime Netzer
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
March 28, 2022

Hi @Taranjeet Singh never fear! The recording is live on the University site now, linked above but also here for your convenience.

Like Taranjeet Singh likes this
Victor Ramirez April 26, 2022

Hi!,

how can i search for similar issues in a project? looking for some insights and candidates for SOP

Marie Kent
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
April 26, 2022

@Victor Ramirez You can search by what they have in common. For example, search for all the issues with a particular word in a text field. 

description ~ implementation 

or

text ~ implementation 

And of course, you can add more criteria to the JQL to narrow down or expand the search. 

Hope that helps!

Marie Kent

Like # people like this
Swetha Moorthy November 30, 2022

Hi @Jaime Netzer

Thanks for a great session, This is really good and thank you for sharing.

Like Jaime Netzer likes this
Jaime Netzer
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
December 5, 2022

Thanks so much for the feedback @Swetha Moorthy , and thank you for learning with us!

Like Swetha Moorthy likes this
TAGS
AUG Leaders

Atlassian Community Events