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