Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

Next challenges

Recent achievements

  • Global
  • Personal

Recognition

  • Give kudos
  • Received
  • Given

Leaderboard

  • Global

Trophy case

Kudos (beta program)

Kudos logo

You've been invited into the Kudos (beta program) private group. Chat with others in the program, or give feedback to Atlassian.

View group

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

JQL query for Breached tickets

We are exporting every month breached tickets count and ticket details by this way 

project = JIRA AND issuetype in ("Incident Request", "Service Request") AND status = Closed AND "Time to resolution" = breached( )AND created > startOfMonth()

There is the chance to miss some of the ticket count 

For example if the ticket is created last month( January) but the ticket is breached on this month ( February )

 

We need JQL query to extract the breached tickets count.

A ticket can be created whenever(Dec, Nov, Oct), but if it breached on this month (January).

 

Thanks in advance

2 answers

1 accepted

0 votes
Answer accepted
Jack Community Leader Jan 21, 2021

If you are wanting to find all issues that breached in the current month regardless of creation date maybe this...

project = JIRA AND issuetype in ("Incident Request", "Service Request") AND statuscategory = done AND "Time to resolution" = breached( ) AND resolutiondate >= start of month

now, depending on when you run this you might want something different. For example, if you want a monthly report I would set up a subscription to run monthly at the first of the new month and reference the previous month....

project = JIRA AND issuetype in ("Incident Request", "Service Request") AND statuscategory = done AND "Time to resolution" = breached( ) AND resolutiondate >= startofmonth(-1) and resolutiondate <=endofmonth(-1)

i think i have the syntax right or maybe close.

I'm watching this one cause it seems like an interesting use case.

Wouldn't that JQL only return the issues that are resolved already? Normally SLA's are that short so they could already breach without being solved.. especially if they are breached it is possible they are still open when running the report.

e.g. 

Created on 25/12/2020 , SLA of 2 days, still open in January and thus breached() but the breachdate was 27/12/2020 

Also it could be that the Resolution was in January but the "breachdate" in December still..

My first comment would be a non-issue if you only want to report on the already fully resolved/closed issues but if you only want the breached issues no matter what the state they are in the resolutiondate would miss some too (or could be in a different month)

Jack Community Leader Jan 21, 2021

yes very good point. I was assuming that since the current JQL is looking at closed issues that was part of the requirement. If you want to find issues that were not breached when entering the month but did breach when exiting the month that gets a bit hairier. I need to ponder that as I think you might need to use say the elapsed("") function or one of the other SLA functions. 

Dirk Ronsmans Community Leader Jan 21, 2021

I was looking at it myself and I fear we need to find a way to find that breachedDate field.. which seems to be available through rest api but not through a JQL filter. 

Otherwise I think the only way might be to set up some kind of automation that writes the date to a custom field at the moment of breach and that would make it a lot easier.

Drawback there is that you need a field for each SLA.

Hi @Thangavel VELUCHAMY ,

Time to SLA  has dedicated search functions, gadgets  and reporting features for this.

You can perform the search with slaFunction = isBreached() and order the results by SLA end date with order by clause.

project = TIS and slaFunction = isBreached() ORDER BY slaEndDate

Or TTS search functions like this: slaFunction >= slaEndDate("-30d") AND slaFunction = isBreached() (You can pass SLA ids and names as parameters)

Screen Shot 2021-01-21 at 22.40.54.png

You can use the reporting functions to extract the SLAs that have ended this month and has the breached status like below.

You can also save the report configuration for later use.

Screen Shot 2021-01-21 at 22.24.37.png

 

Or you can use the TTS gadgets to investigate the trends. The best part is - these gadget are interactive, when you click on them, it'll take you to the issue navigator and show you the related issues.

Screen+Shot+2020-05-14+at+15.29.16.png

Please let me know if you have further questions.

Cheers,

Gökçe

Please note that I'm one of the members of the Snapbytes team.

Thanks for your help.

 

How to arrange the ticket orderby slaEndDate?

slaEndDate is this custom filed ?

 

Could you please match your answer with my JQL query

We are using to find the below query 

project = JIRA AND issuetype in ("Incident Request", "Service Request")  AND "Time to resolution" = breached( )AND created > startOfMonth()

project = JIRA AND issuetype in ("Incident Request", "Service Request")  AND "Time to resolution" = isBreached() ORDER BY Time to resolutionEndDate

 

Correct me if i'm worng?

 

Regards;

@Thangavel VELUCHAMY ,

You can refer to the documentation for details: https://confluence.snapbytes.com/time-to-sla/sla-search/ordering-issues-by-sla

SLA End Date is a TTS-SLA attribute that you can use in your ORDER BY clauses. TTS also uses slaFunction keyword for searched.

Therefore the JQLs should look like this:

project = JIRA AND issuetype in ("Incident Request", "Service Request")  AND slaFunction = isBreached("Time to Resolution") AND created > startOfMonth() ORDER BY slaEndDate

project = JIRA AND issuetype in ("Incident Request", "Service Request")  AND slaFunction = isBreached("Time to Resolution") ORDER BY slaEndDate

Of course, I assuming that Time to SLA is installed and you have defined the Time to Resolution SLA, regenerated the SLA data for existing issues before these JQLs can turn a result.

Please let me know if you have further questions.

Cheers,

Gökçe

Suggest an answer

Log in or Sign up to answer
TAGS

Community Events

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

Events near you