Loading

Using ES|QL

Serverless Stack

Elasticsearch Query Language (ES|QL) helps you explore and analyze your Elasticsearch data directly in Discover, without a data view. ES|QL uses a piped syntax where you chain commands together to filter, transform, and aggregate data without needing to switch between different query interfaces. This tutorial walks you through querying sample data with ES|QL, from basic field selection to complex filtering and visualization.

  • The enableESQL setting enabled in Advanced Settings (enabled by default)
  • Have data in Elasticsearch. The examples on this page use the Kibana sample web logs to explore data and create visualizations. You can install sample data by following Add sample data.
Tip

For the complete ES|QL documentation, including all supported commands, functions, and operators, refer to the ES|QL reference. For a more detailed overview of ES|QL in Kibana, refer to Use ES|QL in Kibana.

To load the sample data:

  1. Go to Discover.

  2. Select Try ES|QL from the application menu bar.

    Tip

    If you've entered a KQL or Lucene query in the default mode of Discover, it automatically converts to ES|QL.

    Let’s say we want to find out what operating system users have and how much RAM is on their machine.

  3. Set the time range to Last 7 days.

  4. Copy the following query. To make queries more readable, you can put each processing command on a new line.

    FROM kibana_sample_data_logs
| KEEP machine.os, machine.ram
    1. We're specifically looking for data from the sample web logs we installed.
    2. We’re only keeping the machine.os and machine.ram fields in the results table.
    Note

    ES|QL keywords are not case sensitive.

  5. Click ▶Run. An image of the query result

Let’s add geo.dest to our query to find out the geographical destination of the visits and limit the results.

  1. Copy the query below:

    FROM kibana_sample_data_logs
| KEEP machine.os, machine.ram, geo.dest
| LIMIT 10

  2. Click ▶Run again. You can notice that the table is now limited to 10 results. The visualization also updated automatically based on the query, and broke down the data for you.

    Note

    When you don’t specify any specific fields to retain using KEEP, the visualization isn’t broken down automatically. Instead, an additional option appears above the visualization and lets you select a field manually.

    An image of the extended query result

We will now take it a step further to sort the data by machine ram and filter out the GB destination.

  1. Copy the query below:

    FROM kibana_sample_data_logs
| KEEP machine.os, machine.ram, geo.dest
| SORT machine.ram desc
| WHERE geo.dest != "GB"
| LIMIT 10

  2. Click ▶Run again. The table and visualization no longer show results for which the geo.dest field value is "GB", and the results are now sorted in descending order in the table based on the machine.ram field.

    An image of the full query result

  3. Click Save to save the query and visualization to a dashboard.

Edit the ES|QL visualization

You can make changes to the visualization by clicking the pencil icon. This opens additional settings that let you adjust the chart type, axes, breakdown, colors, and information displayed to your liking. If you’re not sure which route to go, check one of the suggestions available in the visualization editor.

If you’d like to keep the visualization and add it to a dashboard, you can save it using the floppy disk icon.

ES|QL and time series data

By default, ES|QL identifies time series data when an index contains a @timestamp field. This enables the time range selector and visualization options for your query.

If your index doesn’t have an explicit @timestamp field, but has a different time field, you can still enable the time range selector and visualization options by calling the ?_tstart and ?_tend parameters in your query.

For example, the eCommerce sample data set doesn’t have a @timestamp field, but has an order_date field.

By default, when querying this data set, time series capabilities aren’t active. No visualization is generated and the time picker is disabled.

FROM kibana_sample_data_ecommerce
| KEEP customer_first_name, email, products._id.keyword
ESQL query without time series capabilities enabled

While still querying the same data set, by adding the ?_tstart and ?_tend parameters based on the order_date field, Discover enables times series capabilities.

FROM kibana_sample_data_ecommerce
| WHERE order_date >= ?_tstart and order_date <= ?_tend
ESQL query with a custom time field enabled

Serverless Preview Stack Preview 9.2.0

In Discover, LOOKUP JOIN commands include interactive options that let you create or edit lookup indices directly from the editor.

You can create a lookup index directly from the ES|QL editor. To populate this index, you can type in data manually or upload a CSV file up to 500 MB.

To create lookup indices, you need the create_index Elasticsearch privilege on the corresponding pattern.

  1. In your ES|QL query, add a LOOKUP JOIN command. For example:

    FROM kibana_sample_data_logs
| LOOKUP JOIN

    Add a space after the command. The editor suggests existing lookup indices and offers to create one. You can also type an index name in your query. If it doesn't exist, the editor suggests to create it.

  2. Select the Create lookup index suggestion that appears in the autocomplete menu.

  3. Define a name for the lookup index, then validate it.

    • It must not contain spaces nor any of the following characters: \, /, *, ?, <, >, |, :, and #.
    • It must not start with -, _, or +.

  4. Provide the data of the lookup index. You can choose between:

    • Uploading a CSV file up to 500 MB. When uploading a file, you can preview the data and inspect the file's content before it is imported. If issues are detected, a File issues tab with more details also appears before you validate the import.
    • Adding data manually. To do that, you can add rows and columns, and edit cells directly.
    • Using a combination of both methods. You can upload a file after adding data manually, and edit or expand data imported from a file.
    Tip

    You can explore your index using the search field, or in a new Discover session by selecting Open in Discover. If you choose to open it in Discover, a new browser tab opens with a prefilled ES|QL query on the index.

  5. Save any unsaved changes, then Close the index editor to return to your query.

Your new index is automatically added to your query. You can then specify the field to join using ON <field_to_join>.

You can view and modify existing lookup indices referenced in an ES|QL query directly from the editor, depending on your privileges:

  • To edit lookup indices, you need the write Elasticsearch privilege.
  • To view lookup indices in read-only mode, you need the view_index_metadata Elasticsearch privilege.

To view or edit an index:

  1. In the ES|QL query, hover over the lookup index name.

  2. Select the Edit lookup index or View lookup index option that appears. A flyout showing the index appears.

  3. Depending on your permissions and needs, explore or edit the index.

    Note

    Editing a lookup index affects all ES|QL queries that reference it. Make sure that your changes are compatible with existing queries that use this index.

  4. If you made changes, select Save before closing the flyout.

Serverless Preview Stack Preview 9.2.0

Variable controls help you make your queries more dynamic instead of having to maintain several versions of almost identical queries.

Variable control in Discover

You can add them from your Discover ES|QL query.

  1. While you edit your ES|QL query, the autocomplete menu suggests adding a control when relevant or when you type ? in the query. Select Create control.

    ES|QL query prompting to add a control

  2. A menu opens to let you configure the control. This is where you can specify:

    • The type of the control.
      • For controls with Static values, enter available controls manually or select them from the dropdown list.
      • For controls with Values from a query, write an ES|QL query to populate the list of options.
    • The name of the control. You use this name to reference the control in ES|QL queries.
      • Start the name with ? if you want the options to be simple static values.
      • Start the name with ?? if you want the options to be fields or functions. Stack 9.1.0
    • The values users can select for this control. You can add multiple values from suggested fields, or type in custom values. If you selected Values from a query, you must instead write an ES|QL query at this step.
    • The label of the control. This is the label displayed in Discover or in the dashboard.
    • The width of the control.
    • Whether the control should allow selecting a single value or multiple values. This requires using the MV_CONTAINS function in your query. Stack Planned Serverless Preview

    ES|QL control settings

  3. Save the control.

The variable is inserted into your query, and the control appears.

Examples

  • Integrate filtering into your ES|QL experience

    | WHERE field == ?value

  • Fields in controls for dynamic group by

    | STATS count=COUNT(*) BY ??field

  • Variable time ranges? Bind function configuration settings to a control

    | BUCKET(@timestamp, ?interval),

  • Make the function itself dynamic

    | STATS metric = ??function

Serverless Preview Stack Planned

You can create controls that let users select multiple values. To do that:

  1. Add the MV_CONTAINS function to your query, and create a variable as one of its parameters. For example:

    FROM logs-* | WHERE MV_CONTAINS(?values, field)
    Note

    Multi-selection is only available for ?values variables. It is not available for ??fields and ??functions variables.

  2. When defining the control, select the Allow multiple selections option.

  3. Save the control.

The newly configured control becomes available and allows users to select multiple values.

Once a control is active for your query, you can still edit it by hovering over it and by selecting the Edit option that appears.

You can edit all of the options described in Using ES|QL > Add variable controls to your Discover queries.

When you save your edits, the control is updated for your query.

To add the results of your Discover explorations to a dashboard in a way that preserves the controls created from Discover and also adds them to the dashboard, you have two methods:

Method 1: Adding the Discover session's results

This method allows you to add the result table of your Discover ES|QL query to any dashboard.

  1. Save the ES|QL query containing the variable control into a Discover session. If your Discover session contains several tabs, only the first tab will be imported to the dashboard.

  2. Go to Dashboards and open or create one.

  3. Select Add, then From library.

  4. Find and select the Discover session you saved earlier.

A new panel appears on the dashboard with the results of the query along with any attached controls.

Importing Discover session with controls into a dashboard

Method 2: Adding the Discover visualization Serverless Stack Planned

This method allows you to add the visualization of your Discover ES|QL query to any dashboard.

  1. Next to the Discover visualization, select Save visualization.

    Importing Discover visualization with controls into a dashboard

  2. Select the dashboard to add the visualization to. You can choose an existing dashboard or create one.

The selected dashboard opens. It now includes a new panel that shows the visualization imported from Discover. Existing controls from the initial query in Discover are also added. You can find them at the top of the dashboard.