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 <crythias@gmail.com> 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:

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.andre@gmail.com> 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