Getting started with ES|QL (Elasticsearch Query Language)

ES|QL (Elasticsearch Query Language) is Elastic®'s new innovative piped query language, designed to speed up your data analysis and investigation processes by offering powerful computing and aggregation capabilities. 

Navigate through the complexities of identifying unfolding cyber attacks or pinpointing production issues with enhanced ease and efficiency. 

ES|QL not only simplifies searching, aggregating, and visualizing massive data sets but also empowers users with advanced features like lookups and real-time processing, all from a single screen in Discover.

Our users need agile tools that not only present data but also offer efficient methods to make sense of it, as well as the ability to act on insights in real time and post ingest data processing. 

Elastic’s commitment to enhancing users’ data exploration experience drove us to invest in ES|QL. It is designed to be accessible for beginners and powerful for experts. With ES|QL’s intuitive interface, users can start quickly and dive deep into their data without steep learning curves. The auto-complete and in-app documentation ensure that crafting advanced queries becomes a straightforward workflow. 

Moreover, ES|QL doesn’t just show you numbers; it brings them to life. Contextual visualizations powered by Lens suggestion engine automatically adapt to the nature of your queries, providing a clear view of your insights. 

Additionally, a direct integration into Dashboards and Alerting functionalities reflects our vision of a cohesive, end-to-end experience. 

In essence, our investment in ES|QL was a direct response to the evolving needs of our community — a step toward a more interconnected, insightful, and efficient workflow.

This will get you into ES|QL mode in Discover.

With ES|QL, you can do comprehensive and powerful data exploration. It allows you to conduct ad-hoc data exploration within Discover, create aggregations, transform data, enrich data sets, and more, directly from the query builder. Results are presented in a tabular format or as visualizations — it depends on the query you are executing.

Below you will find examples of ES|QL queries for observability and how the results are represented in both a tabular format and as a visual representation. 

ES|QL query with metrics use case:

The above query is showcasing how you can utilize following source command, aggregation functions, and processing commands: 

from source command (documentation)

from metrics*: This initiates a query from index patterns that match the pattern “metrics*.” The asterisk(*) acts as a wildcard, meaning it will select data from all index patterns whose names start with “metrics.” 

stats…by aggregations (documentation), max (documentation), and by (documentation) 

This segment aggregates data based on specific statistics. It breaks down as follows: 

max_cpu=max(kubernetes.pd.cpu.usage.node.pct): For each distinct “kubernetes.pod.name,” it finds the maximum CPU usage percentage and stores that value in a new column named “max_cpu.” 

max_mem = max(kubernettes.pod.memory.usage.bytes): For each distinct “kubernetes.pod.name,” it finds the maximum memory usage in bytes and stores that value in a new column named “avg_mem.” 

Processing commands (documentation)

• sort (documentation) 
• limit (documentation)

sort max_cpu desc: This sorts the resulting data rows by the “max_cpu” column in descending order. This means the row with the highest “max_cpu” value will be at the top. 

limit 10: This limits the output to the top 10 rows after sorting. 

In summary, the query: 

• Groups data from all metric indices using an index pattern
• Aggregates the data to find the maximum CPU usage percentage and maximum memory usage for each distinct Kubernetes pod
• Sorts the aggregated data by the maximum CPU usage in descending order
• Outputs only the top 10 rows with the highest CPU usage

Contextual visualizations: When writing ES|QL queries in Discover, you’ll receive visual representations powered by the Lens suggestion engine. Your query’s nature determines the type of visualization you get, whether it’s a Metric, Histogram Heatmap, etc. 

Below is a visual representation in the form of a bar chart and a table representation of the above query with columns max_cpu, avg_mem, and kubernetes.pod.name:

Example of an ES|QL query with Observability and time-series data use case: 

The above query is showcasing how you can utilize the following source command, aggregation functions, processing commands, and functions. 

from source command (documentation)

from apache-logs: This initiates a query from an index named “apache-logs.” This index contains log entries related to Apache web server traffic. 

where (documentation)

where url.original==”/login”: Filters the records to only those where the “url.original” field equals “/login.” This means we are only interested in log entries pertaining to login attempts or accesses to the login page. 

eval (documentation) & auto_bucket (documentation)

eval time_buckets =... : This creates a new column named “time_buckets.” 

“auto_bucket” function creates human-friendly buckets and returns a datetime value for each row that corresponds to the resulting bucket the row falls into. 

“@timestamp” is the field containing the timestamp of each log entry. 

“50” is the number of buckets. 

“2023-09-11T21:54:05.000Z”: Start time for bucketing

“2023-09-12T00:40:35.000Z”: End time for bucketing 

This means that log entries from “2023-09-11T21:54:05.000Z” to “2023-09-12T00:40:35.000Z” will be divided into 50 equally spaced intervals, and each entry will be associated with a specific interval based on its timestamp. 

The goal isn’t to provide exactly the target number of buckets, it’s to pick a range that you are comfortable with that provides at most the target number of buckets. If you ask for more buckets, then auto_bucket can pick a smaller range. 

stats…by aggregations (documentation), count (documentation), and by (documentation) 

stats login_attempts = count(user.name) by time_buckets, user.name: Aggregates the data to calculate the number of login attempts. It does this by counting the occurrences of “user.name” (representing unique users attempting to log in). 

The count is grouped by both the “time_buckets” (the time intervals we created) and “user.name.” This means for each time bucket, we will see how many times each user attempted to log in. 

sort (documentation) 

Sort login_attempts desc: Finally, the aggregated results are sorted by the “login_attempts” column in descending order. This means the result will show the highest number of login attempts at the top. 

In summary, the query:

• Selects data from the “apache-logs” index
• Filters for log entries related to the login page
• Buckets these entries into specific time intervals
• Counts the number of login attempts for each user in each of those time intervals
• Outputs the results sorted by the highest number of login attempts first

Below is a visual representation in the form of a bar chart and a table representation of the above query with columns login_attempts, time_buckets, and user.name.

Edit ES|QL visualizations directly within Discover and Dashboards. No need to navigate to Lens for quick edits; you can make changes seamlessly. 

Below you can see a video of an end-to-end workflow or read the step-by-step guide:

1. Writing an ES|QL query

2. Getting contextual visualization based on the nature of the query

3. In-line edit the visualization

4. Save it to a Dashboard

5. Be able to edit the visualization from a Dashboard

Step 1. Writing an ES|QL query. Query example that produces a metric visualization:

Step 2. Getting contextual visualization (in this case a metric visualization) based on the nature of the query. You can then select the pencil icon to go into in-line editing mode.

Step 3. Editing the visualization using in-line editing mode

In the above case, we want the visualization to be in dynamic color mode, so we switch it to “Dynamic.”

We also have the opportunity to define the color ranges we want to use:

Step 4. Saving to a Dashboard

Step 5. Be able to edit the visualization from a Dashboard

You can utilize ES|QL for observability and security alerts, setting aggregated values as thresholds. Enhance detection accuracy and receive actionable notifications by emphasizing meaningful trends over isolated incidents, reducing false positives. 

Below, we will focus on how to create an ES|QL alert rule type from Discover. 

The new alert rule type is available under the existing Elasticsearch rule type. This rule type brings all the new functionalities that are available within ES|QL and unlocks new alerting use cases.

With the new type, users will be able to generate a single alert based on a defined ES|QL query and preview the query result before saving the rule. When the query returns an empty result, no alerts will be generated.

Query example for an alert:

Step 1. Click on “Alerts” and then “Create search threshold rule.” You can start creating your ES|QL alert rule type either after you have defined your ES|QL query in the query bar or before you have defined your ES|QL query. The benefit of doing it after you have defined it is that the query automatically gets pasted into the “Create Alert” flyout. 

Step 2. Start defining your ES|QL alert rule type

Step 3. Test your alert rule type query. You can iterate on the ES|QL query that is pasted in and test it by clicking on “Test query.” This will give you a preview of the results in a table. 

Step 4. Set up your connector and “Save.” You have now successfully created an ES|QL alert rule type!

You can use the enrich command (documentation) to enhance your query data set with fields from another data set, complete with in-context suggestions for the selected policy (i.e., hinting the matching field and enriched columns).

Query example using ENRICH, where a enrich policy :”servers-to-project” is being utilized via the query to enrich the data set with name, server_hostname, and cost:

We have also made it easy for users to create enrich policies by adding an overview and a wizard to create enrich policies. 

To find an overview of enrich policies, navigate to Stack Management ⇒ Index Management, and there you will see a tab called Enrich Policies:

Here is the enrich policy used in above query: “servers-to-project”:

You can easily start creating a new enrich policy by clicking on Create enrich policy. As soon as you have created and executed one, it can then be used in an ES|QL query in Discover.

Learn more about enrich policies here and about the ENRICH command in ES|QL here.