Kibana - range based on count timeseries

As continuation of this question: https://discuss.elastic.co/t/kibana-range-based-on-count/

I would like to now display this data over time. Kind of like this:

Which is backed by the following expression:

filters
| essql 
  query="SELECT COUNT(*) as patches_applied, hostname, source, HISTOGRAM(timestamp, INTERVAL 1 DAY) as hist 
FROM \"vulnerability-report\" 
WHERE 
(\"vulnerability-report\".os != 'Unknown')
GROUP BY hostname,hist,source" count=5000
| mapColumn "range"
  fn={getCell "patches_applied" | switch case={case if={all {gte 0} {lt 10}} then="0-10"} case={case if={all {gte 10} {lt 50}} then="10-50"} case={case if={all {gte 50} {lt 100}} then="50-100"} default="100+"}
| sort by="range"
| pointseries x="hist" y="unique(hostname)" color="range"
| plot defaultStyle={seriesStyle lines=2 fill=1 stack=1} 
  palette={palette "#1ea593" "#2b70f7" "#ce0060" "#38007e" "#fca5d3" "#f37020" "#e49e29" "#b0916f" "#7b000b" "#34130c" gradient=false}
| render

However there is a big problem in that this count parameter, when set to high enough value, simply crashes the browser. In this case, the unique combinations of hostname, hist andsource taken over that period of time amount to over 20000 entries (overall number of documents over that time period is little over 2 million) and that's enough to crash the browser.

Is it maybe possible in timelion?

I could not find any way filter on aggregated count in timelion.

The screenshot above is correct structurally but is basically only showing a subset of the data ordered by hostname, and does not accurately represent the situation

if i understand the issue the problem is that essql returns too many rows, which crashes the browser ? does pointseries return less data then ?

i don't think there is a way to do that in timelion.

Seems that it does, yes. I don't know how this count parameter works or how it limits the results. The correct way would be to aggregate this data so that records are transformed to timeseries points, instead of processing the whole set one record at a time, but I am not sure where to begin with that.

I basically need to operate on the doc_count value of cardinality aggregation, which ESSQL exposes thanks to it's table formatting. For this use-case though, I need to somehow get kibana to map this value over time for each of those ranges using aggregations.

PS:

If it worked, this would give me what I need:


Sadly it doesn't. Is there a way to do this somehow? Maybe via scripts? It's very hard to know how to apply techniques outlined in Elasticsearch aggregation documentation for Kibana visualizations.

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