Tickets in the past 30 days that has not been updated within 24 hours

[Daniel Blomqvist]

Hi,

I'm looking for a JQL search query that helps me filter out tickets that have been created in the past 30 days and where more than 24 hours have passed from creation time/date to the first update.

I was also looking to do this with an advance compare automation to add a label if any ticket fits these conditions.

Thanks in advance.

[Asha]

Hello @Daniel Blomqvist

Regarding the JQL query, you can use something like this:

created >= -30d AND updated< -1d  to filter out tickets created in the past 30 days and last updated more than 24 hours ago as an advance compare condition in automation.

Hope that helps.

Thanks

[Daniel Blomqvist]

Hi @Asha

Thank you, but I think you misunderstood.

I want to filter out tickets that have been created within the last 30 days AND the time between the creation date & time and the first time the ticket was updated exceeds 24 hours.

We have considered using SLA, but we planned to use that differently.

[Tim Kopperud]

Hi @Daniel Blomqvist 

I don't think you can do that. "Updated within" (or something) is not an available JQL function. You might solve it by using third party scripting tool though. See this document for available JQL functions JQL functions | Jira Software Cloud | Atlassian Support

I might suggest you use SLA in this scenario.

If you define an SLA it will affect existing requests so you will be able to find what you are looking for. See also SLA supported functions here JQL fields | Jira Software Cloud | Atlassian Support

For your scenario you can use the function elapsed("24h") in you JQL as it "Returns issues whose SLA clock is at a certain point relative to a cycle's start event". See this JQL functions | Jira Software Cloud | Atlassian Support

TimK

[Daniel Blomqvist]

Hi @Tim Kopperud

OK thank you, I think we need to re-think our SLA to get this data then.

[Tim Kopperud]

Hi @Daniel Blomqvist 

During the day I was thinking about my answer here, and it's rare we cannot solve things in Jira.  So, I found that you can fetch the issue change log using web request. Regard this as advanced though, and you will need to play around with smart values. 

After the web request you will have access to the smart value list {{webhookResponse.body.changelog.histories}}

Remember: The dates you get from the log is in text so you need to use .toDate function before comparison in smart values.

I understand this is only a "half" answer since extracting the first entry from the issue change log is not included in this reply, neither is the filtering you ask for. 

TimK.