Find ticket numbers of tickets with large attachments

RT Users
1

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,

2

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@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:
[rt-users] Delete large attachment

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

3

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@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@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:
[rt-users] Delete large attachment

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

4

Hy Rus,

Tried some more stuff, now it is working. Thanks!

The Query:

mysql> SELECT DISTINCT Transactions.ObjectId,Attachments.Subject FROM
Attachments LEFT OUTER JOIN Transactions ON Transactions.Id =
Attachments.TransactionId WHERE Transactions.ObjectType = ‘RT::Ticket’
AND LENGTH(Attachments.Content) > 4000000;

Written it down for other people who might have the same problem:

https://raymii.org/s/tutorials/RT_find__and_delete_big_attachments.html

Sincerely,

5

Hi,

I hoped you will do some leg work and figure this out.On Thu, Jan 17, 2013 at 5:35 PM, Remy van Elst relst@relst.nl wrote:

Hy Rus,

Tried some more stuff, now it is working. Thanks!

The Query:

mysql> SELECT DISTINCT Transactions.ObjectId,Attachments.Subject FROM
Attachments LEFT OUTER JOIN Transactions ON Transactions.Id =
Attachments.TransactionId WHERE Transactions.ObjectType = ‘RT::Ticket’
AND LENGTH(Attachments.Content) > 4000000;

Written it down for other people who might have the same problem:

RT (Request Tracker) - find and delete big attachments - Raymii.org

Sincerely,

Best regards, Ruslan.