Purging of bulk old closed tickets

aloksinghonline

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

ntozier

Well if you run this SQL querySELECT 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:234Then 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..

ntozier

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.

aloksinghonline

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.

chaosratt

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.

Matt-Cylanda

Is there a more clear step by step version of this? or a way to bulk close tickets? We have around 13k tickets that need to be closed.

maxmokry

I'm writing simple script:

require_once('../main.inc.php');
$max_purge = 4000;

$ids = array ();
$r = db_query ( 'SELECT ticket_id FROM ' . TICKET_TABLE . ' WHERE closed < "2020-04-20"  ORDER BY ticket_id ASC LIMIT ' . $max_purge );
while ( $i = db_fetch_array ( $r ) ) {
       $ids [] = $i ['ticket_id'];
}
$counter = 1;
$total = count($ids);
foreach($ids as $ticket_id){
        $t = Ticket::lookup ( $ticket_id );
        if ($t instanceof Ticket) {
                $t->delete();
                print($counter.' of '.$total.': Ticket '.$ticket_id." - purged\n");
        }
        $counter++;
}

jpichie

Hello,
Currently in the same boat.
Running 1.17.5, we have been running osTicket since around 2009.
Database is around 45GBs at the moment...
I need a way to purge old tickets including attachments if possible.

KevinTheJedi

jpichie

The best way is to make an advanced search for the criteria you want, select all, and click delete for each page of results. This will automatically purge all related data without you having to do any manual queries, etc. However, if you want to bulk delete all of them you'll have to write your own sql queries to select and delete all related data (there is a lot). Here are the Database ERDs that will help you relate the tables:

https://docs.osticket.com/en/latest/Developer%20Documentation/Database%20ERDs.html

Keep in mind that these are basic ERDs so it won't show everything; you'll have to go through things yourself to ensure you are catching all related data.

Cheers.

jpichie

Hello,
I have over 200k tickets to delete through thousands of pages of results...
Is there not a way to see the command that is run when clicking the garbage bin to delete?
Would just need to update the WHERE clause for the specifics (closed, anything older than X years, etc).

KevinTheJedi

jpichie

Not really, it's all via AJAX so you'd have to use a tool to send HTTP requests with the appropriate headers, etc. In such requests you’d still need to include all of the Ticket IDs you want to delete. You’d also have to include an authenticated Agent session in the request that has access/permissions to delete the tickets.

The best way is to do this manually as described above. Other than that you’d have to write your own SQL queries to delete tickets and all associated data. It will take a lot of trial and error and a lot of time but once you got it you can reuse it and simply update the date ranges.

Cheers.