Hi there,I am happy to have discovered osTicket and I am about to introduce it to my IT dept and another non-IT dept in my organization.  I am running osTicket 1.8.1.   One important usage is for managers to generate reports from the MySQL data generated by osTicket.  I know there's a MOD out there for reports but I am wanting to know if someone has experience with queries from osTicket that would be appropriate.    I basically want to run queries to pull "report" data and dump it into a csv/Excel file to make it "pretty".For example, I would probably want to pull data for tickets for a given date range (start/end date) including: *ticket number*date of ticket creation*department (assigned ticket)*user/customer name (who submitted ticket)*help topic*title of ticket*body of ticket*staff member (who closed the ticket)*date/time when ticket closedIf any can point me to some queries to get me started I would appreciate it greatly :).  I am a novice with MySQL but I think if someone could give some examples I could tweak them to suit my needs.TIA and cheers,Chris

Hy hope it can help you :Select Distinct ost_ticket.ticketID As TicketNumber,  ost_user.name As Client,  ost_ticket.ip_address As IP,  ost_department.dept_name As Departament,  ost_staff.username As Staff,  ost_ticket.source As Source,  ost_ticket.created As DataOn,  ost_ticket.duedate As DueDate,  ost_ticket.closed As ClosedDate,  ost_ticket.status As STATUSFrom ost_ticket  Join ost_staff On ost_staff.staff_id = ost_ticket.staff_id  Join ost_department On ost_ticket.dept_id = ost_department.dept_id  Join ost_user_email On ost_ticket.user_id = ost_user_email.user_id  Join ost_user On ost_user_email.id = ost_user.id

I forgot to add Help topic ;) Select Distinct ost_ticket.ticketID As TicketNumber,  ost_user.name As Client,  ost_ticket.ip_address As IP,  ost_department.dept_name As Departament,  ost_staff.username As Staff,  ost_ticket.source As Source,  ost_ticket.created As DataOn,  ost_ticket.duedate As DueDate,  ost_ticket.closed As ClosedDate,  ost_ticket.status As STATUS,  ost_help_topic.topic  From ost_ticket  Join ost_staff On ost_staff.staff_id = ost_ticket.staff_id  Join ost_department On ost_ticket.dept_id = ost_department.dept_id  Join ost_user_email On ost_ticket.user_id = ost_user_email.user_id  Join ost_user On ost_user_email.id = ost_user.idInner Join ost_help_topic On ost_ticket.topic_id = ost_help_topic.topic_id

In osTicket 1.8.1 there is no ticketID column in ost_ticket.There is 'ticket_id' or 'number'.ticket_id is the internal reference ticket id number.number is the external ticket id number that clients see (and staff see).I presume that you would want to use:Select Distinct ost_ticket.number As TicketNumber,  ost_user.name As Client,  ost_ticket.ip_address As IP,  ost_department.dept_name As Departament,  ost_staff.username As Staff,  ost_ticket.source As Source,  ost_ticket.created As DataOn,  ost_ticket.duedate As DueDate,  ost_ticket.closed As ClosedDate,  ost_ticket.status As STATUS,  ost_help_topic.topic  From ost_ticket  Join ost_staff On ost_staff.staff_id = ost_ticket.staff_id  Join ost_department On ost_ticket.dept_id = ost_department.dept_id  Join ost_user_email On ost_ticket.user_id = ost_user_email.user_id  Join ost_user On ost_user_email.id = ost_user.idInner Join ost_help_topic On ost_ticket.topic_id = ost_help_topic.topic_idTo select portions of the data set (like between date ranges) you could further add a WHERE statement.example:WHERE ost_ticket.created BETWEEN '2013-02-25 00' AND '2014-02-25 23'WHERE ost_ticket.created >= '2013-02-25 00' and ost_ticket.closed <= curdate()WHERE ost_ticket.created >= '2013-02-25 00' and ost_ticket.created <= now()

Thanks a lot, alexz0ne!  That is very helpfu!  I tried out the query and the result only shows assigned ticket info (not unassigned tickets). Could you tell me how I would modify the query to show all tickets with same fields regardless of assigned or unassigned?  And how would I limit the date range :)?Thanks again!!!-Chris

All tickets are assigned to the person that closes them.My response indicates how to limit the date range.

Thanks for the updated query and the WHERE statements to limit the date ranges.I will probably also need to see all tickets with the same fields (as above) that were entered into osTicket for a date range regardless of status, or regardless if assigned or not.  The query only shows assigned ticket data right now.  Can you tell me what I would change?Thanks,Chris

3 years later

Hi Everyone,The Latest Modifications for osTicket-v1.10 are as follows:Select Distinct ost_ticket.ticket_id As TicketNumber,  ost_user.name As Client,  ost_ticket.ip_address As IP,  ost_department.name As Departament,  ost_staff.username As Staff,  ost_ticket.source As Source,  ost_ticket.created As DataOn,  ost_ticket.duedate As DueDate,  ost_ticket.closed As ClosedDate,  ost_ticket.status_id As STATUS,  ost_help_topic.topic  From ost_ticket  Join ost_staff On ost_staff.staff_id = ost_ticket.staff_id  Join ost_department On ost_ticket.dept_id = ost_department.id  Join ost_user_email On ost_ticket.user_id = ost_user_email.user_id  Join ost_user On ost_user_email.id = ost_user.idInner Join ost_help_topic On ost_ticket.topic_id = ost_help_topic.topic_id

Write a Reply...