Hello, here is quick SQL query, you could make it better of course :)SELECT ost_ticket.number AS "Ticket Number", CONCAT(ost_staff.firstname," ", ost_staff.lastname) AS "Agents Name", CONCAT(FLOOR(HOUR(TIMEDIFF(ost_ticket.created, ost_ticket.lastupdate)) / 24), ' days ', MOD(HOUR(TIMEDIFF(ost_ticket.created, ost_ticket.lastupdate)), 24), ' hours ', MINUTE(TIMEDIFF(ost_ticket.created, ost_ticket.lastupdate)), ' minutes') AS "Created to Last Update time" FROM ost_ticket, ost_staff where ost_ticket.staff_id=ost_staff.staff_id;What does it do, it calculates difference between created time and last update, headers include: | Ticket Number | Agents Name      | Created to Last Update time |Please do note it does include time such as weekends, bank holidays, etc, thus take these values at face value, if you have support which works 24/7 then it is good enough. It also takes staff who is currently assigned to the ticket, it does not take into account hours spent by other agents.Without few extra VIEWS, complex`y queries, it will be hard to do what you want, e.g., calculate time spent per agent on one ticket.

I found this on git: https://github.com/osTicket/osTicket/pull/3231You might be interested in checking it out, maybe it will do exactly what you need :)

Hello @[deleted] without DB modifications it won't be possible to do complex calculations like that :) Other solution would be to do php modification + db mod (extra column for thread item) and add a field to ticket reply for agent to input time spent manually. There are other products which are used for time spent management, which were meant for this specific reason - to track time :) osTicket is a ticketing system a lot more than a management tool.

Oh, I misunderstood then @[deleted], if you have these fields already, then the only thing you is to modify the query I posted. You would need to link together this table (ost_thread_entry + ost_ticket) and most likely do a sum on total time spent per ticket, something like this:SELECT ost_ticket.number AS "Ticket Number", CONCAT(ost_staff.firstname," ", ost_staff.lastname) AS "Agents Name", SUM(ost_thread_entry.time_spent) AS "Time Spent" FROM ost_ticket, ost_staff, ost_thread_entry where ost_ticket.staff_id=ost_staff.staff_id AND ost_ticket.ticket_id=ost_thread_entry.thread_id GROUP BY ost_ticket.number;*Please do note that I do not have this mod, so in theory, based on what I saw on the GIT, you should have ost_thread_entry.time_spent, I added it and summed up for you, so it _should_ work, but no guarantees :)Thanks,

Hmm, looks like thread_id does not match ticket_id in the query, leave it with me and I will take a closer look at the query :) I will add subject too and time constrains.

Hello @[deleted]Sorry for the long wait, something came up :) But here you got the revised query, had few mistakes here and there, now it will actually show everything, well, at least it should SELECT ost_ticket.number AS "Ticket Number", ost_ticket__cdata.subject AS "Subject", CONCAT(ost_staff.firstname," ", ost_staff.lastname) AS "Agents Name", SUM(ost_thread_entry.time_spent) AS "Time Spent", CONCAT("https://your.osticket.com/scp/tickets.php?id=",ost_ticket.ticket_id) AS "Link" FROM ost_ticket LEFT JOIN ost_staff ON ost_ticket.staff_id=ost_staff.staff_id, ost_thread_entry, ost_thread, ost_ticket__cdata WHERE ost_ticket.ticket_id=ost_thread.object_id AND ost_thread.id=ost_thread_entry.thread_id AND ost_ticket.ticket_id=ost_ticket__cdata.ticket_id AND ost_ticket.created>='2017-01-01' AND ost_ticket.created<='2017-02-01' GROUP BY ost_ticket.ticket_id;The bold part controls the date range modifier. I have added another feature, it will give you html link, so you can view the ticket by clicking on it. Please do change the orange highlighted part to your osticket URL.

No problem @[deleted] :)SELECT ost_ticket.number AS "Ticket Number", ost_ticket__cdata.subject AS "Subject", ost_user.name AS "Created By", CONCAT(ost_staff.firstname," ", ost_staff.lastname) AS "Agents Name", SUM(ost_thread_entry.time_spent) AS "Time Spent", CONCAT("https://your.osticket.com/scp/tickets.php?id=",ost_ticket.ticket_id) AS "Link" FROM ost_ticket LEFT JOIN ost_staff ON ost_ticket.staff_id=ost_staff.staff_id, ost_thread_entry, ost_thread, ost_ticket__cdata, ost_user WHERE  ost_ticket.ticket_id=ost_thread.object_id AND ost_thread.id=ost_thread_entry.thread_id AND ost_ticket.ticket_id=ost_ticket__cdata.ticket_id AND ost_ticket.user_id=ost_user.id AND ost_ticket.created>='2017-01-01' AND ost_ticket.created<='2017-02-01' AND ost_user.name='Full Name' GROUP BY ost_ticket.ticket_id;This will add another column, Created By, if you wish to look for a particular user, then use AND ost_user.name='Full name'

Hello @[deleted], how did you delete the ticket? Because if you delete it using the facility, it removes the entry from the database table. You might want to add this then, but it is a wild shot really:SELECT ost_ticket.number AS "Ticket Number", ost_ticket__cdata.subject AS "Subject", ost_user.name AS "Created By", CONCAT(ost_staff.firstname," ", ost_staff.lastname) AS "Agents Name", SUM(ost_thread_entry.time_spent) AS "Time Spent", CONCAT("https://your.osticket.com/scp/tickets.php?id=",ost_ticket.ticket_id) AS "Link" FROM ost_ticket LEFT JOIN ost_staff ON ost_ticket.staff_id=ost_staff.staff_id, ost_thread_entry, ost_thread, ost_ticket__cdata, ost_user WHERE  ost_ticket.ticket_id=ost_thread.object_id AND ost_thread.id=ost_thread_entry.thread_id AND ost_ticket.ticket_id=ost_ticket__cdata.ticket_id AND ost_ticket.user_id=ost_user.id AND ost_ticket.created>='2017-01-01' AND ost_ticket.created<='2017-02-01' AND ost_user.name='Full Name' AND ost_ticket.status_id IN ('1','2') GROUP BY ost_ticket.ticket_id;Remove or add the extra number depending what you need:+----+----------------------+----------+| id | name                 | state    |+----+----------------------+----------+|  1 | Open                 | open     ||  2 | Resolved             | closed   ||  3 | Closed               | closed   ||  4 | Archived             | archived ||  5 | Deleted              | deleted  ||  6 | Faulty Unit returned | open     ||  7 | AR sent              | open     ||  8 | Monitoring           | open     ||  9 | Feature Request      | open     |+----+----------------------+----------+

@[deleted]I wanted to thank you for your help here as this was almost exactly what I was looking for. I was able to make some minor modifications to the query for my needs but I can't seem to figure out how to pull the organization name in as well. The whole join thing makes my head hurt. Any thoughts on how to get that info as well? And I guess while I'm asking do you know how to generate a link using the auth token so users without a login can view the tickets?

Hello @[deleted], you would need to add:SELECT ost_ticket.number AS "Ticket Number", ost_ticket__cdata.subject AS "Subject", ost_user.name AS "Created By", CONCAT(ost_staff.firstname," ", ost_staff.lastname) AS "Agents Name", SUM(ost_thread_entry.time_spent) AS "Time Spent", CONCAT("https://your.osticket.com/scp/tickets.php?id=",ost_ticket.ticket_id) AS "Link", ost_organization.name AS "Organisation" FROM ost_ticket LEFT JOIN ost_staff ON ost_ticket.staff_id=ost_staff.staff_id, ost_thread_entry, ost_thread, ost_ticket__cdata, ost_user LEFT JOIN ost_organization ON ost_user.org_id=ost_organization.id WHERE  ost_ticket.ticket_id=ost_thread.object_id AND ost_thread.id=ost_thread_entry.thread_id AND ost_ticket.ticket_id=ost_ticket__cdata.ticket_id AND ost_ticket.user_id=ost_user.id AND ost_ticket.created>='2017-01-01' AND ost_ticket.created<='2017-02-01' AND ost_user.name='Full Name' GROUP BY ost_ticket.ticket_id;Organisation table must be linked from ost_user.org_id, it links directly into ost_organization.id :) As for auth tokens, I am not sure, but I believe there was an addon(clickedy) that allowed public to view currently open tickets. That was for 1.7 version of osTicket, you would need to look for an updated version of it (if there is one).

Hello,@[deleted]I have modified this as below;SELECT ost_ticket.number AS "Ticket Number", ost_ticket__cdata.subject AS "Subject",ost_ticket.Source AS "Source",ost_user.name AS "Created By",ost_ticket.created AS "Ticket Opened", CONCAT(HOUR(TIMEDIFF(ost_ticket.created, ost_ticket.lastupdate)), ' Hours ', MINUTE(TIMEDIFF(ost_ticket.created, ost_ticket.lastupdate)), ' Minutes') AS "Created to Last Update time",  CONCAT(ost_staff.firstname," ", ost_staff.lastname) AS "Agents Name", CONCAT("http://ostticket//scp/tickets.php?id=",ost_ticket.ticket_id) AS "Link" FROM ost_ticket LEFT JOIN ost_staff ON ost_ticket.staff_id=ost_staff.staff_id, ost_thread_entry, ost_user, ost_thread, ost_ticket__cdata WHERE ost_ticket.ticket_id=ost_thread.object_id AND ost_thread.id=ost_thread_entry.thread_id AND ost_ticket.ticket_id=ost_ticket__cdata.ticket_id AND ost_ticket.user_id=ost_user.id AND ost_ticket.created>='2018-05-07' AND ost_ticket.created<='2018-06-07' GROUP BY ost_ticket.ticket_id;Is there a way to find the time between a state change? As some get put on hold but would still mean we're within the SLA etc. Some have it where user emails, we reply and place on hold, they never respond and we close 2 days later. This leads to about 53 hours "created to last update".Regards(If someone else wants this SQL, it basically provides number, subject, source, created by user, opened time, created to last update, agent name and link.

Ok, I think this should give a good view of when the ticket was replied too. This might help some people for better reports outside of the time spentSELECT ost_ticket.number AS "Ticket Number", ost_ticket__cdata.subject AS "Subject",ost_ticket.Source AS "Source",ost_user.name AS "Created By",ost_ticket.created AS "Created",ost_ticket.closed AS "Closed",CONCAT(HOUR(TIMEDIFF(ost_ticket.created, ost_thread_entry.created)),' Hours ',MINUTE(TIMEDIFF(ost_ticket.created,  ost_thread_entry.created)), ' Minutes') AS "Reponse time",CONCAT(ost_staff.firstname," ", ost_staff.lastname) AS "Agents Name",CONCAT("http://ost/scp/tickets.php?id=",ost_ticket.ticket_id) AS "Link"FROM ost_ticket LEFT JOIN ost_staff ON ost_ticket.staff_id=ost_staff.staff_id, ost_thread_entry, ost_user, ost_thread, ost_ticket__cdata WHERE ost_ticket.ticket_id=ost_thread.object_id AND ost_thread.id=ost_thread_entry.thread_id AND ost_ticket.ticket_id=ost_ticket__cdata.ticket_id AND ost_thread_entry.type like 'R' AND ost_ticket.user_id=ost_user.id AND ost_ticket.created>='2018-05-07' AND ost_ticket.created<='2018-06-07' GROUP BY ost_ticket.ticket_id;

Only issue with using `R` it will only show if they got a reponse, if they got closed on creation it will not