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/jiracoreserver073 /basic-searching-861257184.html#Basicsearching-tro ...

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

 

 

 

34 comments

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" 

 

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 Community Champion Feb 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 

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 Community Champion Mar 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

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 Community Champion Mar 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

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?

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.

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 I'm New Here Apr 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.

@Dave V, try the following:

Project = ITEL and labels = Seals and status != Withdrawn and (Resolution = Unresolved OR resolved >= -5w)
Dave V I'm New Here Apr 11, 2018

Thank you. It appears this did the trick.

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

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.

@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

@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

@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")

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.

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

@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 Community Champion May 10, 2018

@JohnP 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.

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

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

Comment

Log in or Sign up to comment
Atlassian Summit 2018

Meet the community IRL

Atlassian Summit is an excellent opportunity for in-person support, training, and networking.

Learn more
Community showcase
Posted Wednesday in New to Jira

Are you planning to trial, or are currently trialling Jira Software? - We want to talk to you!

Hello! I'm Rayen, a product manager at Atlassian. My team and I are working hard to improve the trial experience for Jira Software Cloud. We are interested in   talking to 20 people planning t...

196 views 3 0
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you