OTRS Ticket Size

[manoj99]

Hi all,

Is there any way we could search the Ticket's size for an year.

Let say, if I wanted to know the Tickets for 2020. Can I get tickets size for that particular year from OTRS Admin interface or from cli.

Any help is appreciated.

Thanks,
Manoj.

[zzz]

Hello Manoj,

Do you store your articles in your DB or your FS (setting Ticket::Article::Backend::MIMEBase::ArticleStorage)?

Best regards
Emin

[manoj99]

Hi Emin,

We store Articles in DB

Thanks,
Manoj.

[zzz]

If the version in your profile is correct (v5), this should do the work.
Please check the values for plausibility as I did not really test it.

Code: Select all

SELECT DATE_FORMAT(create_time, '%Y') AS 'Year', SUM((length(content)/1024/1024)) AS 'Size in MB' 
FROM article_attachment 
GROUP BY DATE_FORMAT(create_time, '%Y')

Best regards
Emin

[manoj99]

Hi Emin,

I was able to retrieve the size per year through a similar query

Code: Select all

select extract(year from create_time) YEAR ,SUM((length(content)/1024/1024)) SIZEINMB FROM article_attachment GROUP BY extract(year from create_time);

Thanks,
Manoj.

[zzz]

Looks good, thanks for sharing :thumbs:

— Emin