Becoming a JIRA Search Ninja - History searches in JIRA

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

History searches in JIRA

Wouldn't it be cool if you could go back in time sometimes?! In JIRA you kinda can go back. You could find all issues that were in a certain state at a certain point in time or find all issues that were updated by a certain user between two dates.

Date notations in JQL

In JQL you can refer to a specific point in time in several ways. 

Static Date

The first one is the most simple form, you can simply point to a static date. Fe.:

project = Collaboration AND created >= "2017-03-16"

And you can also add a time part to the query

project = Collaboration AND created >= "2017-03-16 10:41"

Dynamic Date

If you want to reuse the query you're writing, you'll usually want the date to be dynamic in a sense that it will dynamically change based on the current time. Let's try to return all issues that have been created in the past 2 weeks:

project = Collaboration AND created >= "-2w"

Or all issues that are due in the next 4 hours:

project = ISD AND due <= "4h"

You can combine weeks, days, hours and minutes. For example:

  • "-4w 2d 3h 2m"
  • "-4w 3h 2m"

There are even more methods of using a dynamic date and compare your fields with for example the start of the month etc. This will be covered in the last post of this series: The secret powers of JQL

Querying the history

Now that we now how to specify a date in JQL we can start using our time machine and try to go back in the history of our JIRA issues.

!These history queries can be pretty slow from time to time depending on what you are searching for. That's just the way these queries work and unfortunately we won't be able to speed them up too much.!

Querying the state of a field at a certain point in time

Let's imagine that we want to know which issues were "In Progress" at a certain moment:

project = PPLUS AND status WAS "In Progress" ON "2017-01-01"

This query will return all issues in the PPLUS project that had their status set to "In Progress" 1st of January 2017. 

Other keywords that you can use include:

BEFORE

project = PPLUS AND status WAS "In Progress" BEFORE "2017-01-01"

AFTER

project = PPLUS AND status WAS "In Progress" AFTER "2017-01-01"

DURING

project = PPLUS AND status WAS "In Progress" DURING ("2017-01-01", "2017-01-31")

Filter on both historical and previous value

Let's imagine you would like to find all issues that have been changed from "In Progress" to "Awaiting Code Review".

project = PPLUS AND status CHANGED FROM "In Progress" TO "Awaiting Code Review"

This will give you all issues that had the status "In Progress" which was changed to "Awaiting Code Review" at any time. But what if you would like to see the same results but only for those issues that had this change (In Progress → Awaiting Code Review) taking place on a particular date:

project = PPLUS AND status CHANGED FROM "In Progress" TO "Awaiting Code Review" ON "2017-03-15"

Be your colleague's Big Brother

Apart from filtering on the value of a field on a certain point in time you can also filter all issues that have been changed by a certain user. Let's imagine you want to know which tickets where put "In Progress" by yourself:

project = PPLUS AND status WAS "In Progress" BY "mcautreels"

Combining (allthethings)

Combining all the previously discussed items we can build pretty complex queries to find what we are looking for. For example:

project = PPLUS AND status CHANGED FROM "In Progress" TO "Awaiting Code Review" AFTER "-2w" BY "fwillems"

Find all issues that have been transitioned from In Progress to Awaiting Code Review in the past 2 weeks by a particular user (Frederick Willems).

Working Scrumban?

As you can read on the internal blog some teams work Scrumban which means they don't use sprints anymore but are still visualizing their velocity. A filter I find very helpful myself is the following:

project = "P+" AND Status changed from ("Ready 4 Dev", "In Progress", "Awaiting Code Review", "In Code Review") to (Done) during ("2017/02/09 12:00", "2017/02/23 12:00")  AND resolution not in ("Won't Do", Duplicate, "Won't Fix") AND type in (story, bug, improvement, spike) ORDER BY "Story Points", rank

This filter gives me all features and bugs that have been delivered in a certain amount of time (usually the previous sprint).

Questions

Please checkout this page from Atlassian first: https://confluence.atlassian.com/jiracoreserver/advanced-searching-fields-reference-939937719.html

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

 

 

 

76 comments

Sam Hall
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.
April 13, 2017

I really like this article, Maarten. Thanks for taking the time to put it together. Loads of useful info here, so I'll start answering some questions by linking here : )

One thing you could add is how to search for issues created on a specific date.

A common thing is to try do a search like:

created = "2017-04-07"

But, since 'created' is a time stamp, this won't find the issues expected. 

Instead you need to do:

created >= "2017-04-07" AND created < "2017-04-08"

or even:

created >= "2017-04-07 00:00" AND created < "2017-04-08 00:00" 

 

Abhay Patil February 27, 2018

I find most of the interesting fields are not supported by "history". E.g. when i attempt "project changed from ...", I get the  message: 

  • History searches do not support the 'project' field.

I could not find any documentation as in which are the fields supported by history. Is there one?

Like # people like this
Maarten Cautreels
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.
February 28, 2018

@Abhay Patil you can see which operators (like WAS, etc) are supported for each of the fields: https://confluence.atlassian.com/jiracoreserver073/advanced-searching-fields-reference-861257219.html 

Abhay Patil February 28, 2018
Lindsay Czopur
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 16, 2018

Is there any way to create a JQL for issues whose status has changed within a 5 minute period?  We have JSD tickets that are actually phone calls that are created and closed within 5 minutes that we want to note in reports (or rather exclude them from actual issues logged through our portal).  Thanks in advance!

Maarten Cautreels
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 24, 2018

Hi @Lindsay Czopur,

Unfortunately comparing dates in a filter (created vs resolved) is not possible but it should be possible using ScriptRunner: https://scriptrunner.adaptavist.com/4.3.1/jira/jql-functions.html#_datecompare 

Hope this helps.

Best,

Maarten

Helle Overbeck March 28, 2018

Great article - opens up some more possibilities.

Can you tell me - is it possible to search for issues that was changed to a certain status more than two weeks ago - and has been in the same status ever since?

So far I have only been able to also find those that move back and forth between statuses and I am not interested in those

Maarten Cautreels
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 28, 2018

Hi @Helle Overbeck

That's certainly possible. If I understood you well this query should work:

status CHANGED TO "In Progress" BEFORE -2w AND status = "In Progress"

It'll give you all issues that had their status changed to "In Progress" over 2 weeks ago and their status is still "In Progress". If you want to do this for all statuses in your workflow you'll have to chain multiple queries with OR in between like:

(status CHANGED TO "In Progress" BEFORE -2w AND status = "In Progress") OR (status CHANGED TO "On Hold" BEFORE -2w AND status = "On Hold")

Hope this helps.

Best,

Maarten

Helle Overbeck March 28, 2018

Thanks for trying - but I already tried that. 

We have a status "Waiting for customer" - I want to find all those issues that have been waiting for the customer contiguously for more than 2 weeks.

Using your suggestion I get all those issues that moved to "Waiting for customer" 2 weeks ago - including those that just waited at few days, then moved to "In Progress" and back to "Waiting for customer" yesterday.

Is that possible?

Walter Buggenhout
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, 2018

Hi @Helle Overbeck,

Just based on status history, not really. But for your specific use case, you could apply the following workaround:

status = "Waiting for Customer" and Updated < -2w

"Updated" covers much more than just a status change. It includes comments being added, updates to fields, status changes and so on. But it probably covers what you want to do: get a list of issues that have been reassigned to the customer for feedback and have been untouched since.

Helle Overbeck April 2, 2018

Hi Walter

Yes, that is a good idea - I think that could answer my question - especially since any update (from the customer) will change the status automatically.

Thank you

Dave V April 10, 2018

Greetings, I hoping someone could guide me to edit a JQL on a Jira Kanban where the items that are marked as Done are removed from view after 5 weeks. I played with the date entry as listed above

(>= "-5w") without success.


My JQL is

project in (ITEL) AND labels in (Seals) AND status not in (Withdrawn) ORDER BY "Rank 1"

Thanks in advance.

Walter Buggenhout
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
April 10, 2018

@Dave V, try the following:

Project = ITEL and labels = Seals and status != Withdrawn and (Resolution = Unresolved OR resolved >= -5w)
Dave V April 11, 2018

Thank you. It appears this did the trick.

Sony Abraham TP April 16, 2018

Hi Marteen, All

I have a 'Select List (single choice)' Custom Field for denoting a specific phase.

Every Monday, I want to get a list of issues which were under a particular phase as on the previous Saturday

Eg :

Issue ABC-100 

On Thu : Phase 3

On Fri   : Phase 2

On Sun : Phase 5

I need to run a report of all issues which were in Phase 2 as on Saturday

So in this report I expect to see ABC-100 (if possible with all attributes showing values as on last Saturday)

Any idea how this can be achieved?

Thanks

Sony

Jatin Garg May 8, 2018

Great article @Maarten Cautreels. Really helpful for a lot of use cases. Could you please help me with a query for all the tickets that have not been updated for last 3 hours.

Helle Overbeck May 8, 2018

@Jatin Garg I believe it is as simple as 

updated < -3h

Maybe you need to add something to exclude all finished business - ie

resolution is empty and updated < -3h

Jatin Garg May 8, 2018

@Helle Overbeck I want to filter only on the basis of comment i.e. I want a ticket for which no comment has been added for last 3 hours

Helle Overbeck May 8, 2018

@Jatin Garg

OK - I see. 

I don't know if you can do it with plain JQL, but if you have Scriptrunner installed you could do this:

issueFunction in lastComment("before -3h")

Jatin Garg May 9, 2018

Hi @Helle Overbeck,

 

What if I use commentLastCreatedOnDate< -3h supported by JQL. I think this should give the same result as the one you mentioned above.

Helle Overbeck May 9, 2018

@Jatin Garg

I'm sorry - but I don't know that function - and it doesn't work in my environment. 

Maybe someone else can help?

John Funk
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
May 9, 2018

@Maarten Cautreels - anyway to do a query to see what issues changed from a particular issue type to another type on a particular day?

Maarten Cautreels
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.
May 10, 2018

@John Palmieri unfortunately the (issue)type field does not support history searches :-(

You can vote and watch this suggestion: JRASERVER-66741

There is a plugin that supports it: JQL Tricks Plugin. I haven't used it in real life but I did do a brief test on my test Jira:

issue in movedIssues("issuetype", "Bug", "Story")

This will return all issues that have been changed from Bug to Story but unfortunately not on a specific date.

John Funk
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
May 10, 2018

Thanks @Maarten Cautreels - I voted for the link above. Unfortunately, the JQL Tricks Plugin is not available for Cloud, which is what we use.  :-(

Steve Korecki May 17, 2018

Is it possible to query the status of an issue on a specific date?

Comment

Log in or Sign up to comment
TAGS
AUG Leaders

Atlassian Community Events