Dossy's Blog: OTRS SQL query to show most recent ticket activity

OTRS SQL query to show most recent ticket activity

OTRS, the Open Ticket Request System, is a GPL’ed open source request tracking application. Here’s a screenshot of the Queue View:

(click for full size)

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):

(click for full size)

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!

Tags: OTRS, Open Ticket Request System, SQL

This entry was posted by Dossy Shiobara on Saturday, September 15th, 2007 at 5:51 pm and is filed under Geeking out, Open Source. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

No Responses to “OTRS SQL query to show most recent ticket activity”

Davide Rollo says:

September 25, 2007 at 6:11 am

I have implemented OTRS in my Office. I have need help!

Thanks

Reply
Dossy says:

September 25, 2007 at 11:54 am

Davide: What kind of help do you need?

Reply