/build/static/layout/Breadcrumb_cap_w.png

Filter tickets with no updates for a week

Hello, I have been trying to somehow filter tickets that has not been updated/commented in a week time but I have not been successful in it.
I used to do this just sorting tickets by its "Modified" field but since I updated to 6.4 the "Modified" field started getting updated every day at specific times even though no visible update is loged in the ticket.
Is there any way I can filter that kind of tickets without using the "Modified" field. Or better, is there any way to prevent the Modified date from changing every day with no aparent reason?

I found a KB explaining custom rules and escalation can be the reason why the "Modified" field is getting updated on its own. I have turned custom rules and escalation off, but the problem persists.

Further information will probably be required but I can't think of any other relevant thing to add right now so please let me know and I'll provide.

Regards.

1 Comment   [ + ] Show comment
  • Would you be satisfied with a report of tickets that haven't been updated in a week? It isn't as convenient as a ticket view but it would give you a list of tickets that need to be updated. - chucksteel 7 years ago
    • Yes sure, I was looking into that but I must say I'm still not very familiar with reports.
      It would be awesome if I could get a report of the "not closed" tickets assigned to a specific agent that have not been commented or updated in 1 week. - Glaporte 7 years ago

Answers (1)

Posted by: chucksteel 7 years ago
Red Belt
0
This report will show tickets in all queues that have not been updated in the past seven days:
SELECT T.ID, T.TITLE, T.CREATED, LAST_CHANGE.TIMESTAMP, OWNER.USER_NAME as "Owner",
LAST_CHANGE.DESCRIPTION, T.TIME_CLOSED
FROM ORG1.HD_TICKET T
JOIN HD_TICKET_CHANGE LAST_CHANGE ON LAST_CHANGE.HD_TICKET_ID = T.ID
 and LAST_CHANGE.ID=(select MAX(ID) from HD_TICKET_CHANGE where HD_TICKET_CHANGE.HD_TICKET_ID = T.ID)
left join HD_STATUS on HD_STATUS_ID = HD_STATUS.ID
join USER OWNER on OWNER.ID = T.OWNER_ID
WHERE 
HD_STATUS.STATE != 'closed'
and LAST_CHANGE.TIMESTAMP < NOW() - INTERVAL 7 DAY
To use this report go to the Reporting module and select Choose Action, New (SQL). Fill out the required fields and paste the above code into the SQL textbox.


Comments:
  • Thanks Chucksteel, that seems to do what I need, however, it is a little too general.
    Is it possible to do the same but for every ticket assigned to a specific agent?

    The context (Which I should have explained before and I apologize for not doing it) is that one of our agents handles tickets that tend to stall for a long time and he needs to have visibility on which of those tickets has not been updated in one week time.
    All the tickets are in the same queue and have the particularity of being assigned to him. - Glaporte 7 years ago
    • You can limit this to a specific user by adding an additional line to the where clause. So at the end add:
      and OWNER.USER_NAME = "technician"

      This will limit it to just tickets assigned to technician. Once the report is created you can schedule it to be emailed on a regular basis under Reporting, Schedule Reports. - chucksteel 7 years ago
      • Thank you very much for the quick reply. I think this is what I needed. we will test it during this week and let you know how it goes. - Glaporte 7 years ago

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ