Friday, January 20, 2012

OTRS-hacking: log all sql queries for use in external reports

OTRS is a great ticket-tracking/helpdesk software. It even has cool statistics built in, but sometimes the PHB goes crazy and asks for impossibily detailed statistics.

For those situations when the stats module cannot help us there a couple of (Open Source) tricks up our sleeve:
  1. install the Eclipse Birt web reporting application (we will need java and tomcat for that)
  2. create a custom report using the report designer
  3. deploy the report on the server and send the link to the boss
To make sure the SQL queries used in the report are the same used by OTRS we can temporarily have OTRS log all queries (remember to disable logging after you're done or your log files will grow out of control) by editing the Kernel/System/DB.pm file as documented here:

# 0=off; 1=updates; 2=+selects; 3=+Connects;
# $Self->{Debug} = $Param{Debug} || 0; 
# leave the original around for later
$Self->{Debug} = 2; 

No comments: