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
Answers (1)
Please log in to answer
Posted by:
chucksteel
8 years ago
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 8 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 8 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 8 years ago
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 8 years ago