Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

In this Discussion

osTicket v1.10 (stable) and Maintenance Release v1.9.15 are now available! Go get it now

report to show first reply to ticket by agent

Hi, is any way to get a report where we can find the first reply by a agent in a Ticket make by a user?

Comments

  • osTicket doesn't really have reporting (unless you count the Dashboard).  The dashboard is old and tired and really needs to be revamped.  I've been told that it is on the list of things to do.

    We use a 3rd party report plugin by @scottro but I dont know if it does what your looking for.
  • How good are you with sql queries ? If you had one, would that enough for you then to be able to put this into a report format you are happy with ?

    You would be looking for the minimum entry in the ost_thread_entry table which has a type of 'R'. This would need to be linked/joined to ost_thread and ost_ticket via matching ids.

    Cheers.





  • pcjkollmorgen: Yes, I could handle it based on a query, thanks
  • Ok, well this is a pretty big query however it does a little more than you are asking for. You should be able to reduce it back without too much trouble.

    SELECT TIC.number AS TicketNumber,CDATA.subject AS Subject,DEPT.name AS Department,USER.name AS UsersName,UACC.username AS UsersUsername,STAFF.firstname AS StaffFirstname,STAFF.lastname AS StaffSurname,STAFF.username AS StaffUsername,TIC.source AS Source,TIC.ip_address AS IPAddress,TIC.created AS Created,TIC.closed AS Closed,MIN(ENTRY.created) AS FirstResponse,TIMEDIFF(ENTRY.created,TIC.created) as ResponseTime,TIMEDIFF(TIC.closed,ENTRY.created) AS ServiceTime,ENTRY.created as FirstResponseDate, ENTRY.body as FirstResponseContents
    FROM `ost_ticket` TIC
    JOIN `ost_ticket__cdata` CDATA ON TIC.ticket_id=CDATA.ticket_id
    JOIN `ost_user` USER ON TIC.user_id=USER.id
    JOIN `ost_department` DEPT ON TIC.dept_id=DEPT.id
    JOIN `ost_staff` STAFF ON TIC.staff_id=STAFF.staff_id
    LEFT JOIN `ost_user_account` UACC ON TIC.user_id=UACC.user_id
    LEFT JOIN `ost_thread` THREAD ON THREAD.object_type = 'T' AND TIC.ticket_id=THREAD.object_id
    LEFT JOIN `ost_thread_entry` ENTRY on ENTRY.type = 'R' AND THREAD.id=ENTRY.thread_id
    WHERE TIC.closed IS NOT NULL
    AND ENTRY.id IS NOT NULL
    AND TIC.status_id = 3
    AND TIC.closed >= (DATE(NOW()) - INTERVAL 14 DAY)
    GROUP BY ENTRY.Thread_id
    ORDER BY DEPT.name, TIC.number DESC

    This query is looking for all tickets closed in the last 14 days, and pulling together all the details in the associated user, department, and staff tables in order to have those details. These are all joined by id numbers. 

    Next, it left joins tables including thread and thread_entries so you have every thread and every thread entry for each ticket. It then groups those and then using MIN(ENTRY.created) reduces this back to only the oldest thread entry which is the first response by an agent.

    You can likely tell from the other fields I was looking to create a report which lists all closed tickets, the time between the ticket opening and the first response by an agent (called response time here) and then the time between that and the closing date (called service time here).

    Unfortunately, we had too many tickets closed in which the agents never sent any email to the user so these tickets were not showing up on this report.

    I changed my approach to make the system record when a ticket is assigned (even if an auto assignment) and instead I generate a report which lists the time between ticket creation and ticket assignment as a response time, then the time between assignment and closing as the response time.

    Hopefully, my earlier query above is still of some use to you :)

    You should be able to edit the WHERE clause so it only looks for a specific user id and isn't restricted to a date range or only looking at closed tickets.

    Cheers.
  • you are the man!!!; the query works excellent, this is what i was looking, the additional info is very useful i will be adapting to the period the supervisor ask
    thanks a lot for your help :)


  • @pcjkollmorgen I'm curious what are types 'N', 'T', 'M' and 'R' from ost_thread and ost_thread_entry tables.

    What do they describe?
  • Note,
    Message,
    Response,
    Task.

    The different types of things in a thread.
  • Thank you very much!

    What is type A?

    Does there exist any other type which I missed?
  • A could be a field value "Answer".
  • You're welcome, LuisPG.

    I'm not sure on type A off hand, I'd have go digging. Certainly sounds like Grizly is correct or at least on the right track with that one.
  • Type 'A' is Task.
  • 'T' is actually Ticket.
  • @scottro, can you please define all types?

    A - task
    M - 
    N - 
    T - 
    R - 
  • A - Task
    N - Note
    T - Ticket
    M - Message
    R - Response
  • Thank you, @ntozier!
  • What are statuses of tickets shown with numbers 1-6 ?

    I'm asking for column status_id in table ost_ticket .
  • Oh, I already found:

    Those are Ticket statuses from table ost_ticket_status (osTicket --> Admin Panel --> Manage --> Lists --> [Ticket Statuses])
    1 - Open
    2 - Resolved
    3 - Closed
    4 - Archived
    5 - Deleted
    6 - Denied (my custom status)
  • The ticket statuses are not as static.  People can create their own statuses and make that list much much longer, but yes. :)
Sign In or Register to comment.