Becoming a JIRA Search Ninja - History searches in JIRA Edited

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.

 

 

 

1 comment

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" 

 

Comment

Log in or Register to comment