osTicket v1.10 (Latest Release)
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?
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
but I dont know if it does what your looking for.
How good are you with
If you had one, would that enough for you then to be able to put this into a report format you are happy
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.
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.
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 :)
I'm curious what are types 'N', 'T', 'M' and 'R' from
What do they describe?
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.
, can you please define all types?
A - task
A - Task
N - Note
T - Ticket
M - Message
R - Response
What are statuses of tickets shown with numbers 1-6 ?
I'm asking for column
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. :)
Powered by Vanilla