Well,
there was something I still didn't understand about OTRS DataBase
Schema.
Now finally I got it.
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%'
AND
t.customer_id LIKE '%'
GROUP BY t.id;
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.
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.
Gabriele D'Andrea
----- Original Message -----
To: "User questions and discussions about OTRS.org" <
otrs@otrs.org>
Sent: Monday, June 18, 2007 7:01 PM
Subject: RE: [otrs] Trying to find out the time taken to close a
ticket
I don't have the database schema in front of me right now, but it
sounds
like you would probably want to query the ticket history for
closing
events, ordered by date descending, with a limit of one
result?
-----Original Message-----
From: otrs-bounces@otrs.org
[mailto:otrs-bounces@otrs.org] On Behalf Of
Gabriele D'Andrea
Sent:
Sunday, June 17, 2007 11:35 PM
To: User questions and discussions about
OTRS.org
Subject: Re: [otrs] Trying to find out the time taken to close a
ticket
> Why shouldn't is be possible.
> Within the
ticket-history, just lookup the dates of ticket-creation
and
>
state-change to a close-state-type. That's the gap between creation
and
> the selected ticket-closure.
But how can I retrieve the
state_change?
I can lookup for ticket "close succesful" state, but there are
plenty if
operations have been executed after the ticket
closure...
----- Original Message -----
From: "Alexander
Scholler" <alexander.scholler@augsburg.de>
To:
"User questions and discussions about OTRS.org" <otrs@otrs.org>
Sent: Monday, June 18, 2007
8:22 AM
Subject: Re: [otrs] Trying to find out the time taken to close a
ticket
> Hi Gabriele,
>
> Gabriele D'Andrea
schrieb:
>> Hi Alex,
>>
>>> are you
looking
>>> (a) for the gap between ticket-creation and
-closure?
>>> This could be easily calculated from DB-entries
if
ticket-closure-time
>>> would be a singulare event, but
tickets can always be reopened. Even
if
>>> you configure otrs
that the customer can't do that, the agent can
always
>>> reopen
a ticket.
>>
>> Yes, I'm looking for this, and it's what i
tried to explain:
>> I think it's not possible to calculate the time
between ticket
creation
>> and closure, due to the partciular
database design.
>
> Why shouldn't is be possible.
> Within
the ticket-history, just lookup the dates of ticket-creation
and
>
state-change to a close-state-type. That's the gap between creation
and
> the selected ticket-closure.
>>
>>
Gabriele
>
> Bye, Alex
>
_______________________________________________
> 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/
>
>
>
>
>
_______________________________________________
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/
_______________________________________________
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/