Hi there,
I'm trying to calculate the "first ticket response time." That is, how long it takes to for an agent to respond to a ticket after it's created. I've been using osTicket for almost a year, but I'm relatively new to trying to query data from the database itself.

Basically what I'd like to do is take the date-time when the agent first responds and subtract that from the created data-time, and voila, I should have the first ticket response time.

I just can't seem to figure out how to link or relate the ticket to the thread. By checking a few tickets, I've found that the thread_id seems to be the ticket_id plus 5, but that seems weird, so I thought I'd check here.

Thanks,

Nick

I think that software-mods reports plugin can do that. (its not free)

Otherwise you would have to use the ticket id and ticket thread and ost_ticket.created field to calculate the time between the created date and the first answer in the thread table.

    Hi ntozier,

    Unfortunately, the Reports plugin cannot do it. I own that plugin and that is not one of the available reports, though Scott does seem to be constantly making updates to the plugin.

    So, with regards to the calculation, I understand the concept of how to do it, I'm just wondering if anyone knows the relationship between tickets and threads. It seems logical that the thread_id and the ticket_id would be the same, but on my system, they're not. Based on a very brief investigation of tickets, it seems that the thread_id = ticket_id + 5. Is that normal and expected?

    Thanks,

    Nick

    So, this query seems to work for me, but I still am unsure why the thread_id and ticket_id are different and if it will always be an offset of 5. The offset of 5 seems arbitrary so I assume that there's some other way to link the two tables correctly.

    SELECT number, ResponseTime, TicketCreateTime, round(time_to_sec(timediff(ResponseTime, TicketCreateTime))/60,2) AS FRMins
FROM
(SELECT TE.id, T.number, T.ticket_id, TE.thread_id, TE.pid, T.created AS TicketCreateTime, TE.created AS ResponseTime, TE.type, TE.staff_id
FROM ost_ticket T
INNER JOIN ost_thread_entry TE ON (T.ticket_id + 5) = TE.thread_id
WHERE T.created >= date('2019-05-31') AND TE.type = 'R'	AND TE.id IN (SELECT min(id) FROM ost_thread_entry WHERE type = 'R' GROUP BY thread_id))AS FTRT_tbl

    ntozier ,

    Do you have any input on this? My query above seems to work for current tickets, but fails for older tickets.

    How does osTicket link threads to tickets?

    Thanks,

    Nick

      3 years later

      ost_thread.ticket_id = ost_ticket.ticket_id
      and to see each entry in a thread, ost_thread_entry.thread_id = ost_thread.id

      Write a Reply...