Report on ticket source?

[kerryrsmith]

Hi,

Is it possible to report on the ticket source (i.e. if it was created via email or web), so that I can show how many tickets were logged during a period by either method?

Cheers,
Kerry.

[adam]

Don't know if this is possible using the stats module, but I've been able to write an SQL query which apparently collates this data. It returns the first article for each ticket (on the assumption that the source of this article is the same as the ticket itself). Go to Admin -> SQL Box and enter the following:

Code: Select all

SELECT
ticket.id as "Ticket ID",
ticket.ticket_priority_id as "Ticket Priority",
ticket.create_time as "Ticket Created",
ticket.title as "Ticket Title",
article_type.name as "Ticket Source"

FROM
ticket
LEFT OUTER JOIN (SELECT ticket_id, min(id), article_type_id FROM article GROUP BY ticket_id) AS articleSource
       ON articleSource.ticket_id = ticket.id
LEFT OUTER JOIN article_type
       ON article_type.id = articleSource.article_type_id