How take a report of ticket not updated by the assignee for the last 24 hour or 48 hours using JQL ?

[Vijaysundarraj Balasundaram]

Need help in query for generating report of ticket notes not updated by the asignee's.

[Mark Segall]

Hi @Vijaysundarraj Balasundaram and welcome to the community!

You won't be able to do this natively.  There is a system field for "Updated", but this is indiscriminate of who made the update or what type of update was made.

Here's a possible workaround:

• Create a custom date field that you'll use to capture the date of last assignee update
• Create an automation rule that time stamps the field created above every time an assignee updates the issue:
  • TRIGGER: Issue Commented
  • CONDITION (Advanced):
    • {{comment.author}}
    • Equals
    • {{issue.assignee}}
  • ACTION: Edit Issue (your new field)
    • {{now}}

You'll then be able to execute desired JQL against the new field

[mauricio.groth]

Hi @Vijaysundarraj Balasundaram 

As you're on Jira Cloud, the correct answer is to get an app that provides JQL extensions you're looking for.

With standard JQL, you can only get a list of issues and export them to Excel for further processing. This works if you want to do a one-off analysis. If your use case is more dynamic than that, look beyond standard Jira.

Standard JQL doesn't easily allow it, but you can quickly find the results using our professional indexing service JQL Search Extensions

You can use this query to find all your issues that the assignee is UserX and this user updated something in the issue between the 01/10/22 and 21/10/22

updatedBy = UserX and assignee = UserX and updatedOnDates>="2022/10/01" AND updatedOnDates<"2022/10/21" 

So you run this query and then save it as a filter in our app.

After this, you can run it using a != in the filter, like: 

Filter != MyFilter

Check out the documentation for more examples.

If you have any other questions, please contact our support. We’ll be happy to help you!
Best regards,
Maurício

[Bill Sheboy]

Hi @Vijaysundarraj Balasundaram -- Welcome to the Atlassian Community!

Perhaps a different approach than a report/notification would be a dashboard with issues not updated in the last 24h, with a filter gadget on assignee.

You could do your original request with an automation rule, however it would be limited to 100 issues tested and would be quite "noisy" and slow...using the updatedBy() function.

If you still want to try your original idea...

• trigger: manual or scheduled
• action: lookup issues with JQL on issues not updated in the last 24h: 
  • project = myProjectName AND updated < -24h AND assignee IS NOT EMPTY
• advanced branch on the distinct assignees from the lookup with
  • smart value: {{lookupIssues.assignee.distinct}}
  • variable name: varAssignee
    • action Lookup Issues with JQL to find any issues NOT updatedBy() the assignee: 
      • project = myProjectName AND issue NOT IN ("{{varAssignee.displayName}}", -24h) AND assignee = {{varAssignee.accountId}}
    • advanced compare condition to check if there are any issues...
      • first value: {{lookupIssues.size|0}}
      • condition: greater than
      • second value:0
    • action: send yourself an email for this assignee's issues from the lookup

Kind regards,
Bill