osTicket: Basic Ticket Report of All Tickets with Subject

scottalanmiller

There are many ways to make a report, this is a simple query that will allow you to view all tickets, or easily output to CSV to open in LibreOffice Calc or Excel and manipulate however you like. This is a generic, general purpose query. Not that ost_ is a default table prefix, yours easily will vary. Adjust accordingly.

SELECT DISTINCT ost_ticket.number AS Ticket_Number,
  ost_user.name AS Client,
  ost_staff.username AS Staff,
  ost_ticket.source AS Source,
  ost_ticket.created AS Date_Created,
  ost_ticket.closed AS Closed_Date,
  ost_ticket_status.name AS Status,
  ost_help_topic.topic AS Category,
  ost_ticket__cdata.subject AS Subject
FROM ost_ticket
  JOIN ost_staff ON ost_staff.staff_id = ost_ticket.staff_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
  JOIN ost_ticket_status ON ost_ticket.status_id = ost_ticket_status.id
  JOIN ost_ticket__cdata ON ost_ticket.ticket_id = ost_ticket__cdata.ticket_id
INNER JOIN ost_help_topic ON ost_ticket.topic_id = ost_help_topic.topic_id;

stacksofplates

I'm thinking of pairing Grafana with it to have usable reports.

dafyre

Or an open source reporting tool... I used to know a couple, but my brain is failing me at the moment.

scottalanmiller

Jasper?

dafyre

@scottalanmiller said in osTicket: Basic Ticket Report of All Tickets with Subject:

Jasper?

That's one of them, yeah.

bbigford

Since there is no inner or outter join specified, inner join is obviously being used where 'join' is specified (outter join having to be explicitly defined where an outter join is needed). How come you explicitly specify 'inner join' at the bottom when you could just use 'join' again as your inner join?

Obviously join and inner join accomplish the same thing, just curious why one is not explicitly defined and the other is.

scottalanmiller

@bbigford said in osTicket: Basic Ticket Report of All Tickets with Subject:

Since there is no inner or outter join specified, inner join is obviously being used where 'join' is specified (outter join having to be explicitly defined where an outter join is needed). How come you explicitly specify 'inner join' at the bottom when you could just use 'join' again as your inner join?

Obviously join and inner join accomplish the same thing, just curious why one is not explicitly defined and the other is.

I expanded on something done by someone from the osTicket team. it was some weird stylistic choice that they made and I just followed.