HD Ticket attachment search
I am trying to create a report or search for all tickets that have an attachment so we can clean them up. I cannot find any way to do this easily through Kbox.
I did find an HD_Attachment table through MySQL workbench but I do not know how to get meaningful data (like the actual ticket number) from it.
Can anyone assist me?
1 Comment
[ + ] Show comment
-
Okay, that lists the ticket numbers only, I would also like to be able to search for certain attachment sizes. I know that is another table and will require some kind of join statement but not sure how to proceed with that. - ellisha 7 years ago
Answers (2)
Please log in to answer
Posted by:
NeoSpeed
5 years ago
You can create a custom Query as follow.
SELECT HD_TICKET_CHANGE.HD_TICKET_ID 'TicketID', HD_TICKET.TITLE 'Ticket Title', HD_ATTACHMENT.FILE_NAME 'Attachment Name', HD_ATTACHMENT.FILE_SIZE 'File Size'FROM HD_ATTACHMENTLEFT JOIN HD_TICKET_CHANGEON HD_ATTACHMENT.HD_TICKET_CHANGE_ID = HD_TICKET_CHANGE.IDLEFT JOIN HD_TICKETON HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
This query might be somewhat broad since its searching for every attachment.
Please consider adding a WHERE clause to ensure this is filtered.
ie. By File size:
WHERE HD_ATTACHMENT.FILE_SIZE > 128000;
ie. By File name:
WHERE HD_ATTACHMENT.FILE_NAME LIKE '%filename1%' or HD_ATTACHMENT.FILE_NAME LIKE '%filename2%';
Posted by:
Druis
7 years ago
Try this:-
SELECT HD_TICKET_CHANGE.HD_TICKET_ID FROM HD_TICKET_CHANGE
WHERE HD_TICKET_CHANGE.DESCRIPTION LIKE 'Added attachment%'
There is also a TIMESTAMP field on that table if you want to filter by date
The HD_TICKET_CHANGE table is what manages the relationship between the HD_TICKET and HD_ATTACHMENT tables
