[rt-users] Find ticket numbers of tickets with large attachments

[Remy van Elst]

Hy Rus,

How would I apply the query you listed to the query I listed? I'm not sure how to combine the two.


Op Jan 16, 2013, om 9:21 PM heeft Ruslan Zakirov <ruz at bestpractical.com> het volgende geschreven:

> Hi,
> 
> Attachments.TransactionId = Transactions.id, Transactions.ObjectId =
> Tickets.id WHERE Transactions.ObjectType = 'RT::Ticket'.
> 
> On Thu, Jan 17, 2013 at 12:08 AM, Remy van Elst <relst at relst.nl> wrote:
>> Hello,
>> 
>> I'm trying to find the ticket numbers of large attachments in my RT
>> instance. It is RT 4.0.8 running on Ubuntu 12.04, and I'm the root user
>> for this search quest.
>> 
>> I've constructed the following MySQL query:
>> 
>> select t.ObjectID,i.Subject from Attachments i LEFT OUTER JOIN
>> Transactions t on t.ObjectId = i.id where LENGTH(CONTENT) > 40000;
>> 
>> which would hopefully return the attachment ticket number.
>> 
>> I've read on this message:
>> http://lists.bestpractical.com/pipermail/rt-users/2007-June/046395.html
>> 
>> "The Attachments:TransactionId column points to a
>> Transactions record whose Transactions:ObjectId column contains the
>> ticket number."
>> 
>> but when searching in RT for a ticket number returned by the SQL query
>> it gives me a "Ticket does not found" error. Also, after ObjectId 65535
>> the returned ObjectID's are displayed as NULL in the query result.
>> 
>> How can I get the ticket numbers which have attachments larger than 4
>> MB? What am I doing wrong, or overlooking?
>> 
>> Sincerely,
>> 
> 
> 
> 
> -- 
> Best regards, Ruslan.