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:

OTRS Queue View screenshot
(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):

OTRS tickets with recent activity query screenshot
(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: , ,

Comments

  1. Davide Rollo says:

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

    Thanks

  2. Davide: What kind of help do you need?

  3. Very good script I was looking for that for a long time. I have an OTRS 2.4.7 implementation since a couple of years ago. I would like to share our experiences with you.

    I would like to change the weekly stats in the principal panel but I don’t know how, did you have success with that?

    Thanks again for your script.

  4. Really interesting stuff, i’ll check out the DB to make my own scripts, i though that it was not possible to do ’till i saw your site.

    Tx 😉

  5. Based on your script is usefull to change the “customer_id” for “t.customer_user_id” that enable you to see the Customer name not his number.

    Just that and thanks for the job.

  6. I like the script example above. We’re trying to come up with a status report. Basically the report would show updates to the ticket during the last 7 days. Any ideas?

  7. Sanjay Prasad says:

    Hi..I m new to OTRS world, we have installed OTRS 3.1 in our office and I need provide feature to automatically send new tickets in QueueView as it categorized. I am providing unique subject in new tickets. how could I achieve it.

    Thx

  8. renzo vilo says:

    I am a new with otrs, can someone help me how to get time the ticket was created to ticket closed?

  9. Hi,
    We not able to generate report for escallated tickets information. please help me.

    Regards,
    Krishna Rajesh

Speak Your Mind

*