OTRS, the Open Ticket Request System, is a GPL’ed open source request tracking application. Here’s a screenshot of the Queue View:
This is fine if you’re a CSR managing your own queue of tickets, but when you’re monitoring the activity of everyone’s tickets, the Queue View doesn’t help you quickly identify which tickets have had activity.
Fortunately, the Admin interface has a Select Box link under the Misc heading which provides you an ad-hoc SQL query screen. I took a look at the database schema for OTRS, and came up with a query that lists new and open tickets sorted by most recent activity:
SELECT q.name AS queue_name, t.tn, t.customer_id, t.title, a.id, a_t.name AS article_type_name, a.create_time, a.a_from FROM queue AS q, ticket AS t, article AS a, article_type AS a_t, ( SELECT _a.ticket_id, MAX(_a.create_time) AS create_time FROM ticket AS _t, ticket_state AS _t_s, article AS _a WHERE _t_s.name IN ('new', 'open') AND _t.ticket_state_id = _t_s.id AND _a.ticket_id = _t.id GROUP BY _a.ticket_id ) a_max WHERE q.id = t.queue_id AND t.id = a_max.ticket_id AND a.create_time = a_max.create_time AND a_t.id = a.article_type_id GROUP BY t.id ORDER BY a.create_time DESC
Here’s a screenshot of what the query results look like (I’ve sanitized out sensitive information):
The Select Box doesn’t display the column names as headings, but they are: Queue Name, Ticket#, Customer ID, Ticket Title, Article ID, Article Type, Article Create Time, Article From Header. The results are sorted in descending order by Article Create Time, so the most recently created articles appear at the top.
I can periodically refresh this page and execute the query to watch for new activity to pop up at the top of the list. This way, I can monitor activity and see new activity in a ticket, at a glance.
Do you use OTRS? Do you have any good tips or tricks that you’ve created? Share them with me in the comments below!