First Response of each ticket.

[cbravo]

Is there any way to create an statistic that can let you get the first response of each ticket? I need the first response an agent made to each of our customers. I've been looking at the options, but can't find anything related to responses. If I have to create a query I don't mind, but I would very much appreciate if you guys could guide me.

Also, is there a database design or something like that? So that everytime I need something like this I could use it to guide myself.

Appreciate it very much.

Greetings,

[jojo]

do you need the repsonse (not possible) or the response time?

[cbravo]

I need the response. I can't get it even by a query?

Greetings,

[crythias]

clear the limit in sqlbox and
SELECT id,name FROM article_type to see what makes sense for "response" to you. (email-external?) (note the id number)
SELECT id,name FROM article_sender_type (agent) (note the id number)

This is every email-external article relating to tickets created by a given agent.
SELECT a_from,a_to,a_cc,a_subject,a_body FROM article LEFT JOIN users ON users.id=article.create_by where article.article_type_id="(email-external id)" AND article.article_sender_type_id="(typeIsAgent id)" AND users.login="agentlogin" GROUP BY ticket_id

After this, you may want to read something like http://code.openark.org/blog/mysql/sql- ... -per-group

[crythias]

is there a database design or something like that

check your doc directory for OTRSDatabaseDiagram.png.

http://source.otrs.org/viewvc.cgi/otrs/ ... iew=markup

the mwb is MySQL Workbench file which is incidentally a .zip file containing
prompt>unzip OTRSDatabaseDiagram.mwb
Archive: OTRSDatabaseDiagram.mwb
MySQL Workbench Model archive 1.0
inflating: document.mwb.xml
inflating: lock
inflating: @db/data.db
inflating: @scripts/1

lock, data.db, and the file named '1' are basically useless if you want to eyeball them for data structure, but the document.mwb.xml file contains the main connectivity information if you wanted to use something besides MySQL Workbench for observing data structure.

[artjoms15]

Of course if the first response changes the state of the ticket from NEW to something else, then you can try to parse ticket_history table to get the first article which has state different from the state NEW, like:

Code: Select all

SELECT ticket.tn, queue.name, CONCAT(users.first_name,' ',users.last_name), a.responsetime 
from 
(SELECT th1.ticket_id, MIN(th1.create_time) as responsetime 
from ticket_history th1 
WHERE th1.state_id NOT IN 
(SELECT ticket_state.id from ticket_state where ticket_state.name = 'new')) as a
 INNER JOIN ticket on ticket.id = a.ticket_id 
INNER JOIN queue on ticket.queue_id = queue.id 
INNER JOIN users on ticket.user_id = users.id 
INNER JOIN ticket_type on ticket.type_id=ticket_type.id 
where ticket.create_time>=? AND ticket.create_time<=?

it takes the first article of a ticket with different state than NEW and adds all the required info from other tables. I set this report as a Static Statistics module, so there are some more changeable parameters, like queue, create time, agentnames etc, but the core sql statement looks like this

[Sophy978]

do you need the repsonse (not possible) or the response time?

Hello, this is exactly what I need, the Response Time that is. Is there a query already available? That would be awesome, thanks.

[crythias]

I tried ... viewtopic.php?f=53&t=19781#p77773

[Sophy978]

Thanks Crythias