Track Time on Each queue until close

Moderator: crythias

Post Reply
Soldos81
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

Post by Soldos81 »

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\%\%'))
OTRS 3.3;4.2;6
MYSQL & MariaDB - Centos 7/Rhel 7/Ubunto
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 on Each queue until close

Post by Soldos81 »

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..

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
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 on Each queue until close

Post by root »

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
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 ?
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 on Each queue until close

Post by Soldos81 »

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...
OTRS 3.3;4.2;6
MYSQL & MariaDB - Centos 7/Rhel 7/Ubunto
Post Reply