Becoming a JIRA Search Ninja - History searches in JIRA

[Maarten Cautreels]

@Steve Korecki Unfortunately you can't exactly get history date out. However you can create a query to get all issues (and their current state) out based on a status they had in the future. For example, the following query will give you all issues that were in the status "In Progress" on the 15th of March:

status WAS "In Progress" ON "2018-03-15"

[Trudy Claspill]

Do you know of a way to search History for Link activity?

I want to search the history of issues to find ones where Issue Links have been changed (added or removed). 

I want to help users monitor when the link between their issue and any other issue is added or removed. There doesn't seem to be a notification option for this.

We do have ScriptRunner. I don't see a built-in JQL function for this. I'm considering building my own custom function.

Thanks!

[Lawrence]

Is there a way to have the search save and automatically updated?  For example, I have a team of 7 people, I would like to have a search output that to show how many tickets in each of member's queue and sorted the output alphabetically or in the increasing order of number of the tickets.

[Alexander Zlatkus]

I have a long query (seen below) that pulled, e.g. 500 issues 2 months ago. I pull the same query now and get e.g. 450 issues (50 less). 

resolved >= 2017-01-01 AND resolved <= 2017-12-31 OR created >= 2017-01-01 AND created <= 2017-12-31 OR status changed after 2017-01-01 before 2017-12-31

I am wondering: 
1. why would why query result in 50 less issues? Maybe 50 issues were reopened?
2. How can I pull the exact 500 issues I pulled two months ago? I tried the ON function (seen below) but it did not give me the exact number it was before..

resolved >= 2017-01-01 AND resolved <= 2017-12-31 OR created >= 2017-01-01 AND created <= 2017-12-31 OR status changed after 2017-01-01 before 2017-12-31 ON 2018-05-21

Any help would be much appreciated!!

[Mani]

Is it possible How could I filter the issues that, for example overpass the SLA completion duration.
E.g list the issues for which the duration between Done date and creation date is above 10 days?

[Mani]

@Maarten Cautreels,

How could I filter the issues that, for example overpass the SLA completion duration.
E.g list the issues for which the duration between Done date and creation date is above 10 days?

[Maarten Cautreels]

@Trudy Claspill - Sorry for the late response, I must have missed the notifications/emails for this topics.

Unfortunately not all fields support history searches and the Link field is one of them. I'm not a Script Runner expert but as far as I know, it can't add history searches for a particular field.

What you could do however is created a custom field that you update using Script Runner each time the Links change. 

Hope this helps.

Best,

Maarten

[Maarten Cautreels]

Hi @Alexander Zlatkus,

I think you are right. The 50 issues that are missing from the query have most likely been reopened in the mean time. Which changes their "resolved" time and thus makes them not come out of the query. Depending on what the idea behind the filter was the query solution might be different. So what was the idea behind the original filter? Did you want to return all issues that were resolved within 2017?

Best,

Maarten

[Maarten Cautreels]

Hi @Mani,

There are several options for filtering SLA's. You can find all details on the Knowledge Base of Jira Service Desk.

In your case the following filter should give you the desired result:

"Time to resolution" >= elapsed("10h")  

[Varma Gadiraju]

Great article @Maarten Cautreels. Thanks

[Varma Gadiraju]

Hello Maarten,

is there a way to skip one status cycle time, that means in my workflow we have four status(Open, Review, In progress and Close). default ticket cycle time will calculate duration from open to close. But I want to skip Review status cycle time and calculate remaining status(Open+In progress + Close) cycle time. is it possible ? 

[Michael Rodriguez]

new to JIRA JQL

looking to search issue history by current user for the last few months

project = XXX, YYY AND issue in issueHistory() AND updated = currentLogin() ORDER BY updatedDate DESC

Does not work.  Ideas?

Atlassian JIRA Project Management Software (v7.10.1#710002-sha1:6efc396) 

[Aleksandro Silva]

Which column do I need to add to view which date the change happen? 

[Helle Overbeck]

@Aleksandro Silva If you add the column 'Updated' you will see the date of the latest update of the issue

[Aleksandro Silva]

@Helle Overbeck, yes but, this works only if my querie was to one stage back, if the ticket was updated 2 times I will not have a correct date.

[Helle Overbeck]

@Aleksandro Silva I don't think you can display the date of a specific change - if it is not the last one. But if it is possible I'd like to know how.

[D. Egerland]

@Maarten Cautreels thank you for the article! I've been trying to use scrumban, and actually got to use your tool on Google Docs for my teams.

However if I apply the query (similar to your suggestion)

project = PD AND Status changed to "Partner QA" during ("2018/10/24", "2018/11/06")  AND labels = CDMS AND type in (bug, improvement, spike, Sub-task, "Technical Task", Task, Story) ORDER BY "Story Points", rank

I noticed that the same Story appears in 2 different ranges. So the same Story appears in the same status for 2 sprints but really it was changed to Partner QA only once. How come I keep seeing the Story in a future date range if the status was not changed but kept?

What am I doing wrong?

[Maarten Cautreels]

Hi @D. Egerland

It's hard to debug from a distance but can you try and change the filter to the following and try again:

project = PD AND Status changed to "Partner QA" AFTER "2018/10/24" AND Status changed to "Partner QA" AFTER "2018/11/06"  AND labels = CDMS AND type in (bug, improvement, spike, Sub-task, "Technical Task", Task, Story) ORDER BY "Story Points", rank

 I've did a couple of tests with "DURING" as well and it did give the desired result on my instance. Can you let me know if you're running Cloud or Server? And if Server, which Jira version you are on?

Thanks,

Maarten

[Joshua Kustin]

@Maarten Cautreels I notice this says for searching in Issues, can these query tips be applied to object searches?

Thanks!

[Rishabh Srivastava]

Hi, any idea on how to find out the date when an issue was moved from "In Progress" to "Done" state? 

For instance, 

on June 11, 2018, ticket moved from In progress to "ready for test"

On Jun 20, 2018, ticket moved from "ready for test" to "done"

On Jun 22, 2018, ticket moved from "done" to "resolved"

Last updated date is going to be june 22, 2018 but I am interested to know when ticket moved to done state i.e. i need to find out june 20, 2018 using jql. 

[Maarten Cautreels]

Hi@Rishabh Srivastava

Do I understand you correct when I say you would like to find all issues who moved from Ready for Test to Done on June 20 2018? If yes than this would be your query:

status CHANGED FROM "ready for test" TO "done" ON "2018-06-22"

Hope this helps.

Best,

Maarten

[Desmond van de Woestijne]

Hi, liked your article.

I wonder if there is any possibility to search for historical values in Insight objects?

"Which laptop was owned by user x"

[Hung Nguyen]

The link @Maarten Cautreels specified above seemed to changed to

https://confluence.atlassian.com/jiracoreserver/advanced-searching-fields-reference-939937719.html

[Maarten Cautreels]

Thank you very much @Hung Nguyen for the feedback. I've adapted the article.

[Deleted user]

Hi, can I get a report about the Due time while going from one state to another state for each issues from 2018 to 2019?