elasticsearch/docs/reference/esql/esql-kibana.asciidoc

[[esql-kibana]]
=== Using {esql} in {kib}

++++
<titleabbrev>Using {esql} in {kib}</titleabbrev>
++++

You can use {esql} in {kib} to query and aggregate your data, create
visualizations, and set up alerts.

This guide shows you how to use {esql} in Kibana. To follow along with the
queries, load the "Sample web logs" sample data set by selecting **Sample Data** 
from the **Integrations** page in {kib}, selecting *Other sample data sets*, 
and clicking *Add data* on the *Sample web logs* card.

[discrete]
[[esql-kibana-enable]]
=== Enable or disable {esql}

{esql} is enabled by default in {kib}. It can be
disabled using the `enableESQL` setting from the
{kibana-ref}/advanced-options.html[Advanced Settings].

This will hide the {esql} user interface from various applications.
However, users will be able to access existing {esql} artifacts like saved searches and visualizations.

[discrete]
[[esql-kibana-get-started]]
=== Get started with {esql}

// tag::esql-mode[]
To get started with {esql} in Discover, open the main menu and select
*Discover*. Next, select *Try ES|QL* from the application menu bar.
// end::esql-mode[]

[discrete]
[[esql-kibana-query-bar]]
=== The query bar

After switching to {esql} mode, the query bar shows a sample query. For example:

[source,esql]
----
from kibana_sample_data_logs | limit 10
----

Every query starts with a <<esql-commands,source command>>. In this query, the
source command is <<esql-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 <<esql-commands,processing
commands>>. In this query, the processing command is <<esql-limit>>. `LIMIT`
limits the number of rows that are retrieved.

TIP: Click the **ES|QL help** button to open the
in-product reference documentation for all commands and functions or to get
recommended queries that will help you get started.

// tag::autocomplete[]
To make it easier to write queries, auto-complete offers suggestions with
possible commands and functions:

image::images/esql/esql-kibana-auto-complete.png[align="center"]
// end::autocomplete[]

[NOTE]
====
{esql} keywords are case-insensitive. The following query is identical to the
previous one:

[source,esql]
----
FROM kibana_sample_data_logs | LIMIT 10
----
====

[discrete]
==== Make your query readable

For readability, you can put each processing command on a new line. The
following query is identical to the previous one:

[source,esql]
----
FROM kibana_sample_data_logs
| LIMIT 10
----

You can do that using the **Add line breaks on pipes** button from the query editor's footer.

image::https://images.contentstack.io/v3/assets/bltefdd0b53724fa2ce/bltd5554518309e10f6/672d153cfeb8f9d479ebcc6e/esql-line-breakdown.gif[Automatic line breaks for ES|QL queries]

// tag::compact[]
You can adjust the editor's height by dragging its bottom border to your liking.
// end::compact[]

[discrete]
==== Warnings

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*.

[discrete]
[[esql-kibana-query-history]]
==== Query history

You can reuse your recent {esql} queries in the query bar.
In the query bar click *Show recent queries*.

You can then scroll through your recent queries:

image::images/esql/esql-discover-query-history.png[align="center",size="50%"]

[discrete]
[[esql-kibana-results-table]]
=== The results table

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.

NOTE: 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 <<esql-keep>> command:

[source,esql]
----
FROM kibana_sample_data_logs
| KEEP @timestamp, bytes, geo.dest
----

To display all fields as separate columns, use `KEEP *`:

[source,esql]
----
FROM kibana_sample_data_logs
| KEEP *
----

NOTE: The maximum number of columns in Discover is 50. If a query returns more
than 50 columns, Discover only shows the first 50.

[discrete]
==== Sorting

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
<<esql-sort>> command:

[source,esql]
----
FROM kibana_sample_data_logs
| KEEP @timestamp, bytes, geo.dest
| SORT bytes DESC
----

[discrete]
[[esql-kibana-time-filter]]
=== Time filtering

To display data within a specified time range, you can use the standard time filter, 
custom time parameters, or a WHERE command.

[discrete]
==== Standard time filter
The standard {kibana-ref}/set-time-filter.html[time filter] is enabled
when the indices you're querying have a field named `@timestamp`.

[discrete]
==== Custom time parameters
If your indices do not have a field named `@timestamp`, you can use
the `?_tstart` and `?_tend` parameters to specify a time range. These parameters 
work with any timestamp field and automatically sync with the {kibana-ref}/set-time-filter.html[time filter].

[source,esql]
----
FROM my_index
| WHERE custom_timestamp >= ?_tstart AND custom_timestamp < ?_tend
----

You can also use the `?_tstart` and `?_tend` parameters with the <<esql-bucket>> function 
to create auto-incrementing time buckets in {esql} <<esql-kibana-visualizations,visualizations>>. 
For example:

[source,esql]
----
FROM kibana_sample_data_logs
| STATS average_bytes = AVG(bytes) BY BUCKET(@timestamp, 50, ?_tstart, ?_tend)
----

This example uses `50` buckets, which is the maximum number of buckets.

[discrete]
==== WHERE command
You can also limit the time range using the <<esql-where>> command and the <<esql-now>> function.
For example, if the timestamp field is called `timestamp`, to query the last 15
minutes of data:

[source,esql]
----
FROM kibana_sample_data_logs
| WHERE timestamp > NOW() - 15minutes
----

[discrete]
[[esql-kibana-visualizations]]
=== Analyze and visualize data

Between the query bar and the results table, Discover shows a date histogram
visualization. By default, if the indices you're querying do not contain a `@timestamp`
field, the histogram is not shown. But you can use a custom time field with the `?_tstart` 
and `?_tend` parameters to enable it. 

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:

[source,esql]
----
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:

image::images/esql/esql-kibana-bar-chart.png[align="center"]

To make changes to the visualization, like changing the visualization type, axes and colors, click the
pencil button (image:images/esql/esql-icon-edit-visualization.svg[]). This opens
an in-line editor:

image::images/esql/esql-kibana-in-line-editor.png[align="center",width=66%]

You can save the visualization to a new or existing dashboard by clicking the
save button (image:images/esql/esql-icon-save-visualization.svg[]). 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 (image:images/esql/esql-icon-options.svg[]) and
select *Edit ES|QL visualization* to open the in-line editor:

image::images/esql/esql-kibana-edit-on-dashboard.png[align="center",width=66%]

[discrete]
[[esql-kibana-dashboard-panel]]
==== Add a panel to a dashboard

You can use {esql} queries to create panels on your dashboards.
To add a panel to a dashboard, under *Dashboards*, click the *Add panel* button and select {esql}.

image::images/esql/esql-dashboard-panel.png[align="center",width=50%]

Check the {esql} query by clicking the Panel filters button (image:images/esql/dashboard_panel_filter_button.png[Panel filters button on panel header]):

image::images/esql/esql-dashboard-panel-query.png[align="center",width=50%]

You can also edit the {esql} visualization from here.
Click the options button in the top-right (image:images/esql/esql-icon-options.svg[]) and
select *Edit ESQL visualization* to open the in-line editor.

image::images/esql/esql-dashboard-panel-edit-visualization.png[align="center",width=50%]

[discrete]
[[esql-kibana-enrich]]
=== Create an enrich policy

The {esql} <<esql-enrich>> command enables you to <<esql-enrich-data,enrich>>
your query dataset with fields from another dataset. Before you can use
`ENRICH`, you need to <<esql-set-up-enrich-policy,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.

image::images/esql/esql-kibana-enrich-autocomplete.png[align="center"]

Next, you can enter a policy name, the policy type, source indices, and
optionally a query:

image::images/esql/esql-kibana-enrich-step-1.png[align="center",width="50%"]

Click *Next* to select the match field and enrich fields:

image::images/esql/esql-kibana-enrich-step-2.png[align="center",width="50%"]

Finally, click *Create and execute*.

Now, you can use the enrich policy in an {esql} query:

[source,esql]
----
FROM kibana_sample_data_logs
| STATS total_bytes = SUM(bytes) BY geo.dest
| SORT total_bytes DESC
| LIMIT 3
| ENRICH countries
----

[discrete]
[[esql-kibana-alerting-rule]]
=== Create an alerting rule

You can use {esql} 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 {esql} query. Next, you can test the query, add a
connector, and save the rule.

image::images/esql/esql-kibana-create-rule.png[align="center",width=50%]

[discrete]
[[esql-kibana-limitations]]
=== Limitations

// tag::limitations[]
* The user interface to filter data is not enabled when Discover is in {esql}
mode. To filter data, write a query that uses the <<esql-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 {esql} is too long. Use <<esql-drop>> or
<<esql-keep>> to limit the number of fields returned.
// end::limitations[]