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
Programmer, husband, father, singer, guitarist, tinkerer, reverse engineer, strange, and living in New Jersey. 
I have implemented OTRS in my Office. I have need help!
Thanks
Davide: What kind of help do you need?
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.
Andres: Sorry, I haven’t done much else with OTRS since I no longer use it. I’d be happy to provide consulting and customization if you are interested, though.
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 ;)
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.
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?
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
I am a new with otrs, can someone help me how to get time the ticket was created to ticket closed?
Hi,
We not able to generate report for escallated tickets information. please help me.
Regards,
Krishna Rajesh
Hello, i have a problem.
I am tring to get the ho is the owner of the ticket or wich tickets are own by a X agent.
Anyone can help me with this, i am really new at this.