Help with JQL. Results not as expected.

Paul Stansberry December 12, 2019

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? 

2 answers

1 accepted

2 votes
Answer accepted
edwin vasquez
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
December 12, 2019

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
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
December 13, 2019

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

Like edwin vasquez likes this
edwin vasquez
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
December 13, 2019

Awesome! Thanks for the feedback. 🙂

Like Ignacio Pulgar likes this
Paul Stansberry December 13, 2019

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:

sshot-1.png

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
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
December 13, 2019

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 December 13, 2019

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
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
December 13, 2019
  • What project is this issue in?
  • What epic link is it linked to?
  • is it flagged?
Like Ignacio Pulgar likes this
Paul Stansberry December 16, 2019

@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
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
December 16, 2019

The only thing I can think of may be indexing.

Like Paul Stansberry likes this
Paul Stansberry December 16, 2019

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
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
December 16, 2019

Haha, no worries. Glad I can help!

0 votes
edwin vasquez
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
December 13, 2019

Paul,

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

Paul Stansberry December 16, 2019

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

Suggest an answer

Log in or Sign up to answer