OTRS provides a GUI interface for administrators called SQL Box to run SQL statements on its database. The results of the queries can be displayed on the screen or exported to HTML, CSV or Excel file formats. To access SQL Box, you must be logged in as a user who is in the admin group.
Once logged in, click on the Admin tab and you will find SQL Box under the Administration section.
Click SQL Box, then enter SQL statement in the SQL box, select the result format and click Run Query.
You can also limit the number of rows in the result using the Limit field or leave it empty for no limit.
SQL Examples
Here are a a few SQL statements to query information about tickets, queues, agents and customers from OTRS database. It is worth noting that the OTRS database schema can be different for different versions of OTRS. The SQL statements below have been tested on OTRS version 6 only and are not guaranteed to run on other versions of OTRS.
To list all tables run the following SQL statement:
SHOW TABLES
You need to leave the Limit field empty when you run the above SQL statement.
To list all agents:
SELECT * FROM users
Table names are case sensitive and must be in lower case.
To show all customer users:
SELECT * FROM customer_user
To show all user groups:
SELECT * FROM groups
Show all users and the groups they belong to:
SELECT g.name, u.login, u.first_name, u.last_name FROM group_user gu INNER JOIN users u on u.id = gu.user_id INNER JOIN groups g on g.id = gu.group_id ORDER BY g.name
List all queues:
SELECT * FROM queue
Show tickets:
SELECT t.tn, t.title, q.name as queue, u.login as agent, ts.name as status, t.customer_id FROM ticket t LEFT JOIN queue q on t.queue_id=q.id LEFT JOIN users u on t.user_id = u.id LEFT JOIN ticket_state ts on ts.id = t.ticket_state_id
The above SQL statement returns ticket number, title, queue name, agent name, customer id and status of all available tickets. You can filter the results further down by adding a WHERE clause to this query.
Get details of all ticket articles:
The following query joins ticket table with article, article_data_mime, ticket_state and ticket_priority tables to get detailed information of the tickets.
SELECT t.queue_id, t.id as ticketid, a.id as articleid, t.tn, t.title, t.customer_user_id, ts.name as state, tp.name as priority, t.create_time, t.change_time, t.create_by, t.change_by, adm.a_from, adm.a_subject, adm.a_body FROM ticket t LEFT JOIN article a on t.id = a.ticket_id LEFT JOIN article_data_mime adm on a.id = adm.article_id LEFT JOIN ticket_state ts on t.ticket_state_id = ts.id LEFT JOIN ticket_priority tp on t.ticket_priority_id = tp.id
For more queries, leave us a comment below.
