track time by ticket state
Moderator: crythias
-
- Znuny newbie
- Posts: 49
- Joined: 24 Feb 2012, 09:59
- Znuny / OTRS Version: 5.0.16
- Location: Barcelona, Spain
- Contact:
track time by ticket state
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!
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
Ubuntu with MySQL
Authenticating users & agents via LDAP
-
- Znuny superhero
- Posts: 631
- Joined: 17 Mar 2011, 14:40
- Znuny / OTRS Version: 5.0.6
- Real Name: Yuri Kolesnikov
- Location: Russia
Re: track time by ticket state
Hi!
Try to see TimeAccounting module, but without hardcoding it seems to me it's not available
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)
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)
-
- Znuny newbie
- Posts: 49
- Joined: 24 Feb 2012, 09:59
- Znuny / OTRS Version: 5.0.16
- Location: Barcelona, Spain
- Contact:
Re: track time by ticket state
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!
thanks!
OTRS 5
Ubuntu with MySQL
Authenticating users & agents via LDAP
Ubuntu with MySQL
Authenticating users & agents via LDAP
-
- Znuny newbie
- Posts: 49
- Joined: 24 Feb 2012, 09:59
- Znuny / OTRS Version: 5.0.16
- Location: Barcelona, Spain
- Contact:
Re: track time by ticket state
(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.
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
Ubuntu with MySQL
Authenticating users & agents via LDAP
Re: track time by ticket state
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
"Testing": ((OTRS Community Edition)) and git Master
Never change Defaults.pm! :: Blog
Professional Services:: http://www.otrs.com :: enjoy@otrs.com
-
- Znuny newbie
- Posts: 49
- Joined: 24 Feb 2012, 09:59
- Znuny / OTRS Version: 5.0.16
- Location: Barcelona, Spain
- Contact:
Re: track time by ticket state
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
If you can help me with that, it would be awesome

OTRS 5
Ubuntu with MySQL
Authenticating users & agents via LDAP
Ubuntu with MySQL
Authenticating users & agents via LDAP
Re: track time by ticket state
i tried it like this (inspired by viewtopic.php?t=12546 ):jojo wrote:only via SQL
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
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
-
- Moderator
- Posts: 10168
- Joined: 04 May 2010, 18:38
- Znuny / OTRS Version: 5.0.x
- Location: SouthWest Florida, USA
- Contact:
Re: track time by ticket state
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
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:
looks kinda like this: (tables are a pain in this forum). Look at it in your SQL box.
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.
In order to do what is requested, you'll need to look at the ticket_history
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.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...
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
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
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
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
Re: track time by ticket state
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?
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?
-
- Moderator
- Posts: 10168
- Joined: 04 May 2010, 18:38
- Znuny / OTRS Version: 5.0.x
- Location: SouthWest Florida, USA
- Contact:
Re: track time by ticket state
A revised version that will give you the time per state
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.
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
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
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
Re: track time by ticket state
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
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
-
- Moderator
- Posts: 10168
- Joined: 04 May 2010, 18:38
- Znuny / OTRS Version: 5.0.x
- Location: SouthWest Florida, USA
- Contact:
Re: track time by ticket state
I don't know if that's feasible. There might be some development needed to achieve this goal.jalvarado wrote:How can I use your query in order to calculate the duration of each ticket state but in working hours?
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
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
-
- Znuny newbie
- Posts: 49
- Joined: 24 Feb 2012, 09:59
- Znuny / OTRS Version: 5.0.16
- Location: Barcelona, Spain
- Contact:
Re: track time by ticket state
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

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
Ubuntu with MySQL
Authenticating users & agents via LDAP
-
- Znuny guru
- Posts: 5018
- Joined: 13 Mar 2011, 09:54
- Znuny / OTRS Version: 6.0.x
- Real Name: Renée Bäcker
- Company: Perl-Services.de
- Contact:
Re: track time by ticket state
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
Free Znuny add ons from the community: http://opar.perl-services.de
Commercial add ons: http://feature-addons.de
Re: track time by ticket state
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
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
-
- Moderator
- Posts: 10168
- Joined: 04 May 2010, 18:38
- Znuny / OTRS Version: 5.0.x
- Location: SouthWest Florida, USA
- Contact:
Re: track time by ticket state
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.)
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
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
-
- Znuny newbie
- Posts: 1
- Joined: 12 Mar 2013, 23:34
- Znuny / OTRS Version: 3.x (0/1/2)
- Real Name: Peter van Beugen
- Company: TDA
Re: track time by ticket state
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
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
Re: track time by ticket state
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.
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
Re: track time by ticket state
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.
%%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.
Re: track time by ticket state
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
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
-
- Moderator
- Posts: 10168
- Joined: 04 May 2010, 18:38
- Znuny / OTRS Version: 5.0.x
- Location: SouthWest Florida, USA
- Contact:
Re: track time by ticket state
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.
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
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
-
- Znuny advanced
- Posts: 102
- Joined: 24 Jun 2016, 11:36
- Znuny / OTRS Version: 5.0.3-01
- Real Name: Ronald Jerard
- Company: Medina
Re: track time by ticket state
crythias wrote:A revised version that will give you the time per stateSQL code formatted from Instant SQL FormatterCode: 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
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 -
-
- Znuny newbie
- Posts: 9
- Joined: 02 Oct 2017, 12:36
- Znuny / OTRS Version: 5/4/3.3
- Real Name: Bruno Graça
- Company: Softinsa
Re: track time by ticket state
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
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
MYSQL & MariaDB - Centos 7/Rhel 7/Ubunto
Re: track time by ticket state
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 ?
We are running on OTRS v5
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
OTRS 5 on Linux/Oracle
-
- Administrator
- Posts: 3559
- Joined: 18 Dec 2007, 12:23
- Znuny / OTRS Version: Znuny and Znuny LTS
- Real Name: Roy Kaldung
- Company: Znuny
- Contact:
Re: track time by ticket state
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
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 ?
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 ?
Re: track time by ticket state
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