/build/static/layout/Breadcrumb_cap_w.png

Scripting Question


HD Ticket attachment search

10/31/2017 902 views
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

Comments

  • 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.

All Answers

0

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%';
Answered 12/06/2019 by: NeoSpeed
White Belt

0

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

Answered 11/02/2017 by: Druis
Third Degree Green Belt

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

View more:

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ