Re: [otrs] Trying to find out the time taken to close a ticket

[Alexander Scholler]

Hi Gabriele,

Gabriele D'Andrea schrieb:

Well,
there was something I still didn't understand about OTRS DataBase Schema.
Now finally I got it.

AND t.customer_id LIKE '%'
GROUP BY t.id;

I've written a SQL query, that allow me to filter closed tickets, and see the time taken from ticket opening till the last closing. SELECT t.tn, t.customer_id, t.create_time, MAX(th.change_time) AS change_time, DATEDIFF(MAX(th.change_time), t.create_time) AS open_days, TIMEDIFF(MAX(th.change_time), t.create_time) AS open_time FROM ticket AS t JOIN ticket_history AS th ON t.id=th.ticket_id JOIN ticket_history_type AS tht ON th.history_type_id=tht.id WHERE t.ticket_state_id=2 AND tht.id=27 AND th.name LIKE '%closed successful%'

I didn't check you query extensive, but I think it's not complete correct.

State-changes are recorded in ticket_history with writing in ticket_history.names '%%prev_state%new_state%%'. You just query for '%closed successful%', so you also receive all tickets with prev_state = 'closed sucessful'. Correct would be '%\%\%closed sucessful\%' (if '%' is masked within SQL as '\%' - I don't know).

This lead to the following output *tn* *customer_id* *create_time* *change_time* *open_days* *open_time* 2007051510000075 ecohmedia 2007-05-15 12:10:06 2007-05-15 12:18:28 0 00:08:22 2007051510000084 ecohmedia 2007-05-15 12:40:08 2007-05-15 14:46:34 0 02:06:26 2007051510000137 ecohmedia 2007-05-15 16:39:45 2007-05-15 16:44:13 0 00:04:28

2007051610000055                2007-05-16 11:22:09     2007-05-17 10:41:13     
1       23:19:04
2007051710000044                2007-05-17 16:23:34     2007-05-17 16:24:08     
0       00:00:34

2007051810000079 FATER 2007-05-18 14:48:07 2007-05-18 15:35:58 0 00:47:51 2007051810000122 FATER 2007-05-18 16:03:57 2007-06-19 17:55:30 32 769:51:33 2007061810000013 SANOFI 2007-06-18 09:41:25 2007-06-18 10:07:41 0 00:26:16 This is still not a perfect measurement of time spent to close the ticket, but is good enough for my purposes at the moment. Let's say that a ticket is closed, then reopened, then closed again: then I'm not able to calculate the time the ticket remained closed the first time. To do this, I think I need to use some programming language (like PHP or Perl), and maybe I'll do it later.

You can write a Static-stats-OTRS-module for this.

Then the output format is not perfect too, cause days are integer (and that colud be fine), but time is in hh:mm:ss (should be good to have days hh:mm:ss for longer periods), thus data may need some further manipulation. In the end I wish to thank all the people in this community that give me feedbacks and precious informations, expecially Alexander Scholler

Further comments, advices, warnings are also appreciated.

No problem.

Gabriele D'Andrea

Bye, Alex

smime.p7s
Description: S/MIME Cryptographic Signature

_______________________________________________
OTRS mailing list: otrs - Webpage: http://otrs.org/
Archive: http://lists.otrs.org/pipermail/otrs
To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs
Support or consulting for your OTRS system?
=> http://www.otrs.com/