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.


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.

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:


    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, u.login, u.first_name, u.last_name FROM group_user gu
    INNER JOIN users u on = gu.user_id
    INNER JOIN groups g on = gu.group_id
  • List all queues:

    SELECT * FROM queue
  • Show tickets:

    SELECT, t.title, as queue, u.login as agent, as status, t.customer_id 
    FROM ticket t  
    LEFT JOIN queue q on  
    LEFT JOIN users u on t.user_id = 
    LEFT JOIN ticket_state ts on = 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, as ticketid, as articleid,, t.title, t.customer_user_id, as state, 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 = a.ticket_id
    LEFT JOIN article_data_mime adm on = adm.article_id
    LEFT JOIN ticket_state ts on t.ticket_state_id =
    LEFT JOIN ticket_priority tp on t.ticket_priority_id = 

For more queries, leave us a comment below.

Post a comment


Vinicius Cavalcanti | December 3, 2020 5:50 PM |

Wich queue services registered self designate?

Robert Weißenberg | November 12, 2020 9:43 AM |

Thank you for this article! I've migrated from OTRS to Jira with your help. I'll upload a short documentation on Github.

daniel | October 25, 2020 2:52 PM |

Hello. I want a report that brings me the list of calls by call number, state, SLA, Queue and the business time of each state in which state it remained. Example: Called 1 - first iteration time, 5 min open, 3 minutes pending, 6 minutes running,

Daniel Carvalho Resende | October 1, 2020 12:23 AM |

Sample query sql otrs

Sachin Gorivale | February 7, 2020 6:48 AM |

Report fetching query require . Kindly provide

Jose Emmanuell Villa Lopez | November 5, 2019 4:26 PM |

Hola requiero traer la informacion del cliente y notas realizadas

Jose Emmanuell Villa Lopez | November 27, 2019 10:09 PM |

Hola tendran el query que hay que ejecutar para traer informacion de notas realizadas e informacion del cliente???

Alex | April 12, 2019 1:06 AM |

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