Delete old tickets & delete automation

LeaJustLea

Hello,

I have two questions that I hope you can help me with.

(1) I would like to delete all old, closed tickets from before 2023 and (2) for the future, I would like to set a delete automation that deletes closed tickets after e.g. 6 months.

Are there any functions in the ticket system that can be used for my problems or are there any plugins?

Thanks in advance

Best regards

Lea

KevinTheJedi

LeaJustLea

Currently you must manually delete tickets. We don’t have an automatic purging system.

You can definitely write your own script that checks for old tickets and deletes them. However you'd be on your own with that as that is a customization.

Cheers.

LeaJustLea

Thank you for your quick reply.

Best & Cheerio

LeaJustLea

Hello again,
We were able to make the old ticket deletion and the automatic deletion work well.

But we also want to delete old users and use an automatic deletion for the future.

Is there a similar solution?

Thanks in advance for your help.

Best wishes

KevinTheJedi

LeaJustLea

Nope you’ll have to either manually delete users or write a script that does it.

Cheers.

ramrajone

LeaJustLea
You can run the SQL command if you want to delete an older ticket

You can run the following command to get tickets created more than 5 years ago:
SELECT * FROM ost_ticket WHERE DATE(created) < DATE_SUB(NOW(), INTERVAL 5 YEAR);

Then if you are happy with the results, you can run the following to delete them:
DELETE FROM ost_ticket WHERE DATE(created) < DATE_SUB(NOW(), INTERVAL 5 YEAR);

Please do a test and make sure you have a good backup, just in case anything goes wrong

ramrajone

LeaJustLea
Not sure if this still works or not you can try
https://github.com/clonemeagain/osticket-plugin-archiver

KevinTheJedi

ramrajone

That query deletes tickets but does not delete any associated records which holds the bulk of the storage and information in the db. There are many related objects to Tickets so running a delete on Tickets alone doesn’t do much for the db storage or purging related data. You could in theory setup events in the database to purge related data but that’s probably more complex than what OP is willing to get into.

Cheers.

KevinTheJedi

ramrajone

It doesn’t look like it. The latest version it appears to support is 1.10.x. That version is very old and no longer supported. Plus the plugin backend has changed dramatically since the introduction of multi-instance plugins in 1.17.x.

We do have plans on adding an archiving feature after v2.0 has been established so stay tuned for that!

Cheers.

ramrajone

KevinTheJedi
Great, waiting for v2.0, hope it will be sooner

codeaddict

@LeaJustLea

Here is a mysql query I created (with the help of ChatGPT) to mass delete certain tickets and all associated data, proceed with caution as this is very dangerous... and i'm also not sure if I missed any other tables (besides handling attachments)

START TRANSACTION;

-- Store the ticket IDs, related object IDs, and associated thread IDs for data to be deleted in a temporary table
CREATE TEMPORARY TABLE temp_ids_to_delete
(
    temp_id   INT AUTO_INCREMENT PRIMARY KEY,
    ticket_id INT,
    object_id INT,
    thread_id INT
);

INSERT INTO temp_ids_to_delete (ticket_id, object_id, thread_id)
SELECT ot.ticket_id, th.object_id, th.id AS thread_id
FROM ost_ticket ot
         JOIN ost_thread th ON ot.ticket_id = th.object_id
-- You can modify the where selection to match your needs
WHERE ot.ticket_id = 313;

-- Delete associated records from ost_thread_entry_email first since it queries live data
DELETE
FROM ost_thread_entry_email
WHERE thread_entry_id IN (SELECT id FROM ost_thread_entry WHERE thread_id IN (SELECT thread_id FROM temp_ids_to_delete));


-- Delete from ost_ticket for specified ticket ID
DELETE
FROM ost_ticket
WHERE ticket_id IN (SELECT ticket_id FROM temp_ids_to_delete);

-- Delete from ost_ticket__cdata for specified ticket ID
DELETE
FROM ost_ticket__cdata
WHERE ticket_id IN (SELECT ticket_id FROM temp_ids_to_delete);

-- Delete from ost_thread row(s) for specified object ID
DELETE
FROM ost_thread
WHERE id IN (SELECT thread_id FROM temp_ids_to_delete);

-- Delete associated records from ost_thread_entry
DELETE
FROM ost_thread_entry
WHERE thread_id IN (SELECT thread_id FROM temp_ids_to_delete);

-- Delete associated records from ost_thread_collaborator
DELETE
FROM ost_thread_collaborator
WHERE thread_id IN (SELECT thread_id FROM temp_ids_to_delete);


-- Delete from ost_thread_event
DELETE
FROM ost_thread_event
WHERE thread_id IN (SELECT thread_id FROM temp_ids_to_delete);

-- Delete from ost_thread_referral
DELETE
FROM ost_thread_referral
WHERE thread_id IN (SELECT thread_id FROM temp_ids_to_delete);


-- Drop the temporary table
DROP TEMPORARY TABLE IF EXISTS temp_ids_to_delete;

COMMIT;

KevinTheJedi

codeaddict

That doesn't include deleting all associated form data, etc. as well.

Also, please don't cross-post. Find the most recent post and make one comment.

Cheers.

nitesh

codeaddict you got any solution ?

nitesh

I want to delete the all closed tickets which are older than 2021 using sql query which i directly run in database , but i also want to delete the all associated data which are in other table

my current query :

SELECT ost_ticket.*
FROM ost_ticket
JOIN ost_ticket_status ON ost_ticket_status.id = ost_ticket.status_id
WHERE YEAR(ost_ticket.closed) < 2021;

Help me..

KevinTheJedi

nitesh

Do not cross-post. You already created your own discussion so wait for someone to reply there (which I already did).

Cheers.