Here is a query to return average ticket close by category (which is set using freefield1).  It should be relatively easy to adapt this to your needs…

 

select ifnull(freetext1,'Unclassified'),

         avg(TO_DAYS(t.change_time) - TO_DAYS(t.create_time))

         from ticket t,  ticket_state ts

         where t.ticket_state_id = ts.id and

         ts.id in (2,10) and

         DATE_FORMAT(t.create_time,'%Y') = DATE_FORMAT(now(),'%Y')

         group by freetext1;

 

I actually use this to feed a plot routine for my system that returns the data in the form of a graph

 

 

For more comprehensive reporting (beyond a simple graph) we have implemented more of an SLA type of report and I’ve attached part of the SQL for that (we do it by queue/geography).  It is implemented outside of the OTRS database since we didn’t want to mess with adding new tables and impact our ability to easily upgrade, etc.  Here is the table structure for that:

 

Table bangalore_sla in database oc

Field name	Type	Allow nulls?	Key	Default value	Extras
tn	varchar(50)	No	None
month	tinyint(4)	No	None	0
year	smallint(6)	No	None	0
submitter	varchar(50)	No	None
closed_by	varchar(50)	No	None
subject	tinytext	Yes	None
queue	varchar(200)	No	None
type	varchar(150)	No	None
category	varchar(150)	No	None
status	varchar(100)	No	None
create_time	datetime	No	None	0000-00-00 00:00:00
resp_time	datetime	Yes	None
close_time	datetime	Yes	None
hours_to_resolve	decimal(7,2)	Yes	None

 

Here is the relevant part of the script we run to refresh the SLA tables…

 

sladir='/home/oc_sla'

date=`date -I`

logfile=$sladir/refresh_oc_sla_$date.log

#

#  Refresh SLA data

#

mysql -u $user -h $host -D otrs < $sladir/bangalore.sql > $logfile

 

Hope it helps someone – have fun.

 

Mike

 

 

Is there a way to calculate the average time between states in OTRS?  For example:  we would like to know the average time between "open" and "closed successfully" for all tickets submitted to the "development" queue.

Any help would be appreciated….

Thanks
Gary Davis
PSIC Lab Manger