Help with JQL. Results not as expected.

[Paul Stansberry]

Hello All - 

I'm trying to write a JQL statement that will gather stale issues across two projects we have running.  The problem is that I'm getting issues in the results that aren't stale.  I'm defining stale as an issue that has remained in it's current status for more than 5 days.

Here's what I wrote:

(project = SCOPE AND "Epic Link" in (SCOPE-1701, SCOPE-2322) OR project = GCPIT) AND STATUS in ("Development Complete", "In Progress", "ON GCP INT", Testing) AND Flagged is EMPTY AND STATUS changed before -120h ORDER BY Rank ASC

I'm getting 12 issues returned, but when I check each individual issue returned, several have transitioned within the past 5 days.  I have written this query six-ways-from-Sunday and get invalid issues in it no matter what I do.

What am I missing here? 

[edwin vasquez · Accepted solution]

Try this. Let me know if it works.

(project = SCOPE AND "Epic Link" in (SCOPE-1701, SCOPE-2322) OR project = GCPIT) AND STATUS in ("Development Complete", "In Progress", "ON GCP INT", Testing) AND Flagged is EMPTY AND NOT STATUS changed during (-120h, now()) ORDER BY Rank ASC

[Ignacio Pulgar]

Well done, edwin! This is a correct answer.

Just for mere syntax simplicity, I'd use this last part instead:

...AND NOT status changed after -5d

which is just a little bit shorter. But your answer is absolutely correct and will work perfectly.

Regards

[edwin vasquez]

Awesome! Thanks for the feedback. 🙂

[Paul Stansberry]

Hi @edwin vasquez and @Ignacio Pulgar  - Thanks for your feedback.  I think this is an improvement, and this does seem to have eliminated issues from the results that were transitioned within the past 5 days as I was looking for.

However, now I can identify issues that were transitioned MORE than 5 days ago that _should_ be included in these results, but are not.  And I'm not sure why that would be.

Here's an example:

There are more than just the above example.  I can see several cards that I expect to appear in the "Stalled Tasks" section.  I can't for the life of me figure out why these cards aren't showing in the correct section of the Kanban board.

[edwin vasquez]

if you go to search and enter that query you have, does that issue show up? I'm wondering if based on the other parts of the query, its being left out. For example, maybe Project or Epic Link. 

Can you share the other details of this issue?

[Paul Stansberry]

If I use the query you suggested in the issue navigator, the issue does NOT show up.

What are you looking for in the issue, so I can share the correct information?

[edwin vasquez]

• What project is this issue in?
• What epic link is it linked to?
• is it flagged?

[Paul Stansberry]

@edwin vasquez - Strangely, everything appears to be in the correct section this morning, even though I haven't made any change to the JQL that powers this section since last week.  As such, I will mark your answer as correct.  Thank you.

That said, to answer your questions...  Yes, the issues that weren't appearing in the correct section were:

• In either the "SCOPE" or "GCPIT" projects.
  • If in the SCOPE project, also in one of the two epic links (SCOPE-1701, SCOPE-2322)
• NOT flagged.

This is actually a swimlane query in a Kanban board.  The base query of the board is as such:

project = SCOPE AND "Epic Link" in (SCOPE-1701, SCOPE-2322) OR project = GCPIT ORDER BY Rank ASC

...and the specific swimlane query controlling this section is:

STATUS in ("Development Complete", "In Progress", "ON GCP INT", Testing) AND Flagged is EMPTY AND NOT STATUS changed after -5d

It's strange.  Is there an overnight process or something that runs in Jira to ensure that these are correct?

[edwin vasquez]

The only thing I can think of may be indexing.

[Paul Stansberry]

Ah...  That makes sense.  I bet you're right, and that when these issues were later touched the index rebuild and that's why they appear in the correct section now.  Thanks for the knock on the head.

V/R

Paul

[edwin vasquez]

Haha, no worries. Glad I can help!

[edwin vasquez]

Paul,

Did this resolve your issue? If so, please mark as answered. :-) 

[Paul Stansberry]

It did!  Thanks.  I have marked the answer as accepted.