SQL Box

Note

This feature is not enabled by default. Activate the system configuration setting Frontend::Module###AdminSelectBox to enable this feature.

In a ticket system, it is usually possible to have statistics that show a summarized view of ticket information when needed. Sometimes, it is however required to access the database directly to have even more individual reports, allow external statistic tools to query information from the system or perform in-depth analysis of a ticket behavior.

Direct access to the database requires access to the command line which an administrator may not have. In addition to the username and password for the command line access, which is not given by all organizations, the username and password for the database are needed. These hurdles can prevent an administrator from using the database for more complex searches and operations.

OTRS offers application administrators the SQL Box in the graphical interface. It allows read access to the database. All results can be seen in the GUI or exported to CSV/Excel files.

Use this screen to query SQL statements in the system. The SQL box screen is available in the SQL Box module of the Administration group.

SQL Box Screen

SQL Box Screen

Query SQL statements

Note

The SQL statements entered here are sent directly to the application database. By default, it is not possible to change the content of the tables, only SELECT queries are allowed.

Warning

It is possible to modify the application database via SQL box. To do this, you have to enabled the system configuration setting AdminSelectBox::AllowDatabaseModification. Activate it to your own risk!

To execute an SQL statement:

  1. Enter the SQL statement into the SQL box.
  2. Select the result format.
  3. Click on the Run Query button.
SQL Box Widget

SQL Box Widget

SQL Settings

The following settings are available when adding or editing this resource. The fields marked with an asterisk are mandatory.

SQL *
The SQL statement to be queried.
Limit

Enter a number to limit the result to at most this number of rows. Leaving this field empty means there is no limit.

Note

Don’t use LIMIT inside the SQL statement. Always use this field to limit the number of results.

Result format

The format of the SQL statement result.

HTML
The results are visible below the SQL box in a new widget.
CSV
The results can be downloaded in comma separated plain text format.
Excel
The results can be downloaded as Microsoft Excel spreadsheet.

SQL Examples

To list some information about agents and output the results as HTML:

SELECT id, login , first_name, last_name, valid_id FROM users
SQL Box Result

SQL Box Result

To list all tables, you need to leave empty the Limit field and run the following query:

SHOW TABLES

To show the structure of the users table, you need to limit the results to 1 and run the following query (see the table header for the columns):

SELECT * FROM users