Using SQL statements to query information from OTRS database

Posted on 15th February 2019

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.

OTRS SQL Box
SQL Box

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.

OTRS SQL Box
Executing SQL statements

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.


Post a comment

Comments

Alex | April 12, 2019 1:06 AM |

Hello. I want select ci classes by customer company. Thanks :)