Report MySQL statements?

[dallen]

Hello,

I have a custom report that gives me everything i'd like, however i am missing 1 field which i cannot seem to locate as an option to add when i go through the report wizard. I am looking to add the "text" (ticket body/response) to my report. I believe the attribute i want is (article.a_body).

Is there a way i can obtain the SQL statements of the existing reports? if i could do that, i could simply modify to include my missing attribute and get the data i need...

Thanks in advance!

[CSL]

Hi,

You can do this with the MySQL general log. It records all statements run on the database, making it ideal for investigation / troubleshooting. I've done exactly what you describe - used it to look at the commands OTRS runs for its stats in order to build my own.

You can turn on the MySQL general log by adding something like this to your my.cnf file:
# Turn on general log
general-log=1
general_log_file=/var/log/mysql/general.log

It can be daunting to look through, especially for stats, as many of them run a separate command for every single ticket ID that is found, meaning that you could be scrolling through hundreds or even thousands of commands. The best thing to do is to look at the commands ran at the very start of the stat, and then try to understand what it's doing with each ticket ID after that.

Make sure to clear out the log between runs ( cat /dev/null > /var/log/mysql/general.log ) so you only capture what you're interested in.