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. 2014-04-15 21:36 GMT-03:00 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).

On Tue, Apr 15, 2014 at 6:40 PM, André Cavalcante wrote:

...

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

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

-- *André Luiz C. e Cavalcante* Coordenador de Eleições TRE-BA / STI / COELE

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. On Thu, Apr 17, 2014 at 8:58 AM, Gerald Young wrote:

...

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: SELECThttp://192.168.1.90/pmyadmin/url.php?url=http%3A%2F%2Fdev.mysql.com%2Fdoc%2F... 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 wrote:

...

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

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