Averaging last values of multiple beats

Elastic Stack Kibana
1

I have a collection of Heartbeat monitors pinging several different hosts. Each host has multiple services being pinged (by different beats). Is there a way to display, for each host, either the number of beats that came back UP on their last ping or the average of the last summary.up value for each beat?

2

Try this

FROM heartbeat-*
| SORT @timestamp DESC
| STATS latest_status = TOP(monitor.status, 1), latest_summary_up = TOP(summary.up, 1) BY monitor.id, url.domain
| EVAL is_up = CASE(latest_status == "up", 1, 0)
| STATS up_beats = SUM(is_up), avg_up_summary = AVG(latest_summary_up) BY url.domain
| LIMIT 100

3

Is it then possible to display this on a Lens visualization (such as a Heatmap)?

4

Yes, it is absolutely possible to display this on a Lens visualization, such as a Heatmap!

Your ES|QL query is excellent for getting the aggregated up_beats and avg_up_summary per url.domain.

However, for a Heatmap specifically, which typically visualizes a matrix of two categorical fields against a metric (e.g., host vs. monitor showing its status), we'll need a slightly adjusted ES|QL query. The query you provided aggregates down to just url.domain, up_beats, and avg_up_summary, losing the individual monitor.id in the final STATS step.

To create a Heatmap that shows the status of each individual monitor across different hosts, we'll use this ES|QL query:

FROM heartbeat-*
| SORT @timestamp DESC
| STATS latest_summary_up = TOP(summary.up, 1) BY monitor.id, url.domain
| EVAL up_status_numeric = CASE(latest_summary_up == true, 1, 0) // 1 for UP, 0 for DOWN
| LIMIT 1000

This query will give you the monitor.id, url.domain, and a numeric up_status_numeric (1 for up, 0 for down) for the latest ping of each unique monitor. This format is perfect for a Heatmap.

Here are the step-by-step instructions in English to create a Heatmap in Kibana Lens using this query:

  1. Navigate to Lens:

    • In Kibana, go to Analytics > Lens.

  2. Select ES|QL as Data Source:

    • When prompted to choose a data source, select "ES|QL".

  3. Paste the ES|QL Query:

    • In the ES|QL editor that appears, paste the query provided above:

      FROM heartbeat-*
| SORT @timestamp DESC
| STATS latest_summary_up = TOP(summary.up, 1) BY monitor.id, url.domain
| EVAL up_status_numeric = CASE(latest_summary_up == true, 1, 0) // 1 for UP, 0 for DOWN
| LIMIT 1000

    • Click "Run" or "Update" to execute the query.

  4. Choose Heatmap Visualization:

    • On the right-hand side of the Lens editor, under "Visualization type", select "Heatmap".

  5. Configure the Heatmap Axes:

    • X-axis: Drag the url.domain field from the "Fields" list on the left to the "X-axis" drop zone.
    • Y-axis: Drag the monitor.id field to the "Y-axis" drop zone.
    • Color (Metric): Drag the up_status_numeric field to the "Color" drop zone. Lens will automatically apply an aggregation (e.g., Average or Sum). Since up_status_numeric is already 0 or 1, the default aggregation should work well to represent the status.

  6. Adjust Color Palette (Optional):

    • You might want to customize the color palette to clearly distinguish between 'up' (e.g., green) and 'down' (e.g., red). You can usually find color options in the "Layer settings" or "Color" section of the Heatmap configuration.

This will create a Heatmap where each cell represents a specific monitor on a specific host, and the color of the cell will indicate its latest 'up' or 'down' status.

Note: If you wanted to visualize the output of your original query (which provides url.domain, up_beats, and avg_up_summary), a Data Table or Bar Chart would be more suitable.

  • Data Table: Simply paste your original query into the ES|QL editor and select "Data Table" as the visualization type.
  • Bar Chart: For up_beats per url.domain, you could use url.domain on the X-axis and up_beats as the Y-axis metric.
5

I am not seeing “ES|QL” as a data source option in Kibana 8.19.14.