Hi,
I have a challenge.. I'm trying to get by mysql the time tat one ticket spends on each queue, until state = closed:
Example:
-> Ticket 123456 -> Open on queue Entry after Xminutes moved to Queue Websphere
-> Queue Websphere after X minutes moved to Queue Aproval
-> Aproval state=closed after X minutes
or
-> Ticket 123456 -> X min on Entry
-> Xmin on Websphere
-> Xmin on Aproval
Below is the query i'm trying to make, however i only get in one field the previos queue and the "new" queue.
After 5 days on this issue, i'm run-out of ideas..
Any thoughts???
SELECT
ticket.tn as 'Ticket Number',
ticket.title as Title,
ticket_history.name as Queue,
ticket_history_type.name as Action,
ticket_history.create_time
FROM
`ticket`
LEFT JOIN
ticket_history ON ticket_history.ticket_id = ticket.id
LEFT JOIN
ticket_history_type ON ticket_history.history_type_id = ticket_history_type.id
WHERE
ticket_history_type.name = 'Move'
OR ticket_history_type.name = 'NewTicket'
OR (ticket_history_type.name = 'StateUpdate'
AND (ticket_history.name LIKE '%closed successful\%\%'
OR ticket_history.name LIKE '%closed unsuccessful\%\%'))
Track Time on Each queue until close
Moderator: crythias
-
- Znuny newbie
- Posts: 9
- Joined: 02 Oct 2017, 12:36
- Znuny Version: 5/4/3.3
- Real Name: Bruno Graça
- Company: Softinsa
Track Time on Each queue until close
OTRS 3.3;4.2;6
MYSQL & MariaDB - Centos 7/Rhel 7/Ubunto
MYSQL & MariaDB - Centos 7/Rhel 7/Ubunto
-
- 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 on Each queue until close
I have managed to advance whit another direction.
get code on other, otterhub post, and change a little to my intent.
My issue now is to separate the queue's by row..
get code on other, otterhub post, and change a little to my intent.
My issue now is to separate the queue's by row..
Code: Select all
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
-
- Administrator
- Posts: 3965
- Joined: 18 Dec 2007, 12:23
- Znuny Version: Znuny and Znuny LTS
- Real Name: Roy Kaldung
- Company: Znuny
- Contact:
Re: Track Time on Each queue until close
Hi,
Just as a remark. As soon as you access the data via SQL you skip all time related setting like calendars in OTRS.
- Roy
Just as a remark. As soon as you access the data via SQL you skip all time related setting like calendars in OTRS.
- 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 ?
-
- 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 on Each queue until close
Yep... thanks... That was my suspicion..
However, for now, the previous query that i posted is getting the job done.(adding + 1 hours of pivot tables im Excel...)
As soon as i manage to get more time to search, maybe with temporary tables, just maybe, i can transform in the output that i want..
I'm always open to new ideas...
However, for now, the previous query that i posted is getting the job done.(adding + 1 hours of pivot tables im Excel...)
As soon as i manage to get more time to search, maybe with temporary tables, just maybe, i can transform in the output that i want..
I'm always open to new ideas...
OTRS 3.3;4.2;6
MYSQL & MariaDB - Centos 7/Rhel 7/Ubunto
MYSQL & MariaDB - Centos 7/Rhel 7/Ubunto