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

**URL:** <https://discuss.elastic.co/t/show-documents-with-a-last-value-greater-than-a-number-and-drop-all-other-documents-from-the-selection/338783>\
**Category:** Kibana\
**Created:** [July 19, 2023, 11:48am UTC](https://discuss.elastic.co/t/show-documents-with-a-last-value-greater-than-a-number-and-drop-all-other-documents-from-the-selection/338783 "2023-07-19T11:48:11Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![zafire](https://avatars.discourse-cdn.com/v4/letter/z/aeb1de/32.png) [@zafire](https://discuss.elastic.co/u/zafire)\
**Post date:** [July 19, 2023, 11:48am UTC](https://discuss.elastic.co/t/show-documents-with-a-last-value-greater-than-a-number-and-drop-all-other-documents-from-the-selection/338783/1 "2023-07-19T11:48:11Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![jsanz](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jsanz/32/53734_2.png) [@jsanz](https://discuss.elastic.co/u/jsanz)\
**Post date:** [July 19, 2023, 2:53pm UTC](https://discuss.elastic.co/t/show-documents-with-a-last-value-greater-than-a-number-and-drop-all-other-documents-from-the-selection/338783/2 "2023-07-19T14:53:21Z")

</div>

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

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/4/b/4bf2e8f07eb459de5d24f30ab7c8ad422b7f108b.png)

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.

```auto
{
  "$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}
    }
  }
}

```

---

<div class="post-metadata">

**Author:** ![zafire](https://avatars.discourse-cdn.com/v4/letter/z/aeb1de/32.png) [@zafire](https://discuss.elastic.co/u/zafire)\
**Post date:** [July 20, 2023, 10:06am UTC](https://discuss.elastic.co/t/show-documents-with-a-last-value-greater-than-a-number-and-drop-all-other-documents-from-the-selection/338783/3 "2023-07-20T10:06:43Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![jsanz](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jsanz/32/53734_2.png) [@jsanz](https://discuss.elastic.co/u/jsanz)\
**Post date:** [July 20, 2023, 10:42am UTC](https://discuss.elastic.co/t/show-documents-with-a-last-value-greater-than-a-number-and-drop-all-other-documents-from-the-selection/338783/4 "2023-07-20T10:42:03Z")

</div>

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

> **[Aggregation](https://vega.github.io/vega-lite/docs/aggregate.html#transform)**
>
> Vega-Lite - a high-level grammar for statistical graphics. Vega-Lite provides a higher-level grammar for visual analysis, comparable to ggplot or Tableau, that generates complete Vega specifications. Vega-Lite specifications consist of simple...

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [August 17, 2023, 10:42am UTC](https://discuss.elastic.co/t/show-documents-with-a-last-value-greater-than-a-number-and-drop-all-other-documents-from-the-selection/338783/5 "2023-08-17T10:42:10Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.