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

Help with MySQL query to pull data for reports from osTicket 1.8.1

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 closed

If 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
Tagged:

Comments

  • 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 STATUS
    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.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.id
    Inner 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.id
    Inner Join ost_help_topic On ost_ticket.topic_id = ost_help_topic.topic_id


    To 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:00:00' AND '2014-02-25 23:59:59'

    WHERE ost_ticket.created >= '2013-02-25 00:00:00' and ost_ticket.closed <= curdate()

    WHERE ost_ticket.created >= '2013-02-25 00:00: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
  • 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.id
    Inner Join ost_help_topic On ost_ticket.topic_id = ost_help_topic.topic_id

Sign In or Register to comment.