JQL query to identify all issues active on a given date

[Rachel Gornall]

I wish to see a count of issues that were active on the 1st of each month through last year. 

Active, as in not in a 'resolved' or 'transferred/rejected' status at that date or after it.

I'm struggling to create a suitable JQL query. If someone could give me a hand I'd really appreciate it! Thanks

[John Funk · Accepted solution]

Hi Rachel, 

Try this: project = ABC and status was in (Investigation, "Development Required", "Awaiting GA Release) on 2024-01-01

This will show the list of issues that were in one of those statuses on January 1. 

[Trudy Claspill · Accepted solution]

Hello @Rachel Gornall 

Can you provide some example data to illustrate would should and should not be included in the results?

If an issue was active on Jan. 1 and still met the criteria on Feb. 1 should it be counted in both months?

Does it matter when the issue was created?

Are Resolved and "Transferred/Rejected" actual Status values or Resolution values?

If an issue transitions to Resolved and "Transferred/Rejected" after the first of the month but then transitioned out of those statuses, should it be considered active?

[Rachel Gornall]

Hi @Trudy Claspill , thank you for your help.

-If an issue was active on Jan. 1 and still met the criteria on Feb. 1 should it be counted in both months? Yes

-Does it matter when the issue was created?  No

-Are Resolved and "Transferred/Rejected" actual Status values or Resolution values? Resolution values

-If an issue transitions to Resolved and "Transferred/Rejected" after the first of the month but then transitioned out of those statuses, should it be considered active? Yes

We use a project to track risks rather than to develop software. So our use case is slightly different to the norm. The data consists of Issues (risks) which follow this workflow:

So the aim is to identify any trend in active risks.

[Trudy Claspill]

-Are Resolved and "Transferred/Rejected" actual Status values or Resolution values? Resolution values

As per your diagram those are Status.

Jira does not have a native reporting option that would enable you to run one query and get the information parsed out for each of the past months. 

The suggestion made by @John Funk could be replicated for each month, updating the date given in the query.

If you wanted a more dynamic option to give you that information on a monthly basis for the past X months, then you could create an Automation rule that would run on a schedule.

In it you could run X number of Lookup Issues actions using the same JQL suggested by John, but instead of using a fixed date you could use the startOfMonth() function

project = ABC and status was in (Investigation, "Development Required", "Awaiting GA Release") on startOfMonth()

project = ABC and status was in (Investigation, "Development Required", "Awaiting GA Release") on startOfMonth(-1)

project = ABC and status was in (Investigation, "Development Required", "Awaiting GA Release") on startOfMonth(-2)

...

project = ABC and status was in (Investigation, "Development Required", "Awaiting GA Release") on startOfMonth(-X)

After each Lookup Issues action you could create a variable to hold the count of issues found.

The final action in the rule would be to send that information in an email.

Example of creating a variable and sending the email.

There may be a more elegant way to do this. Automation doesn't have an explicit function to create a loop to say "do these same actions X number of times". But in this other post there is some discussion of ways to accomplish that.

https://community.atlassian.com/t5/Automation-articles/Branching-over-smart-values-in-Jira-Automation/ba-p/1741935#M546

[Trudy Claspill]

I just realized though that John's suggested JQL misses part of your requirement - 

Active, as in not in a 'resolved' or 'transferred/rejected' status at that date or after it.

To adjust for that, use this query.

project = ABC and status was in (Investigation, "Development Required", "Awaiting GA Release") on startOfMonth(-X) and status not in (Resolved, "Transferred/Rejected")

If the issue is in one of those last Statuses now, then clearly it was moved to that status sometime after the first of the specified month.

[Bill Sheboy]

Hi @Rachel Gornall 

Adding to the suggestions offered, you may want to look at some of the free training from Atlassian on using JQL.  It may help create such queries in the future.  Here is a great example:

https://university.atlassian.com/student/path/849533-gain-project-insights-through-jql

Kind regards,
Bill

[Rachel Gornall]

You took my question and gave me more than I'd asked for but exactly what I needed! Thank you I'm very grateful.