track time by ticket state

Moderator: crythias

Post Reply
blastik
Znuny newbie
Posts: 49
Joined: 24 Feb 2012, 09:59
Znuny Version: 5.0.16
Location: Barcelona, Spain
Contact:

track time by ticket state

Post by blastik »

Hi there guys,

I've been doing a little bit of research everywhere but I couldn't find the answer.
I want to track the lifetime of a ticket by counting the minutes per state.
For example, ticket spent in new state, open state, waiting for provider...

Is that possible somehow?

Thanks!
OTRS 5
Ubuntu with MySQL
Authenticating users & agents via LDAP
yuri0001
Znuny superhero
Posts: 631
Joined: 17 Mar 2011, 14:40
Znuny Version: 5.0.6
Real Name: Yuri Kolesnikov
Location: Russia

Re: track time by ticket state

Post by yuri0001 »

Hi!
Try to see TimeAccounting module, but without hardcoding it seems to me it's not available
Best regards Yuri Kolesnikov
OTRS 5.0.14, ITSM 5.0.14
SUSE 13.2, MariaDB 10.0.22(productive)
OTRS 5.0.14, ITSM 5.0.14(test)
blastik
Znuny newbie
Posts: 49
Joined: 24 Feb 2012, 09:59
Znuny Version: 5.0.16
Location: Barcelona, Spain
Contact:

Re: track time by ticket state

Post by blastik »

nope. that doesn't help me to archive what i'm looking for. there should be some watchdog running around and counting the time since someone opens a ticket until we close it.

thanks!
OTRS 5
Ubuntu with MySQL
Authenticating users & agents via LDAP
blastik
Znuny newbie
Posts: 49
Joined: 24 Feb 2012, 09:59
Znuny Version: 5.0.16
Location: Barcelona, Spain
Contact:

Re: track time by ticket state

Post by blastik »

(bump)

Ok let's explain it different way.
Is there any way to generate a statistic saying the average time a ticket spends per state (new/open/waiting for user/waiting for provider)? Separated by owner.

Thanks!
D.
OTRS 5
Ubuntu with MySQL
Authenticating users & agents via LDAP
jojo
Znuny guru
Posts: 15019
Joined: 26 Jan 2007, 14:50
Znuny Version: Git Master
Contact:

Re: track time by ticket state

Post by jojo »

only via SQL
"Production": OTRS™ 8, OTRS™ 7, STORM powered by OTRS
"Testing": ((OTRS Community Edition)) and git Master

Never change Defaults.pm! :: Blog
Professional Services:: http://www.otrs.com :: enjoy@otrs.com
blastik
Znuny newbie
Posts: 49
Joined: 24 Feb 2012, 09:59
Znuny Version: 5.0.16
Location: Barcelona, Spain
Contact:

Re: track time by ticket state

Post by blastik »

Ok I guess that's a huge query as system needs to pick up the times when a ticket changed the state, compare it with the current time, make the calculation and also show it by owner...
If you can help me with that, it would be awesome :lol:
OTRS 5
Ubuntu with MySQL
Authenticating users & agents via LDAP
New
Znuny newbie
Posts: 6
Joined: 05 Sep 2012, 13:39
Znuny Version: 3.1.8

Re: track time by ticket state

Post by New »

jojo wrote:only via SQL
i tried it like this (inspired by viewtopic.php?t=12546 ):

Code: Select all

select
	t.tn ticketnumber,
	CONCAT(u.first_name, " ", u.last_name) name,
	t.title title,
	t.customer_user_id customer_user,
	ta.time_unit time_unit,
	DATE_FORMAT(ta.create_time, "%m/%d/%y %H:%i") create_time,
	a.a_subject subject,
	ts.name status
from ticket t 
left join time_accounting ta on ta.ticket_id=t.id
left join article a on a.id=ta.article_id
left join users u on u.id = a.create_by
left join ticket_state ts on ts.id=t.ticket_state_id
where ta.time_unit is not null
order by create_time
than you get the time of each state, but if the ticket is closed, you just see 'closed successful' in the column 'state'.
has anyone an idea how i can get the 'old' state of the ticket? that means the tickets state, when time units were entered in the article
crythias
Moderator
Posts: 10169
Joined: 04 May 2010, 18:38
Znuny Version: 5.0.x
Location: SouthWest Florida, USA
Contact:

Re: track time by ticket state

Post by crythias »

New: The information you've gathered is self-reported, and does not accurately reflect the time spent in a given state.
In order to do what is requested, you'll need to look at the ticket_history
blastik wrote:system needs to pick up the times when a ticket changed the state, compare it with the current time, make the calculation and also show it by owner...
This will be an interesting calculation in itself. That is to say, if the owner changes within a state, that, also, will likely need to be addressed.

To reduce some overhead, it might be a good idea to query
SELECT id FROM `ticket_history_type` WHERE name='StateUpdate'
(In my case, this is 27.)
Also,
SELECT id FROM `ticket_history_type` WHERE name='NewTicket'
(in my case, this is 1.)
This won't change, so I can look in ticket_history WHERE history_type_id = '27' or '1'

This SQL is a basic query that should get you started. How you do the math is up to you:

Code: Select all

SELECT u.login owner, ts.name state, th.name fromto, th.create_time createtime, th.ticket_id ticket_id
FROM  `ticket_history` th
LEFT JOIN users u ON u.id = th.owner_id
LEFT JOIN ticket_state ts ON ts.id = th.state_id
WHERE th.history_type_id =  '27'
OR th.history_type_id = '1'
ORDER BY ticket_id
looks kinda like this: (tables are a pain in this forum). Look at it in your SQL box.

Code: Select all

owner       state	 fromto	          createtime	         ticket_id
root@localhost	new	New Ticket [2010080210123456] created.	2012-07-07 14:38:14	1
root@localhost	open	%%2012070710000013%%Misc%%3 normal%%open%%2	2012-07-07 14:52:31	2
root@localhost	open	%%2012090310000017%%Misc%%3 normal%%open%%3	2012-09-03 09:29:07	3
root@localhost	merged	%%open%%merged%%	2012-09-03 09:29:43	3
root@localhost	open	%%2012091210000018%%Junk%%2 low%%open%%4	2012-09-12 21:08:07	4
What it means, generally, is that ticket is currently in the last state listed, so it has been in that state "now" minus createtime. You may choose to include the th.history_type_id in the SELECT before FROM, but the first state listed per ticket_id is the first state datetimestamp of the ticket. There should not be any previous date per ticket_id.
OTRS 6.0.x (private/testing/public) on Linux with MySQL database.
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask
eterjack
Znuny newbie
Posts: 55
Joined: 21 Sep 2012, 01:45
Znuny Version: 3.1.10

Re: track time by ticket state

Post by eterjack »

Hello,

This question is really cool.

How could we do to calculate the interval between the state ticket, since we need to calculate over the calendar?
crythias
Moderator
Posts: 10169
Joined: 04 May 2010, 18:38
Znuny Version: 5.0.x
Location: SouthWest Florida, USA
Contact:

Re: track time by ticket state

Post by crythias »

A revised version that will give you the time per state

Code: Select all

SELECT u.login                                                            owner,
       ts.name                                                            state,
       th.name                                                            fromto
       ,
       th.create_time
       createtime,
       th.ticket_id
       ticket_id,
       Coalesce((SELECT th2.create_time
                 FROM   ticket_history th2
                 WHERE  th2.history_type_id IN ( '1', '27' )
                        AND th2.ticket_id = th.ticket_id
                        AND th2.id > th.id
                 LIMIT  1), Now())
       custom_date,
       Timestampdiff(second, th.create_time, Coalesce(
       (SELECT th2.create_time
        FROM   ticket_history th2
        WHERE  th2.history_type_id IN (
               '1', '27' )
               AND th2.ticket_id =
                   th.ticket_id
               AND th2.id > th.id
        LIMIT  1), Now())) diff
FROM   `ticket_history` th
       LEFT JOIN users u
              ON u.id = th.owner_id
       LEFT JOIN ticket_state ts
              ON ts.id = th.state_id
WHERE  th.history_type_id IN ( '1', '27' )
ORDER  BY ticket_id 
SQL code formatted from Instant SQL Formatter

This will return the difference in seconds. Whatever this means to you should be able to be formatted in whatever presentation layer you need.
OTRS 6.0.x (private/testing/public) on Linux with MySQL database.
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask
jalvarado
Znuny newbie
Posts: 8
Joined: 02 Oct 2012, 09:42
Znuny Version: 3.1.10

Re: track time by ticket state

Post by jalvarado »

Hello,

How can I use your query in order to calculate the duration of each ticket state but in working hours?
Any idea to integrate your query with files with calendars´configuration?
May be using static stats in OTRS (http://doc.otrs.org/3.1/en/html/stats-m ... ats-static)?

Thanks
OTRS 3.1 on Linux with MySQL
crythias
Moderator
Posts: 10169
Joined: 04 May 2010, 18:38
Znuny Version: 5.0.x
Location: SouthWest Florida, USA
Contact:

Re: track time by ticket state

Post by crythias »

jalvarado wrote:How can I use your query in order to calculate the duration of each ticket state but in working hours?
I don't know if that's feasible. There might be some development needed to achieve this goal.

Somehow, I'd think there's some CASE IF statements or something and converting your work hours into something that can be parsed against the above query is no small feat.

The logic sentence that I can come up with goes like this:
grab the th.create_time. If it resolves to a weekend or holiday (how do we know? ... guess...) or the create time is not within open hours (how do we know?) then keep checking for a th.create_time versus the start of open hours. If it changes during off hours, ignore it as it never was in that state during open hours. If changed, we check this again ... If changed again, we check until we find a state that stays until the start of business. but now we have a psuedo start of the later of opening of business or the ticket state.
But what if the ticket state has changed from New on Friday, open on Saturday (we're closed, but someone worked on it?), and for some reason is now (Saturday) in Pending for 3 days?

According to the logic, the ticket was never opened, and even though the ticket is pending, it never changed during open hours and "feels" like it's still "new" (all changes to ticket state happen off hours, so we don't know how to calculate the length of time it was open.)

Basically, draw your own map like this. What would be a valid value for the state "new"? Did it cease being "new" at 5pm Friday? What state should it be in on Monday? What is the value? Do you ignore that it was in "open" on Saturday? What if the state change was at 5:05pm? How much wiggle room are you going to give yourself? And how do you give that information to your query?

What you request is nontrivial.
OTRS 6.0.x (private/testing/public) on Linux with MySQL database.
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask
blastik
Znuny newbie
Posts: 49
Joined: 24 Feb 2012, 09:59
Znuny Version: 5.0.16
Location: Barcelona, Spain
Contact:

Re: track time by ticket state

Post by blastik »

Following up on this.... :?

I think OTRS should create an addon for this. My company will be very interested on paying for it :)

Regards,
David
OTRS 5
Ubuntu with MySQL
Authenticating users & agents via LDAP
reneeb
Znuny guru
Posts: 5018
Joined: 13 Mar 2011, 09:54
Znuny Version: 6.0.x
Real Name: Renée Bäcker
Company: Perl-Services.de
Contact:

Re: track time by ticket state

Post by reneeb »

Then you should contact OTRS at enjoy@otrs.com or any other company that provides OTRS development (like c.a.p.e IT, znuny.com, perl-services.de, ...).
Perl / Znuny development: http://perl-services.de
Free Znuny add ons from the community: http://opar.perl-services.de
Commercial add ons: http://feature-addons.de
jalvarado
Znuny newbie
Posts: 8
Joined: 02 Oct 2012, 09:42
Znuny Version: 3.1.10

Re: track time by ticket state

Post by jalvarado »

Hello:

I have been doing some queries with the following logic:
1. Create tables to store calendars and working hour per calendar
2. Populate data into these tables with the same configuration of calendar and working hours in OTRS files
3. Calculate starting and ending dates for each state for each ticket with SLA configured.
4. Calculate the duration of each state in hours.
5. It is necessary to call a stored procedure in mysql console, and then query a final table.


I am validating performance and I hope to use this solution to validate the SLA´s fulfillment by only adding up duration of valid states grouping per ticket (discarding states like "Pending customer response" and so on). I am using OTRS 3.1.

Regards,

Jorge
OTRS 3.1 on Linux with MySQL
crythias
Moderator
Posts: 10169
Joined: 04 May 2010, 18:38
Znuny Version: 5.0.x
Location: SouthWest Florida, USA
Contact:

Re: track time by ticket state

Post by crythias »

I have a workaround for after hours:
Create a state "off hours" of type "open" (or pending?)
Generic agent set state="off hours" if ticket= new or open at close of business M-F. (edit: you'll want to run this at least every hour overnight to "catch" new overnight tickets)
Generic Agent set state="open" if ticket state is "off hours" at start of business M-F (for instance)


if you want to exclude certain states, you can use, for example, a "AND th.state_id IN (1,4)" (new, open) in the bottom WHERE.
The way I see it, if work is performed after hours, it still doesn't apply to SLA (unless you've a 24 hour SLA, then you aren't going to talk about working hours.)
OTRS 6.0.x (private/testing/public) on Linux with MySQL database.
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask
petervanbeugen
Znuny newbie
Posts: 1
Joined: 12 Mar 2013, 23:34
Znuny Version: 3.x (0/1/2)
Real Name: Peter van Beugen
Company: TDA

Re: track time by ticket state

Post by petervanbeugen »

create a separate table (ticket_state_time) (id,ticket_id,ticket_state,calculatedtime,....etc etc)
create a trigger on the "ticket" table which insert a record in the ticket_state_time table when the ticket_state changes (NEW.ticket_state_id <> OLD.ticket_state_id) with the desired information / time calculation (and store the OLD.ticket_state_id !!)
create a generic agent job or a MySQL scheduled query (very accurate and fast!) which changes the ticket_state off/on business hours like the workaround of crythias
now every change of the ticket_state is immediate recorded in the separate table with the exact data and a query on this table (create indexes!) is simple and fast
jalvarado
Znuny newbie
Posts: 8
Joined: 02 Oct 2012, 09:42
Znuny Version: 3.1.10

Re: track time by ticket state

Post by jalvarado »

I need to make calculations for old tickets, so the use of trigger is not an option in my case.
I created an stored procedure that uses new tables. These tables must have the data for calendars, any changes in OTRS calendars configurations must be replicated to these tables (this is a tedious duty). I attached 5 files, the readme file has the instructions to use the procedure. (for 4205 tickets the process takes 1 minute executing). Comments are welcome.
You do not have the required permissions to view the files attached to this post.
OTRS 3.1 on Linux with MySQL
benjamin1
Znuny newbie
Posts: 14
Joined: 19 Aug 2015, 05:16
Znuny Version: 5.0.13

Re: track time by ticket state

Post by benjamin1 »

I have noticed an issue when using Crythias's sql, the custom_date is not picking up the next state change correctly instead as shown in the example below, the bottom two result have the same custom_date causing duplication in pending time calculation

%%Awaiting Acknowledgement%%Awaiting Customer Info%% 2015-08-16 17:48:40 3320 2015-08-19 11:23:03 236063
%%Pending auto close+%%Closed%% 2015-08-19 11:23:03 3320 2015-08-21 17:20:34 194251
%%Awaiting Customer Info%%Pending auto close+%% 2015-08-17 10:39:11 3320 2015-08-19 11:23:03 175432
%%Pending auto close+%%Awaiting Acknowledgement%% 2015-08-17 11:09:03 3320 2015-08-19 11:23:03 173640

Can any SQL wizards verify the query.

Thank you.
benjamin1
Znuny newbie
Posts: 14
Joined: 19 Aug 2015, 05:16
Znuny Version: 5.0.13

Re: track time by ticket state

Post by benjamin1 »

The problem appears to be the sorting of the timestamps in custom_date column, I have added a 'order by th2.id' which fixes the issue but also causes the query to be too slow to run on whole DB. Can anyone optimize below corrected query?



SELECT th.id,
ts.name state,
th.name fromto,
th.create_time
createtime,
th.ticket_id
ticket_id,
Coalesce((SELECT th2.create_time
FROM ticket_history th2
WHERE th2.history_type_id IN ( '1', '27' )
AND th2.ticket_id = th.ticket_id
AND th2.id > th.id
order by th2.id
LIMIT 1), Now())
custom_date,
Timestampdiff(second, th.create_time, Coalesce(
(SELECT th2.create_time
FROM ticket_history th2
WHERE th2.history_type_id IN (
'1', '27' )
AND th2.ticket_id =
th.ticket_id
AND th2.id > th.id
order by th2.id
LIMIT 1), Now())) diff
FROM `ticket_history` th
LEFT JOIN ticket_state ts
ON ts.id = th.state_id
WHERE th.history_type_id IN ( '1', '27' )
AND ts.id IN ('9', '11', '14', '25')
and ticket_id='3248'
ORDER BY ticket_id
crythias
Moderator
Posts: 10169
Joined: 04 May 2010, 18:38
Znuny Version: 5.0.x
Location: SouthWest Florida, USA
Contact:

Re: track time by ticket state

Post by crythias »

I don't really understand the problem. The create_time is or should be always in the order of the database. Can you explain what's "wrong" for you? and if it's slow, then you may wish to apply an index.

Please don't PM me for support. It's not fair to others.
OTRS 6.0.x (private/testing/public) on Linux with MySQL database.
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask
ronaldjerardmedina
Znuny advanced
Posts: 102
Joined: 24 Jun 2016, 11:36
Znuny Version: 5.0.3-01
Real Name: Ronald Jerard
Company: Medina

Re: track time by ticket state

Post by ronaldjerardmedina »

crythias wrote:A revised version that will give you the time per state

Code: Select all

SELECT u.login                                                            owner,
       ts.name                                                            state,
       th.name                                                            fromto
       ,
       th.create_time
       createtime,
       th.ticket_id
       ticket_id,
       Coalesce((SELECT th2.create_time
                 FROM   ticket_history th2
                 WHERE  th2.history_type_id IN ( '1', '27' )
                        AND th2.ticket_id = th.ticket_id
                        AND th2.id > th.id
                 LIMIT  1), Now())
       custom_date,
       Timestampdiff(second, th.create_time, Coalesce(
       (SELECT th2.create_time
        FROM   ticket_history th2
        WHERE  th2.history_type_id IN (
               '1', '27' )
               AND th2.ticket_id =
                   th.ticket_id
               AND th2.id > th.id
        LIMIT  1), Now())) diff
FROM   `ticket_history` th
       LEFT JOIN users u
              ON u.id = th.owner_id
       LEFT JOIN ticket_state ts
              ON ts.id = th.state_id
WHERE  th.history_type_id IN ( '1', '27' )
ORDER  BY ticket_id 
SQL code formatted from Instant SQL Formatter

This will return the difference in seconds. Whatever this means to you should be able to be formatted in whatever presentation layer you need.


How can i extract the ticket number? and ticket number on where clause.
Example i Wanted to add TicketNumber on Select clause and i wanted to output only Ticket number 1600001 to 1600100?
OTRS 5.0.3-01 - MySql - SLES -
Soldos81
Znuny newbie
Posts: 9
Joined: 02 Oct 2017, 12:36
Znuny Version: 5/4/3.3
Real Name: Bruno Graça
Company: Softinsa

Re: track time by ticket state

Post by Soldos81 »

Add -> t.tn as 'Ticket_Number', in the first select
and
LEFT JOIN ticket t ON t.id = th.ticket_id
on the last from...

we use:
SELECT
th.id,
ts.name state,
t.tn as 'Ticket_Number',
th.name fromto,
th.create_time createtime,
-- th.ticket_id ticket_id,
COALESCE((SELECT
th2.create_time
FROM
ticket_history th2
WHERE
th2.history_type_id IN ('1' , '16')
AND th2.ticket_id = th.ticket_id
AND th2.id > th.id
ORDER BY th2.id
LIMIT 1),
NOW()) custom_date,
TIMESTAMPDIFF(SECOND,
th.create_time,
COALESCE((SELECT
th2.create_time
FROM
ticket_history th2
WHERE
th2.history_type_id IN ('1' , '16')
AND th2.ticket_id = th.ticket_id
AND th2.id > th.id
ORDER BY th2.id
LIMIT 1),
NOW())) diff
FROM
`ticket_history` th
LEFT JOIN ticket_state ts ON ts.id = th.state_id
LEFT JOIN ticket t ON t.id = th.ticket_id
WHERE
th.history_type_id IN ('1' , '16')
ORDER BY ticket_id
OTRS 3.3;4.2;6
MYSQL & MariaDB - Centos 7/Rhel 7/Ubunto
PavelV
Znuny newbie
Posts: 4
Joined: 08 Mar 2019, 10:33
Znuny Version: 5xxxx

Re: track time by ticket state

Post by PavelV »

Hello,

For our internal reporting I'm trying to track status changes over time per each ticket in result format TICKET_ID, STATE_ID, VALFROM, VALTO.
At the moment I'm capturing just events NewTicket and StateUpdate, what is strange that sometime TICKET_HISTORY table STATE_ID is changed without occurance of those events, so if I will compare all tickets by their last state from TICKET table and by last state based on this query I'm getting some unexpected differences .. is there some comon explanation for this ?

Code: Select all

select
   TICKET_ID, VALFROM, VALTO, case when sta.ID is null then to_number(STATE_ID) else sta.ID end as STATE_ID
from(
  select
     TICKET_ID
    ,CREATE_TIME as VALFROM
    ,case when lead(TICKET_ID) over(order by TICKET_ID, ID) <> TICKET_ID then date '3000-01-01' - interval '1' second
     else nvl(lead(CREATE_TIME) over(order by TICKET_ID, ID) - interval '1' second, date '3000-01-01' - interval '1' second) 
     end as VALTO
    ,STATE_ID
  from(
    select th.TICKET_ID, th.ID, th.CREATE_TIME
      ,case when tht.NAME='NewTicket' then to_char(th.STATE_ID)
            when tht.NAME='StateUpdate' then regexp_replace(th.NAME,'%%.*%%(.*)%%','\1')
       end as STATE_ID
      ,lag(th.TICKET_ID) over(order by th.TICKET_ID, th.ID) as LT
	    ,lag(case when tht.NAME='NewTicket' then to_char(th.STATE_ID)
                when tht.NAME='StateUpdate' then regexp_replace(th.NAME,'%%.*%%(.*)%%','\1')
           end) over(order by th.TICKET_ID, th.ID)
       as LS
    from TICKET_HISTORY th
    inner join TICKET_HISTORY_TYPE tht
      on tht.ID=th.HISTORY_TYPE_ID
    where 1=1
      and tht.NAME in('NewTicket','StateUpdate') -- only following events are considered
  )
  where LS is null or LS <> STATE_ID or LT is null or LT <> TICKET_ID
)h_sta
left join DWCORE.TOTRSv1D_STATE sta
  on sta.NAME=h_sta.STATE_ID and not regexp_like(h_sta.STATE_ID, '\d+')
where VALTO>VALFROM -- 0 time states eliminated
We are running on OTRS v5
OTRS 5 on Linux/Oracle
root
Administrator
Posts: 3934
Joined: 18 Dec 2007, 12:23
Znuny Version: Znuny and Znuny LTS
Real Name: Roy Kaldung
Company: Znuny
Contact:

Re: track time by ticket state

Post by root »

Hi,

If you like to track the accounted or working time of each single state of a ticket I recommend using existing (commercial) packages.

- Roy
Znuny and Znuny LTS running on CentOS / RHEL / Debian / SLES / MySQL / PostgreSQL / Oracle / OpenLDAP / Active Directory / SSO

Use a test system - always.

Do you need professional services? Check out https://www.znuny.com/

Do you want to contribute or want to know where it goes ?
PavelV
Znuny newbie
Posts: 4
Joined: 08 Mar 2019, 10:33
Znuny Version: 5xxxx

Re: track time by ticket state

Post by PavelV »

Can you please give some example what would you recommend in general as a good extension for reporting ? We need to get for example .. time when ticket was solved, it's solution time, number of queue hops, time spent per queue, include/exclude child tickets and many more.
OTRS 5 on Linux/Oracle
Post Reply