JQL query for Breached tickets

[Thangavel VELUCHAMY]

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

[Gökçe Gürsel {Appfire} · Accepted solution]

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)

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.

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.

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.

[Thangavel VELUCHAMY]

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;

[Gökçe Gürsel {Appfire}]

@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

[Ask Høgaas Himberg]

Hey  @Gökçe Gürsel {Appfire} .

Is it possible to get a report exported to a table to show how many breach of SLA there was per week the last 4 weeks for example? And have different filters for each row in the table?

Br

Ask

[Jack Brickey · Accepted solution]

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.

[Dirk Ronsmans]

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 Brickey]

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]

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.