Using ES|QL in Kibana
editUsing ES|QL in Kibanaedit
You can use ES|QL in Kibana to query and aggregate your data, create visualizations, and set up alerts.
This guide shows you how to use ES|QL in Kibana. To follow along with the queries, load the "Sample web logs" sample data set by clicking Try sample data from the Kibana Home, selecting Other sample data sets, and clicking Add data on the Sample web logs card.
Enable or disable ES|QLedit
ES|QL is enabled by default in Kibana. It can be
disabled using the enableESQL
setting from the
Advanced Settings.
This will hide the ES|QL user interface from various applications. However, users will be able to access existing ES|QL artifacts like saved searches and visualizations.
Get started with ES|QLedit
To get started with ES|QL in Discover, open the main menu and select Discover. Next, from the Data views menu, select Language: ES|QL.
![esql data view menu](images/esql/esql-data-view-menu.png)
The query baredit
After switching to ES|QL mode, the query bar shows a sample query. For example:
from kibana_sample_data_logs | limit 10
Every query starts with a source command. In this query, the
source command is FROM
. FROM
retrieves data from data streams, indices, or
aliases. In this example, the data is retrieved from kibana_sample_data_logs
.
A source command can be followed by one or more processing
commands. In this query, the processing command is LIMIT
. LIMIT
limits the number of rows that are retrieved.
Click the help icon () to open the
in-product reference documentation for all commands and functions.
To make it easier to write queries, auto-complete offers suggestions with possible commands and functions:
![esql kibana auto complete](images/esql/esql-kibana-auto-complete.png)
ES|QL keywords are case-insensitive. The following query is identical to the previous one:
FROM kibana_sample_data_logs | LIMIT 10
Expand the query baredit
For readability, you can put each processing command on a new line. The following query is identical to the previous one:
FROM kibana_sample_data_logs | LIMIT 10
To make it easier to write multi-line queries, click the double-headed arrow
button () to expand the query
bar:
![esql expanded query bar](images/esql/esql-expanded-query-bar.png)
To return to a compact query bar, click the minimize editor button
().
Warningsedit
A query may result in warnings, for example when querying an unsupported field type. When that happens, a warning symbol is shown in the query bar. To see the detailed warning, expand the query bar, and click warnings.
Query historyedit
You can reuse your recent ES|QL queries in the query bar. In the query bar click Show recent queries:
![esql discover show recent query](images/esql/esql-discover-show-recent-query.png)
You can then scroll through your recent queries:
![esql discover query history](images/esql/esql-discover-query-history.png)
The results tableedit
For the example query, the results table shows 10 rows. Omitting the LIMIT
command, the results table defaults to up to 1000 rows. Using LIMIT
, you can
increase the limit to up to 10,000 rows.
the 10,000 row limit only applies to the number of rows that are retrieved by the query and displayed in Discover. Any query or aggregation runs on the full data set.
Each row shows two columns for the example query: a column with the @timestamp
field and a column with the full document. To display specific fields from the
documents, use the KEEP
command:
FROM kibana_sample_data_logs | KEEP @timestamp, bytes, geo.dest
To display all fields as separate columns, use KEEP *
:
FROM kibana_sample_data_logs | KEEP *
The maximum number of columns in Discover is 50. If a query returns more than 50 columns, Discover only shows the first 50.
Sortingedit
To sort on one of the columns, click the column name you want to sort on and
select the sort order. Note that this performs client-side sorting. It only
sorts the rows that were retrieved by the query, which may not be the full
dataset because of the (implicit) limit. To sort the full data set, use the
SORT
command:
FROM kibana_sample_data_logs | KEEP @timestamp, bytes, geo.dest | SORT bytes DESC
Time filteringedit
To display data within a specified time range, use the
time filter. The time filter is only enabled
when the indices you’re querying have a field called @timestamp
.
If your indices do not have a timestamp field called @timestamp
, you can limit
the time range using the WHERE
command and the NOW
function.
For example, if the timestamp field is called timestamp
, to query the last 15
minutes of data:
FROM kibana_sample_data_logs | WHERE timestamp > NOW() - 15minutes
Analyze and visualize dataedit
Between the query bar and the results table, Discover shows a date histogram
visualization. If the indices you’re querying do not contain a @timestamp
field, the histogram is not shown.
The visualization adapts to the query. A query’s nature determines the type of visualization. For example, this query aggregates the total number of bytes per destination country:
FROM kibana_sample_data_logs | STATS total_bytes = SUM(bytes) BY geo.dest | SORT total_bytes DESC | LIMIT 3
The resulting visualization is a bar chart showing the top 3 countries:
![esql kibana bar chart](images/esql/esql-kibana-bar-chart.png)
To make changes to the visualization, like changing the visualization type, axes and colors, click the
pencil button (). This opens
an in-line editor:
![esql kibana in line editor](images/esql/esql-kibana-in-line-editor.png)
You can save the visualization to a new or existing dashboard by clicking the
save button (). Once saved
to a dashboard, you’ll be taken to the Dashboards page. You can continue to
make changes to the visualization. Click the
options button in the top-right (
) and
select Edit ESQL visualization to open the in-line editor:
![esql kibana edit on dashboard](images/esql/esql-kibana-edit-on-dashboard.png)
Add a panel to a dashboardedit
You can use ES|QL queries to create panels on your dashboards. To add a panel to a dashboard, under Dashboards, click the Add panel button and select ES|QL.
![esql dashboard panel](images/esql/esql-dashboard-panel.png)
Check the ES|QL query by clicking the Panel filters button ():
![esql dashboard panel query](images/esql/esql-dashboard-panel-query.png)
You can also edit the ES|QL visualization from here.
Click the options button in the top-right () and
select Edit ESQL visualization to open the in-line editor.
![esql dashboard panel edit visualization](images/esql/esql-dashboard-panel-edit-visualization.png)
Create an enrich policyedit
The ES|QL ENRICH
command enables you to enrich
your query dataset with fields from another dataset. Before you can use
ENRICH
, you need to create and execute an enrich
policy. If a policy exists, it will be suggested by auto-complete. If not,
click Click to create to create one.
![esql kibana enrich autocomplete](images/esql/esql-kibana-enrich-autocomplete.png)
Next, you can enter a policy name, the policy type, source indices, and optionally a query:
![esql kibana enrich step 1](images/esql/esql-kibana-enrich-step-1.png)
Click Next to select the match field and enrich fields:
![esql kibana enrich step 2](images/esql/esql-kibana-enrich-step-2.png)
Finally, click Create and execute.
Now, you can use the enrich policy in an ES|QL query:
FROM kibana_sample_data_logs | STATS total_bytes = SUM(bytes) BY geo.dest | SORT total_bytes DESC | LIMIT 3 | ENRICH countries
Create an alerting ruleedit
You can use ES|QL queries to create alerts. From Discover, click Alerts and select Create search threshold rule. This opens a panel that enables you to create a rule using an ES|QL query. Next, you can test the query, add a connector, and save the rule.
![esql kibana create rule](images/esql/esql-kibana-create-rule.png)
Limitationsedit
-
The user interface to filter data is not enabled when Discover is in ES|QL
mode. To filter data, write a query that uses the
WHERE
command instead. - Discover shows no more than 10,000 rows. This limit only applies to the number of rows that are retrieved by the query and displayed in Discover. Queries and aggregations run on the full data set.
- Discover shows no more than 50 columns. If a query returns more than 50 columns, Discover only shows the first 50.
- CSV export from Discover shows no more than 10,000 rows. This limit only applies to the number of rows that are retrieved by the query and displayed in Discover. Queries and aggregations run on the full data set.
-
Querying many indices at once without any filters can cause an error in
kibana which looks like
[esql] > Unexpected error from Elasticsearch: The content length (536885793) is bigger than the maximum allowed string (536870888)
. The response from ES|QL is too long. UseDROP
orKEEP
to limit the number of fields returned.