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:
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.
In JQL you can refer to a specific point in time in several ways.
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"
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:
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
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.!
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:
project = PPLUS AND status WAS "In Progress" BEFORE "2017-01-01"
project = PPLUS AND status WAS "In Progress" AFTER "2017-01-01"
project = PPLUS AND status WAS "In Progress" DURING ("2017-01-01", "2017-01-31")
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"
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 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).
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).
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.
Maarten CautreelsCommunity Leader
Remember that time you realized it was possible to refresh your Jira data in Google sheets with just one click? What if we told you that you can now get the latest data with no clicks at all?! Zero! ...
Connect with like-minded Atlassian users at free events near you!Find an event
Connect with like-minded Atlassian users at free events near you!
Unfortunately there are no Community Events near you at the moment.Host an event
You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events