Note
This feature is not enabled by default. Activate the system configuration setting Frontend::Module###AdminSelectBox to enable this feature.
Note
This feature is only available to On-Premise customers. If you are a Managed customer, this feature is taken care of by the Customer Solutions Team in OTRS. Please contact us via support@otrs.com or in the OTRS Portal.
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.
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:
-
Enter the SQL statement into the SQL box.
-
Select the result format.
-
Click on the Run Query button.
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
LIMITinside 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
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
