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