Re: [otrs] DB Tables

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 http://q.name queue, count(t.id http://t.id) number from ticket t left join queue q on q.id http://q.id=t.queue_id left join ticket_state ts on ts.id http://ts.id = t.ticket_state_id group by q.name http://q.name

number of tickets locked and unlocked in each queue:

select q.name http://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 http://q.id=t.queue_id left join ticket_state ts on ts.id http://ts.id = t.ticket_state_id group by q.name http://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 mailto: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

--------------------------------------------------------------------- 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.clark@netwolves.com http://www.netwolves.com