Show documents with a last value greater than a number and drop all other documents from the selection

[zafire]

Hi all.

First post here, so hope I am doing it right.

I have an index of servers and mounted disks (from metricbeat) that has a percent used value, system.filesystem.used.pct and system.filesystem.mount_point.

I want to ONLY show those file systems that are currently, ie last value, over 90%, either as a Metric count or a horizontal bar.

The problem I have is that if I filter "used pct > 90" then I gather up the OLD documents for when the filesystem went over 90%. If that file system was cleaned, and is now (say) at 80% then I DONT want it to appear in the metric/bar chart.

I can use last_value and sort descending, but it will always show all mount points below 90% where as (especially for a metric) I want to only show those that are currently over 90%.

It's like I want to filter AFTER I have selected the last_values.

I have tried a variety of things, including SQL with GROUP BY and BETWEENs etc, but just can't get this to work.

Summary: Only select documents where a field has a last_value of greater than a certain number without using filters.

Any guidance appreciated.

Many thanks,
Mark

[jsanz (Jorge Sanz)]

To filter the results of your query you need to use Vega and the transform section.

I would suggest to render all your servers and highlight those with the values that are above your threshold.

Still, I did some work and got a vega viz that filters based on the value of system.memory.actual.used.pct (I had that field available from metricbeat for two hosts)

So this screenshot shows first the metric with a line chart embedded and colored by the value of the last value of the metric. The second is basically the same but as a table, loosing the history of the metric over time. Finally the vega visualization shows only one of the values as the other was filtered (see the spec below).

I had to use a runtime field to "rename" the metricbeat field because I could see a way to escape the dots in the field name. Check the the transform section for the manipulations in the output data and filtering leaving it ready for rendering.

My Vega skills are limited but I guess there are ways to make the chart nicer.

{
  "$schema": "https://vega.github.io/schema/vega-lite/v5.json",
  "data": {
    "url": {
      "%context%": true,
      "%timefield%": "@timestamp",
      "index": "metrics-*,metricbeat-*",
      "body": {
      "runtime_mappings": {
        "memory_pct": {
          "type": "double",
          "script": {
            "source": "emit(doc['system.memory.actual.used.pct'].value)"
          }
        }
      }, 
      "aggs": {
        "hostnames": {
          "terms": {
            "field": "host.hostname",
            "order": {
              "bucket>metric[memory_pct]": "desc"
            },
            "size": 2
          },
          "aggs": {
            "bucket": {
              "filter": {
                "bool": {
                  "should": [
                    {
                      "exists": {
                        "field": "system.memory.actual.used.pct"
                      }
                    }
                  ],
                  "minimum_should_match": 1
                }
              },
              "aggs": {
                "metric": {
                  "top_metrics": {
                    "metrics": {
                      "field": "memory_pct"
                    },
                    "size": 1,
                    "sort": {
                      "@timestamp": "desc"
                    }
                  }
                }
              }
            }
          }
        }
      },
        "size": 0
      }
    },
    "format": {"property": "aggregations.hostnames.buckets"}
  },
  "transform": [
    {"calculate": "datum.bucket.metric.top[0].metrics.memory_pct * 100", "as": "memory_pct"},
    {"calculate": "substring(datum.key,0,5)", "as": "key_trim"},
    {"filter": "datum.bucket.metric.top[0].metrics.memory_pct > 0.6"}
  ],
  "mark": {
    "type": "text",
    "fontSize": 25
  },
  "encoding": {
    "column": {
      "field": "key_trim",
      "type": "ordinal",
      "header": { "title": "Memory per host"}
    },
    "text": {
      "field": "memory_pct",
      "type": "ordinal",
      "format": ".1f",
      "header": { "title": null}
    }
  }
}

[zafire]

This is really great stuff, and I've already modified it to work with finding a list of hosts with some disk over 90% on it.

Is there a way to show that last table (with 73.7) just as the number "1" .. ie there is 1 host that meets these criteria?

This will be for a "Metric" that then provides a click through to the detailed dashboard etc.

[jsanz (Jorge Sanz)]

Sure, check the transforms in Vega-Lite and particularly the "aggregate" section.

Aggregation