Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

In this Discussion

osTicket v1.10 (stable) and Maintenance Release v1.9.15 are now available! Go get it now

Purging of bulk old closed tickets

Dear,

I need to purge closed tickets which i dont need anymore.

Also, due to huge data when we need to check history of any ticket it takes too much time in loading.

Currently i need one year data. I tried to delete the closed tickets from frontend where i can delete 50 tickets as page size is 50. Even i tried using advanced seach by marking filter of tickets which needs to be deleted. 

But my problem is whatever i do its taking too much time in loading.

Can anyone suggest a smart way to purge old closed tickets which i dont need anymore (Not Frontend)    

Thanks,
Tanu Girdhar

Comments

  • edited June 2016
    Well if you run this SQL query
    SELECT ticket_id FROM ost_ticket WHERE closed < DATE_SUB(NOW(),INTERVAL 1 YEAR);


    You will get a list of all ticket_id's of tickets older than a year.
    example:
    2
    3
    4

    Then you could use that list to delete the ticket and ticket thread and any cdata for each one.

    DELETE FROM ost_ticket WHERE ticket_id=#;
    DELETE FROM ost_ticket_thread WHERE ticket_id=#;
    DELETE FROM ost_ticket__cdata WHERE ticket_id=#;
    DELETE FROM ost_ticket_lock WHERE ticket_id=#;
    DELETE FROM ost_ticket_event WHERE ticket_id=#;
    DELETE FROM ost_ticket_collaborator WHERE ticket_id=#;
    DELETE FROM ost_ticket_attachment WHERE ticket_id=#;

    Obviously doing this programmatically or by combining queries into one large query would save a lot of time..
  • as a follow up you might want to check other tables for the ticket_id field and delete those also... as I didn't look at EVERY table to ensure I got them all.
  • Dear,

    Thanks a lot for your prompt response on the same. Will Definitly try it out.
    Also, do let me know if there is another table (Except listed above) for which i need to run delete command.

    Thanks,
    Tanu Girdhar.
  • I would suggest you do this programmatically and make a new table to keep track of the ID#s you deleted. This way if you run into another table that needs to be cleared, you have a list of IDs you've cleared already.
Sign In or Register to comment.