[SOLVED] Remove deleted tickets from Dashboard

colomailbe

Hi everyone,

There's already plenty of threads concerning this topic, but I still need some help, as we are running in production and it's not completely obvious (at least for me ;-)

One of our IT guy wrote an agent that mailed hundreds of cases to osTicket. We deleted them but it's still in the Dashboard, as we are using it for KPI, it would be nice to be able to completely remove them.

I've checked table thread and thread_event (as suggested in other posts). In table thread_event I can easily find the deleted entries by timestamp and username. But I can't in table thread, id and object_id doesn't seems to match with anything I have in the other table.

So how can I safely remove those deleted tickets from the dashboard ?

Thanks !!

PHP: 8.2.4
Arch Linux: Last update somewhere in September
osTicket: 1.17.3

Colo

KevinTheJedi

colomailbe

Simply delete the records in the _thread_event table. We use those for the dashboard stats.

Cheers.

colomailbe

KevinTheJedi

Hi Kevin,

Thanks for quick answer, but I still have a question then.

As it seems that everything is logged apart. I can easily delete the rows for the DELETE action, but what about the CLOSE action and SYSTEM action when it created them from mail ? I suppose that I'll have to delete them as well (otherwise I'll still have 1k cases created today). Is there any way to find those rows based on either rows with delete action or subject, or mail body ?

Thanks !

Colo

KevinTheJedi

colomailbe

You would have to join it back to the _thread table where the event's thread_id matches the thread's ID.

Cheers.

colomailbe

it wasn't possible to still link on _thread after deletion... however, I figured out how to cleanup _thread_event:

Remove deletion event:
SELECT COUNT(*) FROM ost_thread_event WHERE username='user1' AND data IS NULL AND event_id=14;

Remove closed event:
SELECT COUNT(*) FROM ost_thread_event WHERE username='user2' AND data='{"status":[3,"Closed"]}' AND event_id=2 AND thread_id=0;

Remove created event:
SELECT COUNT(*) FROM ost_thread_event WHERE username='SYSTEM' AND data IS NULL AND event_id=1 AND thread_id=0;

Note. Of course SELECT COUNT(*) has to be replaced by DELETE, it was just to make sure to get the same number of rows everywhere.

And now:
SELECT COUNT(*) FROM ost_thread_event WHERE thread_id=0;
equal 0

Guess we are saved ;-)

Thanks Kevin !

Colo