Query to show tickets containing file attachments

Once in a while, I use rtx-shredder to purge tickets older than a month that are marked as deleted.

rtx-shredder --plugin “Tickets=limit,5000;status,deleted;updated_before,date -I -d '1 month ago'” --force

We have some very old tickets that contain attachments that I would like to purge. First, I would like to get a list of these to review before actually purging them from the db. Secondly, will someone please provide the syntax for doing this using rtx-shredder?

Thanks,
Shannon

Never miss a thing. Make Yahoo your home page.

Update on this. I found some old posts and I am trying to use the following command to display tickets by size:

mysql -u root -p -hlocalhost rt3 -e’select length(A.Content), A.id, T.Ticket fromAttachments A, Transactions T where A.TransactionId = T.id order by 1’

it returns:
“ERROR 1054 (42S22) at line 1: Unknown column ‘T.Ticket’ in ‘field list’”

I am running RT 3.6.5. Can help would be greatly appreciated!

Thanks,
Shannon

Never miss a thing. Make Yahoo your home page.

Shannon,

Your code doesn't make any sense to me. You use "A" for the Attachments 

table and “T” for the Transactions table. But when you refer to the
field in your code you wrote “T.Ticket”. There is no “Ticket” field on
the Transaction table. That’s probably why you got the error.
My DataDictionary describes the “Type” field as “containing what the
transaction is for (i.e. to change a field on a ticket, the “type” would
be “set”)”. “ObjectID” is described as “the ID of the record the
transaction is referring to”. “ObjectType” is described as “Describes
the class of record to which the transaction refers (most often
�Ticket�)”. Your code should probably refer to these three fields as
“T.ObjectType = ‘Ticket’ and T.ObjectID = A.Id and T.Type = (the type of
transaction attachment you’re looking for i.e. t"create” or “set” or
whatever). Hope this helps.

Kenn
LBNLOn 2/28/2008 8:07 AM, Shannon Adams wrote:

Update on this. I found some old posts and I am trying to use the following command to display tickets by size:

mysql -u root -p -hlocalhost rt3 -e’select length(A.Content), A.id, T.Ticket fromAttachments A, Transactions T where A.TransactionId = T.id order by 1’

it returns:
“ERROR 1054 (42S22) at line 1: Unknown column ‘T.Ticket’ in ‘field list’”

I am running RT 3.6.5. Can help would be greatly appreciated!

Thanks,
Shannon

  ____________________________________________________________________________________

Never miss a thing. Make Yahoo your home page.
Yahoo | Mail, Wetter, Suche, Nachrichten, Finanzen, Sport & mehr


The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Shannon,

    Your code doesn't make any sense to me. You use "A" for the Attachments

table and “T” for the Transactions table. But when you refer to the
field in your code you wrote “T.Ticket”. There is no “Ticket” field on
the Transaction table. That’s probably why you got the error.
Ticket was there for a long time until 3.4 (I think or even 3.2)
version, when transactions were only for tickets. Now, other records
may have transactions too.

    My DataDictionary describes the "Type" field as "containing what the

transaction is for (i.e. to change a field on a ticket, the “type” would
be “set”)". “ObjectID” is described as “the ID of the record the
transaction is referring to”. “ObjectType” is described as “Describes
the class of record to which the transaction refers (most often
“Ticket”)”. Your code should probably refer to these three fields as
“T.ObjectType = ‘Ticket’ and T.ObjectID = A.Id and T.Type = (the type of
transaction attachment you’re looking for i.e. t"create” or “set” or
whatever). Hope this helps.

Kenn
LBNL

Update on this. I found some old posts and I am trying to use the following command to display tickets by size:

mysql -u root -p -hlocalhost rt3 -e’select length(A.Content), A.id, T.Ticket fromAttachments A, Transactions T where A.TransactionId = T.id order by 1’

it returns:
“ERROR 1054 (42S22) at line 1: Unknown column ‘T.Ticket’ in ‘field list’”

I am running RT 3.6.5. Can help would be greatly appreciated!

Thanks,
Shannon

  ____________________________________________________________________________________

Never miss a thing. Make Yahoo your home page.
Yahoo | Mail, Wetter, Suche, Nachrichten, Finanzen, Sport & mehr


The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com


The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Best regards, Ruslan.