/build/static/layout/Breadcrumb_cap_w.png
10/07/2016 950 views
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

Comments

  • 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.
    • 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.

All Answers

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.

Answered 10/17/2016 by: chucksteel
Red Belt

  • 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.
    • 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.
      • 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.