API To Delete Old Ticket & Attachments

rsclmumbai

osTicket Version v1.17.4 (ea462cb) — Up to date
Web Server Software Apache/2.4.53 (Rocky Linux) OpenSSL/3.0.1
MySQL Version 10.10.3
PHP Version 8.1.17

Does OSTikcte have an API to delete tickets and their attachments va CLI?
It would be great to have such a script for maintenance purposes.
Thanks

KevinTheJedi

rsclmumbai

You can't delete Tickets but you can delete files with expunge action for file cli command. (eg. php manage.php file expunge) The caveat to this is you will need to provide some sort of criteria (eg. php manage.php file expunge -T ticket_id).

Cheers.

rsclmumbai

Okay, so let's say I want to delete all tickets that were last updated BEFORE 01-01-2020 (along with their attachments).
attachments

select tocket_id from ost_ticket where lastupdate < '2020-01-01'
php manage.php file expunge -T $ticket_id

Will something like this delete all the attachments for the respective tickets?

Also, let's say I want to delete ticket_id 123456 via CLI.
What is the best approach to writing a script to delete all relevant records from all tables for ticket_id = 123456

Thanks

KevinTheJedi

rsclmumbai

php manage.php file expunge -T $ticket_id Will something like this delete all the attachments for the respective tickets?

It should, yes.

What is the best approach to writing a script to delete all relevant records from all tables for ticket_id = 123456

Reviewing the database structure and writing your own script. With any custom mods, scripts, etc. you are on your own.

Please be careful though as we deduplicate file uploads. Meaning 1 file can be attached to multiple objects. So even if you delete a ticket with an attachment, the associated file could be attached elsewhere so you should account for this before blindly deleting associated files.

Cheers.

rsclmumbai

KevinTheJedi Thanks
Does OSTicet have a CLI based script to delete tickets?

If not, then I may consider venturing this path.
Thanks again!!

KevinTheJedi

rsclmumbai

Not at this time, no. Only through UI.

Cheers.

rsclmumbai

KevinTheJedi
Okay, let me give it a thought.
Thanks

nohcho82

I deleted old tickets but the size of database is not reduced. Probably attachments are not deleted, why? I did the same with previous version and attachments were deleted automatically.
I use now version 1.17.4
Any help?

KevinTheJedi

nohcho82

That’s because when you delete a ticket we don’t automatically delete the attachments. They are queued for deletion and will be fully deleted after 24 hours. To test this you can set the created date for a file object to 24 hours ago, run cron, and it should be deleted.

Also, if the file is attached to multiple objects and you only delete one of the related objects the file will not be deleted as it’s still being used on the other objects.

Cheers.

rsclmumbai

Hi nohcho82
I'd be interested in learning about your solution to delete tickets. Would you be willing to share the code?
Thanks

rsclmumbai

nohcho82 I believe the size of the DB will reduce only if you do "optimize table". That's how MySQL works. Correct me if I'm wrong.

KevinTheJedi

rsclmumbai

You are wrong. Please read my reply above.

Cheers.

rsclmumbai

KevinTheJedi
For database size reduction - I was referring to the file size on disk, which will not reduce if mysql is not optimized.

KevinTheJedi

rsclmumbai

That is somewhat valid. It just depends on if you have InnoDB or MyISAM tables. MyISAM keeps memory on disk until you reclaim it manually but InnoDB should reclaim storage automatically. Of course this only applies if you have innodb_file_per_table setting enabled.

Cheers.