[otrs] DB Tables

[André Cavalcante]

Hi there,

Once I don't have a graphic dashboard plugin, I'd like to build some queries on otrs db (mysql) to show the results in Pentaho.

Problem: I don't know in which table data are.

Desired result: A graphic dahsboard that shows:

- how many tickets are in each queue

- how many tickets are locked and unlocked in each queue

- how many tickets have escalated per queue

- how many tickets per agent, per queue

- survey results

and other that my imagination can think about.

Does anyone know where I can get these information?

Thanks

--

André Luiz C. e Cavalcante, PMP, PRINCE2

ITS Manager

[Gerald Young]

MySQL Workbench or phpMyAdmin would be helpful tools for you.

The schema isn't terribly obscure.

For instance, the table named "ticket" holds ticket data. 

Do you know how to write sql queries? (I need a base of understanding -- whether you need direction to accomplish the task yourself or you need someone to do it for you).

For instance: 

number of tickets in each queue

select  q.name queue, count(t.id) number from ticket t left join queue q on q.id=t.queue_id left join ticket_state ts on ts.id = t.ticket_state_id group by q.name

number of tickets locked and unlocked in each queue:

select  q.name queue, sum(if(t.ticket_lock_id =1, 1, 0)) unlocked, sum(if(t.ticket_lock_id = 2, 1, 0)) locked from ticket t left join queue q on q.id=t.queue_id left join ticket_state ts on ts.id = t.ticket_state_id group by q.name

(yeah, you're not going to find *that* one easily in a list). 

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

[André Cavalcante]

I know the basic of doing queries, but I have people on my team that can do them for me. I just need to know where to find data.

I know phpmyadmin.

Coordenador de Eleições

TRE-BA / STI / COELE

[Friedrich Kölbel]

Am 16.04.2014 02:36, schrieb Gerald Young:

...

select  q.name queue, count(t.id) number from ticket t left join queue q on q.id=t.queue_id left join ticket_state ts on ts.id = t.ticket_state_id group by q.name

number of tickets locked and unlocked in each queue:

select  q.name queue, sum(if(t.ticket_lock_id =1, 1, 0)) unlocked, sum(if(t.ticket_lock_id = 2, 1, 0)) locked from ticket t left join queue q on q.id=t.queue_id left join ticket_state ts on ts.id = t.ticket_state_id group by q.name

(yeah, you're not going to find *that* one easily in a list). 

Thank you Gerald for sharing!
(this is also ment for many other answers you gave in this List)

regards Fritz

[Steve Clark]

On 04/15/2014 08:36 PM, Gerald Young wrote:

MySQL Workbench or phpMyAdmin would be helpful tools for you.

The schema isn't terribly obscure.

For instance, the table named "ticket" holds ticket data. 

Do you know how to write sql queries? (I need a base of understanding -- whether you need direction to accomplish the task yourself or you need someone to do it for you).

For instance: 

number of tickets in each queue

select  q.name queue, count(t.id) number from ticket t left join queue q on q.id=t.queue_id left join ticket_state ts on ts.id = t.ticket_state_id group by q.name

number of tickets locked and unlocked in each queue:

select  q.name queue, sum(if(t.ticket_lock_id =1, 1, 0)) unlocked, sum(if(t.ticket_lock_id = 2, 1, 0)) locked from ticket t left join queue q on q.id=t.queue_id left join ticket_state ts on ts.id = t.ticket_state_id group by q.name

Hi Gerald,

I am using postgresql 8.4 and the above query gives me errors at the (if( - is that only for mysql?

Thanks,
Steve

(yeah, you're not going to find *that* one easily in a list). 

On Tue, Apr 15, 2014 at 6:40 PM, André Cavalcante <treba...@gmail.com> wrote:

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

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

--
Stephen Clark
NetWolves Managed Services, LLC.
Director of Technology
Phone: 813-579-3200
Fax: 813-882-0209
Email: steve...@netwolves.com
http://www.netwolves.com

[Steve Clark]

On 04/17/2014 07:30 AM, Gerald Young wrote:

sum(case when t.ticket_lock_id = 1 then 1 else 0 end) as unlocked

select  q.name queue, sum(case when t.ticket_lock_id = 1 then 1 else 0 end) as unlocked, sum(case when t.ticket_lock_id = 2 then 1 else 0 end) as locked from ticket t left join queue q on q.id=t.queue_id left join ticket_state ts on ts.id = t.ticket_state_id group by q.name order by q.name;

Worked great,
Thanks!

[Gerald Young]

> how many tickets per agent, per queue

SELECT u.login user, sum(case when q.id = 2 then 1 else 0 end) as Raw, sum(case when q.id = 3 then 1 else 0 end) as Junk, sum(case when q.id = 4 then 1 else 0 end) as Misc FROM `ticket` t left join users u on u.id = t.user_id left join queue q on q.id = t.queue_id group by u.id

This will be a bit tedious to assemble, because it relies on specifying your individual list of queues:

This can help you generate:

SELECT CONCAT("SUM(CASE WHEN q.id=", id, " then 1 else 0 end) AS ", q.name, ", ") Query FROM `queue` q WHERE 1

Just remember the last entry before "FROM" must not have a comma.

On Tue, Apr 15, 2014 at 6:40 PM, André Cavalcante <treba...@gmail.com> wrote:

[Gerald Young]

I suppose you might want to count states per queue, right?

sum(case when q.id = 2 and t.state_id=1 then 1 else 0 end) as RawNew

sum(case when q.id = 2 and t.state_id=4 then 1 else 0 end) as RawOpen

You can do something similar with ticket_state_type instead for types instead of explicit states. 

You may also throw in locked/unlocked with t.ticket-lock_id (Raw Locked, Raw Unlocked)

Certainly, the possibilities abound at this point, but I think you get the idea. 

[Gerald Young]

sorry for the multi-post ... t.ticket_lock_id (underscore, not dash/minus)