Calculating count per term filter within one metric

[saif3r (A)]

Hello All,

I am currently working with a simple data set where each row represents an entry with one of three possible states. Let's say i have 5 documents in my index:

{
    "@timestamp": "2019-07-01T11:39:00.132Z",
    "name": "Car1",
    "state": "NA"
  }
{
    "@timestamp": "2019-07-02T11:39:00.132Z",
    "name": "Car2",
    "state": "OK"
  }
{
    "@timestamp": "2019-07-03T11:39:00.132Z",
    "name": "Car3",
    "state": "NOT OK"
  }
{
    "@timestamp": "2019-07-04T11:39:00.132Z",
    "name": "Car4",
    "state": "OK"
  }
{
    "@timestamp": "2019-07-05T11:39:00.132Z",
    "name": "Car5",
    "state": "OK"
  }

In above example, we have 5 documents:
1 with state: NA
1 with state: NOT OK
3 with state: OK

I would like to have a metric that would count in percentage the ratio of (OK) to (NOT OK + OK), so based on above documents, we have a calculation: 3/4 which should show us a metric of 75%. It's important to have an option to color the background based on the predefined threshold (eg 0% - 90% red, 91% - 100% green).
Is that possible? If so, how could I do that? I tried visual builder but applied filter per status applies to entire visualization so I'm not able to effectively 'extract' count of records per each state.

[lukeelmers]

I'm not sure if I can think of a way to accomplish this in visualize metrics or visual builder, though you can perform math operations like this in Timelion.

If you used the metric element in Canvas, you could send a timelion function to it and then conditionally style based on the result. An expression along these lines might come close to what you are looking for:

filters
| timelion 
  query=".es(index=my_index_name, q=state:OK).divide(.es(index=my_index_name, q=\"state:OK OR state:\"NOT OK\"\"))"
| filterrows {getCell "value" | any {neq null}}
| math "round(multiply(mean(value), 100), 0)"
| metric "% OK" 
  metricFont={ color={ if {gte 91} then="green" else="red" } }
| render

This expression does the following:

1. Queries your index and produces a value that is the total number of OK entries divided by the total OK + NOT OK entries.
2. Filters the resulting value to remove any null entries (may or may not be necessary depending on your data set)
3. Takes the avg of the selected values (based on your time range), and converts them to a percentage.
4. Creates a metric with that value which is conditionally red/green based on whether the value is below 91.

There are probably some other ways to accomplish this, but wanted to share the first idea that came to mind in hopes that it points you in the right direction!

[saif3r (A)]

Hello @lukeelmers,

Thank you for the answer. This Timelion query seems to be working just fine. I've learned a lot, since i have't used it until now, so thank you very much for that.
As for the result itself, I'm looking for slightly different type of visual presentation, and I apologize for not mentioning it earlier. I have a dashboard with around 5 different visualizations. My intention is to put this calculation among other within same dashboard, but as a metric based on a set of filters i have. So in this case, I can't use Canvas as some of those visualizations cannot be placed there (at least not with my level of knowledge). Also instead of line chart Timelion shows, I need to have a single field with a number. More or less similar to what we can see in [Metricbeat System] Overview ECS Dashboard provided by Kibana.

Is that something that could be achieved with current data model?
Thanks in advance.

[lukeelmers]

Ah, yeah if you need to make calculations like this which are based on other computed metrics/buckets, then what you are probably looking for is a bucket script aggregation or a scripted metric aggregation. Unfortunately these aren't currently supported in Kibana, but there are Github issues tracking support here and here.

In the meantime, Timelion with Canvas is probably the best workaround I can think of for achieving a similar result

[saif3r (A)]

Understood, thank you @lukeelmers