Getting the attachment size by Queue

RT Users
1

Hi all,

Is there a easy way of getting the total attachment size by queue ? An SQL query, perhaps ?

We have about 20 queues on our instance, and although we know who are the biggest “offenders”, we would like to have a more detailed view on the subject…

2

Hi Francisco,

We had a similar issue on RT3.8. I haven’t checked this SQL query on RT4.4, but maybe it helps:

SELECT Users.Name, Users.id, Tickets.id, Queues.Name, Filename,Attachments.ContentType, Attachments.Created, Size, TimeWorked FROM Attachments, AttSize, Transactions, Tickets, Users, Queues WHERE AttId=Attachments.id AND AttSize.Size > 1500000 /* the query shows attachments larger than 1.5 MB /
AND Attachments.Created < “2013-03-01” / the query shows transactions after this date /
AND Transactions.id = TransactionId
AND Tickets.EffectiveId = Transactions.ObjectId
AND Transactions.ObjectType = ‘RT::Ticket’
AND Users.id = Attachments.Creator
AND TimeWorked = 0
AND Users.EmailAddress not like “%YOURDOMAIN.COM” / you can exclude your domestic stuff */
AND Queues.id = Tickets.Queue
order by AttSize.Size DESC
limit 500

Bests,

Akos

3

Hi Akos, thank for your help, but there isn’t a AttSize table on rt, neither in 4.2.3 that I’m using, and i could not find it on the rt 3.x schema also.
Actually, I cannot find a “size” anything on the current DB schema… Could it be that you had a temp table for that ?
My SQL know-how is pretty limited, so I can wrong here…

4

ops, yes, I’m getting remember. U have to do this proir of that (this is for mysql or mariadb, maybe u have to adjust something for pg):

CREATE TABLE AttSize (
AttId int(10) UNSIGNED NOT NULL,
Size bigint(20) UNSIGNED NOT NULL
) ;
ALTER TABLE AttSize
ADD UNIQUE KEY AttId (AttId,Size),
ADD KEY Size (Size);
INSERT INTO AttSize SELECT id, OCTET_LENGTH(Content) FROM Attachments;

The last command - the insert into - could be a time consuming process depending on your database size.

5

In MS SQL, I know you can define a view that lets you get the effect of this extra table. Maybe your DB has something similar.

/jeff

6

I think view could be useful. In this case I am not sure view is a right solution. I see this way:

Calculating the size of all blobs (attachment content fields) is a time-consuming process. (For example our RT has 2.6 million tickets, the size of the attachments table is 142 GB, it took half an hour to create this AttSize table.)

Since I think the view is usually produced on-the-fly (by the sql server), so every time when a view is invoked by a query the sql engine should (re)calculate all the attachment sizes. Maybe the view is cached for a short time (depending on the configuration of the given sql server instance), but it is temporary. As far as I know.

7

Hi all, just to reply that this worked just fine, the insert took about 1h15m on our test BD, which is about 750k tickets and 220 Gb.

As the SQL code got a bit mangled on the first post, I’m posting it here again for clarity. Our database is called rt3, and since we have many tickets from external emails , I’ve removed the filter for the external domains.

use rt3;
SELECT Users.Name, Users.id, Tickets.id, Queues.Name, Filename,Attachments.ContentType, Attachments.Created, Size, TimeWorked
FROM Attachments,AttSize, Transactions, Tickets, Users, Queues WHERE
AttId=Attachments.id AND AttSize.Size > 1500000 /* the query shows attachments larger than 1.5 MB */
AND Transactions.id = TransactionId
AND Tickets.EffectiveId = Transactions.ObjectId
AND Transactions.ObjectType = ‘RT::Ticket’
AND Users.id = Attachments.Creator
AND TimeWorked = 0
AND Queues.id = Tickets.Queue
order by AttSize.Size DESC
limit 500

1 Like