Use SQL explorer to make live reports

DB Connect 3.x.x provides a SQL Explorer interface to help you use dbxquery to query your database in real time. You can browse the database connections, catalogs, and schema, write and test SQL commands, and modify results with Splunk's SPL commands. You can use the resulting command in Splunk Enterprise searches to create reports, alerts, and dashboard panels.

Use SQL Explorer to build queries

You can reproduce the examples on this page in your test environment using a free tool such as MySQL Sakila sample database.

1. Navigate to Data Lab>SQL Explorer in DB Connect.
2. Select a Connection that you defined in create and manage database connections.
3. Browse to the desired Catalog, Schema, and Table from the type-ahead lists on the left. Refer to the basic query of the table you selected in the SQL Editor window.
4. Replace or customize the query by writing SQL in SQL Editor. Click Format SQL to make complex SQL statements easier to read. This is a one-way operation.
5. DB Connect generates the dbxquery command and displays it in the SPL Editor. If you want to further customize the command, write SPL in this window, then click Run to see the results.
6. Click Open in Search to copy the final dbxquery command to Splunk Search in a new Tab. Add visualizations, look for patterns, or save the query as a dashboard panel, alert, saved search, or report. Each use of these new elements generates a live query to the database. Review access controls and performance considerations before you deploy in production.

Use variables in dbxquery to build dashboard panels

DB Connect allows users to use variables in dbxquery to interactively query databases and create dashboards. You can use variables to enter an input, select multiple time pickers, or dynamically drill down. In the example below, create a dashboard form in which the user enters a first name into a DB Connect search from the sakila sample database.

1. Create a SQL statement by choosing Connection, Catalog, Schema or Table under Data Lab > SQL Explorer.

   SELECT * FROM `sakila`.`actor` WHERE first_name='PENELOPE'

   

2. DB Connect generates the dbxquery command and presents it in the SPL Editor. Edit the SPL if you wish, then click Open In Search.

   | dbxquery query="SELECT * FROM `sakila`.`actor` where first_name='PENELOPE'" connection="sh-mysql"

3. DB Connect generates the SPL and presents it in the Searching and Reporting app, where you can further edit the SPL or use visualizations.
4. Click Save As > Dashboard Panel > New Dashboard to create a dashboard. If you are not familiar with how to create and edit dashboards, see create dashboards for details.
5. Add a form element for the user to use. Click Edit > Add Input > Text to edit the Token. Set the input field name to First Name and the token to first_name in this form, then click Apply. See token usage in dashboards for further details.
   
6. Click Edit Search to edit the SPL, and replace where first_name='PENELOPE' with where first_name='$first_name$'. This variable name is the token name from your input form, bracketed with dollar signs. Click Apply to continue.
   
7. Test the form by entering 'RICHARD' in the First Name input field. The dashboard updates with the value you entered in this field.

Use dynamic variables in dashboard panels

Now, replace the First Name form with a drop down that allows the user to select from existing names in the table.

1. Return to SQL Explorer and edit the SQL statement to select a distinct list of first names from the actors table.

   SELECT DISTINCT first_name FROM `sakila`.`dbo`.`actor`

   Test the result, then Open in Search to copy the resulting dbxquery command.

2. Edit the dashboard again, then edit the input form element. Change the type from Text to Dropdown. Under Dynamic Options, set the "Search String" field to the dbxquery command for selecting a distinct list of names: | dbxquery query="SELECT DISTINCT first_name FROM `sakila`.`dbo`.`actor`" connection="sh-mysql". Set the "Field For Label" field to first_name, and set the "Field for Value" field to first_name. Click Apply. See token usage in dashboards for further details.
3. Test the form by selecting 'ANGELINA' from the dropdown input field. The dashboard updates with the value you entered in this field.

Limitations of time-based inputs

Splunk Enterprise dashboards and Splunk DB Connect support many types of variables in a dashboard, including string, numeric, and time values. The native Splunk Enterprise dashboard time selector is not always a good choice for dashboards that use DB Connect to perform time-based queries. The time selector allows users to select exact time representations (for instance,earliest=%m/%d/%Y:%H:%M:%) and relative time representations (for instance, [+|-]<time_integer><time_unit>) in the same form. These two types of WHERE conditions use different SQL which can cause SQL errors. To avoid errors, customize the form to limit available time input formats. See date and time format variables for details.