Can I filter based on status history?

[Bud Herz]

I have been tracking status changes for nearly a year. That's been fantastic.

• Discovered our fail metric (if moved to Blocked) seems high.
• Was not collecting a special field 'Blocking Reason' 

Can I write a JQL query that shows Story tickets that were transitioned to Blocked within a 12 month span even if they are in a different status now? 

If I can, then I can capture it and the blocking reason in the same sheet.

[Laurie Sciutti · Accepted solution]

Hi @Bud Herz ~ to elaborate on @Valerie Knapp 's answer, I believe you would want to use the WAS operator.  Have a look here for examples (including date ranges):  https://support.atlassian.com/jira-software-cloud/docs/jql-operators/#WAS 

[Laurie Sciutti]

CHANGED operator may also be useful for this use case:  https://support.atlassian.com/jira-software-cloud/docs/jql-operators/#CHANGED 

[Valerie Knapp]

Thanks for the assist @Laurie Sciutti ! :)

[Bud Herz]

@Valerie Knapp  and @Laurie Sciutti  - Thank you so much! I got it to work with the WAS operator and the Changed will come in handy later too. 

These will be very helpful to track when our tickets entered certain status going forward. 

[Bud Herz]

I'm guessing I can use CHANGED to find when the tickets moved to Blocked? 
I'll research that.

[Valerie Knapp · Accepted solution]

Hi @Bud Herz , thanks for your query. 

It is possible to query the status history using the WAS operator.

Please check out the documentation here https://support.atlassian.com/jira-software-cloud/docs/jql-operators/ 

Hope this helps you. 

Cheers

[Yuliia_Borivets__SaaSJet_]

Hi @Bud Herz 

You can try something like this:

project ="YourProject" AND issuetype = Story AND status CHANGED TO Blocked DURING ("-12M", "now")

Here, you might check some other cases: Status transition history for multiple Jira issues

[Hannes Obweger - JXL for Jira]

Hi @Bud Herz

I trust that you'll get a fair bit closer using the CHANGED operator as previously suggested.

For more advanced use cases, you may want to have a look at the Atlassian Marketplace app that my team and I are working on, JXL for Jira.

JXL is a full-fledged spreadsheet/table view for your issues that allows viewing, inline-editing, sorting, and filtering by all your issue fields, much like you’d do in e.g. Excel or Google Sheets. It also comes with a long list of so-called history columns that aren’t natively available, including date of [first/last] status change from [status] to [status], and many many more.

This is how it looks in action:

As you can see above, you can easily sort and filter by your history columns, and also use them across JXL's advanced features, such as support for (configurable) issue hierarchies, issue grouping by any issue field(s), sum-ups, or conditional formatting.

Any questions just let me know,

Best,

Hannes

[Petru Simion _Simitech Ltd__]

If you are open to using apps, you can use CIFj - Issue History Dashboard for Jira, an app recently released by our company

https://marketplace.atlassian.com/apps/1235591/cifj-issue-history-dashboard?hosting=cloud&tab=overview

You can filter by Field Updated = Status and Change Date from and to as you need.

If you need further drill down you can export to CSV.