Becoming a JIRA Search Ninja - History searches in JIRA

[#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

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.

[Sam Hall]

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]

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?

[Maarten Cautreels]

@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]

Thanks @Maarten Cautreels!

[Lindsay Czopur]

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]

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]

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]

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]

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]

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]

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]

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]

@Dave V, try the following:

Project = ITEL and labels = Seals and status != Withdrawn and (Resolution = Unresolved OR resolved >= -5w)

[Dave V]

Thank you. It appears this did the trick.

[Sony Abraham TP]

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]

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]

@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]

@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]

@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]

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]

@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]

@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]

@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]

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]

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