Master Advanced Historical Operators for JQL Search

This blog post discusses some intermediate to advanced JQL skills. Beginners should first read our introduction to Jira Query Language (JQL) here: Searching for what you need in Jira using JQL

Introduction

Jira is a powerful issue and project tracking tool that enables teams to work and collaborate together effectively and efficiently, regardless of location. It is used by organizations large and small around the world. As teams expand, take on new work, and continue to push to meet deadlines, it becomes increasingly likely that users will mistakenly edit the wrong issue, use the wrong transition, select the wrong assignee, or simply lose track of an issue.

NEW RESEARCH: LEARN HOW DECISION-MAKERS ARE PRIORITIZING DIGITAL INITIATIVES IN 2024.

Trying to find issues where you only know what a given value ‘used to be’ can be a nightmare. Luckily, we have some JQL tricks up our sleeves that allow us to step back in time before those issues get away from us.

Searching through history with JQL

In Jira, there is a list of historic fields that have a special functionality – you can use JQL queries to return sets of issues based on the historic value of those fields at a specified date, as opposed to what the value is currently. This lesser known functionality is incredibly powerful and enables you to run queries that turn back the clock and provide snapshots of where your team, project, or issue was at a certain point in the past.

These historic fields store high-level information about the history of each issue and can be quite useful when trying to answer questions like,

• “What happened to that issue that was in the backlog? We talked about it during standup on Monday?!”
• “We have an issue that we worked on in version 3.5, but it’s not listed in the report. Is it possible someone edited the versions?”
• “Someone did a bulk update last month and transitioned a group of issues that should not have been included. Is there any way to figure out which issues have been affected?”
• “I had an issue assigned to me in sprint planning, but now I can’t find it anywhere!”

Historic Fields in Jira

• Assignee
• Fix Version
• Priority
• Reporter
• Resolution
• Status*

(*) – When accessing historical data, users can only refer to Statuses by name and not the underlying ID.

Jira tracks the value changes (and metadata of the changes) for these historic fields over time and makes that metadata available for users to query.

Tracking historic values in a Specific Issue

In Jira, every time a change (or update) to an issue occurs, a record of the change is recorded in that issue’s change history. As a result, Jira has a timeline of events for each issue. You can view the list of change records for an issue by selecting the History tab from within the issue view.

Historic Operators

To access the historical data for the fields above, Jira has provided a list of special operators that are available for use in any JQL query.

• WAS
• WAS IN
• WAS NOT
• WAS NOT IN
• CHANGED

Understanding How Historic Operators Work

These Historic Operators actually search through the entire list of change records (or change history) for each ticket in scope of the current query. If the value provided to the operator is found in any of the individual change records that meet the criteria, the issue will be returned by the query, regardless of what the value of the field is currently.

Additionally, these historic operators have a special set of predicates (or parameters) that can be used to help fine tune your query, all of which are optional. Use of the operators without the predicates (like the examples below) is acceptable, but it should be understood that it is the equivalent of asking questions that include the words “at any point in time” or “never” when referring to a possible occurrence.

status WAS "On Hold"

This query will return any/all issues that were at any point and time in the status of “On Hold“

assignee WAS NOT userA

This query will return any/all issues that were never assigned to user userA

Take caution when using these operators without predicates against large data sets, as those types of queries can take a great deal of time to process, and may impact overall Jira performance.

Examples – Search Rating – Jedi Padawan

resolution WAS Cancelled 
status WAS IN ("On Hold", "In Progress") 
fixVersion WAS NOT IN (1.0, 1.2)
priority CHANGED

Predicates (Optional Parameters) for Historic Operators

The list below shows each of the optional predicates and the expected value type. These optional predicates are available for any of the historic operators, with an exception for TO and FROM being reserved for the CHANGED operator.

(*) – Used with the CHANGED operator only. Additionally, searchers should note that the value you provide will be validated against the specific field you are using for the historic query. You will receive an error if the FROM or TO value is not valid for that specific field (i.e. Assignee CHANGED TO ‘Closed’).

You can write a query using any one of these predicates or you can combine many of them together to slice and dice through your query results. It’s important to note that issues must meet ALL of the predicates (or parameters) to be returned by the query, much like the use of the word AND between JQL search criteria.

Examples – Search Rating – Jedi Knight

status WAS ‘In Progress’ DURING (‘2020-01-01’, ‘2020-01-31’) 
priority WAS NOT Critical BEFORE "2000-01-01"
assignee CHANGED BY adam FROM bill TO catherine AFTER "2018-02-02"

As you can see, the predicates allow you to dial into the specific context of issues that you’d like returned by your query.

Using JQL Functions to Make Historic Searches Dynamic

You may have noticed that we are using static date values in all of the queries above. These examples are perfectly suited to answer one-time questions (or in a moment of panic), but the opportunity to re-use that query is fairly limited.

JQL has a number of functions that allow you to set date values in your queries dynamically.

Dynamic Dates

• startOfDay()
• startOfWeek()
• startOfMonth()
• startOfYear()
• endOfDay()
• endOfWeek()
• endOfMonth()
• endOfYear()
• now()
• currentLogin()
• lastLogin()

It’s important to note that these functions can be used in many scenarios and are not limited to the historic operators discussed here.

A full list of JQL’s built-in functions and their descriptions can be found in the Atlassian Jira Documentation: Advanced Search Reference – JQL Functions

When writing JQL queries you can simply replace any of the static date values with one of these JQL functions to use a date variable. This allows you to create a single JQL filter that can power reports, dashboards, or subscriptions where the results update dynamically, depending on when the information is accessed.

Examples – Search Rating – Jedi Master

• Use case: Issues that were worked on last week, but are not yet completed
  Query:

• status WAS "In Progress" DURING (startOfWeek(-1),startOfWeek()) AND statusCategory != Done

• Use case: Issues that you escalated to another team a few days ago that may need a follow up
  Query:

• assignee CHANGED FROM currentUser() BY currentUser() BEFORE endOfDay(-3) AND Project = "Tier 2 Support" AND resolution is EMPTY

• Use case: You are using the Jira macro in Confluence to create reports and want each page to represent certain changes over the period of a month
  Query:

• (Created >= startOfMonth(-1) OR status WAS Backlog ON startOfMonth(-1)) AND resolved <= endOfMonth(-1)

• Use case: A filter subscription of all issues updated last night by a scheduled service
  Query:

• priority CHANGED TO Critical BY service_account AFTER endOfDay(-1) and assignee is EMPTY

Conclusion

These examples are just a glimpse of what you can unearth when using historic operators. Additionally, this list only represents a small subset of operators that make JQL such a powerful tool; view the full list of operators, along with some helpful tips and examples in the Atlassian Jira Documentation: Advanced Search Reference -JQL Operators.