JQL filter to remove tickets that were created and resolved in a minute or less

[Brandon Mohl]

I'm looking for help with a JQL filter that will remove any tickets that were created and resolved in under a minute to get more accurate ticket metrics.

[Ste Wright · Accepted solution]

Hi @Brandon Mohl 

This isn't possible using native JQL - which gives you two options:

1. Automation: Create the metadata to use in JQL
2. App: Find an App which extends JQL to meet your need

I've detailed both options below.

---

Option 1 - Automation

You could use Automation to populate a custom "Number" field, which could detail the amount of time between Created and Resolved.

Prerequisite:

• Create a custom "Number" field

The Automation Rule would then look like this:

• Trigger: Field Value Changed
  • Field = Resolution
  • Value = Added
• Action: Edit Issue
  • Field = <Custom Number Field>
    • Value = {{issue.created.diff(resolved).minutes}}

Result:

• You could then use JQL to find Issues which took at least 1 minute to resolve - i.e:
  

  • resolution is not EMPTY and "Custom Number Field" > 0

---

A few notes on this rule:

• The rule automatically calculates the field whenever an Issue is "closed"
• {{issue.created.diff(resolved).minutes}} is a smart value, and calculates the minutes between created and resolved (including 0)
• For the Trigger, I used Resolution to limit the number of executions - but you could use "Issue Transitioned" if you prefer
• You could run this against previously closed Issues also, using a Scheduled Trigger. You only need to run it once, then delete/deactivate it. The Trigger would be:
  
  • Trigger: Scheduled
    
    • Run Rule = Doesn't matter, you'll run it manually
    • Run a JQL search = check to TRUE
    • JQL =

      • resolution is not EMPTY

• If you can reopen Issues, I'd consider clearing this field, alongside Resolution/Resolution Date. You can do this using a Workflow Post Function (eg. Clear Field Value)

---

Option 2 - App:

You could also look into whether an App can help you, with more advanced JQL functions.

Apps which extend JQL include...

• JQL Search Extensions for Jira
• Enhanced Search
• ...or another option from the Marketplace

You could then use functions like dateCompare (this is from Enhanced Search) - for example...

issueFunction not in dateCompare("resolution is NOT EMPTY", "created +1m > resolutiondate ")

...should find you issues that were not resolved within 1 minute of creation. Other Apps could have similar functions - eg. JQL Search Extensions has DateCompare I believe.

You'll need to trial/test Apps for this approach, but there's options to do this.

---

I hope one of these options works for you!

Just as a final note, I did trial Option 1 and it works - but I didn't test Option 2, so you might need to test/tweak the JQL, depending on the App and your logic.

Ste

[Brandon Mohl]

Hi @Ste Wright 

What a wonderfully detailed response. This will work perfectly. I'm going to give Option 1 a shot first.

I sincerely appreciate the time and effort!

[Ste Wright]

No worries :)

Let us know how you go!

Ste

[Brandon Mohl]

Hi @Ste Wright 

I know it's been some time, but due to lack of access I had to request the "Custom Number Field" which was named "Time to Resolve". One of the admins on the project did create that field for me. 

As far as running this against previously closed issues, which I do want to do, I wanted to test on a small sample size of dates. I wanted to make sure these dates had at least a couple of issues that were resolved in a minute or less. For the JQL for the scheduled trigger I opted to use (and I assumed this was the correct way to do so) project = ITAS AND "Location[Dropdown]" = "LOCATION HERE" AND created >= 2022-10-04 AND created <= 2022-10-05 AND resolution is not EMPTY

Where I'm getting a bit lost is the next step, Add Component. I made the assumption I would select New Action > Edit Issue > Choose Field To Set > Time to Resolve, but then after that point is where I get tripped up. 

Forgive me, I'm still learning the wonder that is JQL.

I sincerely appreciate your help!

[Ste Wright]

Hi @Brandon Mohl 

Do you mean the Action in the Automation Rule - i.e

• Action: Edit Issue
  • Field = <Custom Number Field>
    • Value = {{issue.created.diff(resolved).minutes}}

If yes, the value would go into the box next to "Time to Resolve" - this is a smart value, which will calculate the numeric value based on the rule being run :)

Ste