Re: [otrs] DB Tables

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