# Visualize metrics as a column using Metric Visualization

(Fabio) #1

Hi there!

My question is simple: is there any way to visualize some data similar to how I do using Metric Visualization but as a column rather than a row?

I have a field "accidents" and a field "city". I'd like to have the average number of accidents for every city of a country.

Using the Metric Visualization I can manage to have a visualization for country Italy like:

Accidents Accidents Accidents
30.2 15.8 20.6
Rome Milan Naples

But I'd like to have something like:

Rome: 30.2
Milan: 15.8
Naples: 20.6

#2

With Visual Builder > Markdown I think you can achieve this, but with just plain Metric Visualization I think it's not possible (I assume?).

(Fabio) #3

I think I'll go with the Markdown.

Mind you, now I'm having the following problem.

Let's say I have a field which is a boolean (can be either a SUCCESS or a FAIL).
I'd like to extract the percentage of those successes over the failures.

I tried constructing the Markdown as follows:

Aggregation:Filter Ratio
Numerator: myField:SUCCESS
Denominator: *
Metric Aggregation: Count

Aggregation: Moving Average
Metric: Filter Ratio
Model: Simple
Windows: Size:
Minimize: False

Group By: Terms
By: interesting_field_to_split_by.keyword

It works, but the estimation doesn't seem to be correct.
I mean, for example in the last 7 days, from another graph I can see evident low peaks for that field, but the Markdown says the average is 100% (which is not, obviously).

What did I do wrong?

I even tried to make the filter ratio manually by splitting the series into two Count Aggregation series and giving a variable name to each series. It works since in the Markdown then I see the mustache variable with the right values. The problem here is I cannot make mathematical computations in Markdown. Otherwise it'd have been sufficient to do {{my_successes_variable}}/{{my_total_variable}}100. But in so doing it will print out something like 2612/3515100 and not the result of the calculation.

Any idea?

Thank you so much!

#4

Not a expert on this subject, but for the Visual Builder to show "wonky values", such as 100% when it obviously is not, I would suggest to check/try changing the Panel options > Interval from: auto -> to something else, such as 1d in case your time range is week / month level ... something to do about, is the data fetched all in same bucket or not.

Did little bit testing myself and this another Kibana forum discussion was quite helpful: https://discuss.elastic.co/t/doing-math-over-count-of-events/97278/10

I wanted to calculate percentage of call_success:failed per host, so I did following:

Aggregation: Filter Ratio
Numerator: call_success:failed
Denominator: call_success:failed call_success:ok
Metric Aggregation: count

Aggregation: Moving Average:
Metric: Filter Ratio
Model: Simple
Window Size:
Minimize:false
Predict:
Group by: Terms By: host.keyword

Panel options
Interval: 1d
Drop last bucket: Yes

Metrics-Options:
Offset series by : 1d
Drop last bucket: No

Time Range: Last 7 days

... and I think I got the percentages that I was after, both on Time Series, Metric and Markdown views on Visual Builder.

About the another option, doing the mathematical computations .. perhaps you could achieve this by Bucket Script and then just present the values in Markdown, as discussed here: https://discuss.elastic.co/t/show-column-as-percentage-of-another-column/113149/3

(Fabio) #5

Now, trying out the Overall Average as a sibling aggregation rather than the parent aggregation Moving Average it starts showing some better results.

Mind you, things are not 100% accurate since I still have something like 8-9% error over a week of samples.

To make an example, filtering over the last 7 days, in the Discover I can see there are 2954 total hits and 1339 successful hits. So 45.32% of success.

Same results are given in the Visual Builder if I create a new series and Sum the total occurrences and the successful occurrences.

Though, in a Visual Builder series using the Filter Ratio it still shows 52.62%.

I tried changing the sampling as you suggested but it really varies a little (let's say it goes from 51% to 53%).

I really cannot get what the problem might be. Also because I'm grouping by another field, and a certain category of documents which should be 100% successful (since over that period of time show 2944 hits and 2944 successes) is something like 99.954%. It cannot depends on the sampling since EVERY document is a SUCCESS.

Unfortunately it seems I cannot use the mathematical computation approach (at least the way they used it in that thread) because I can't count only the occurrences that have a specific value for a field, can I?

In the discussion you linked, tsullivan does:

Sum -> number_of_tokens
Sum -> number_of_tokens_matched

And then use a Bucket Script.
What I'd need is something like:

Count -> my_field:SUCCESS
Count -> everything

But I cannot find a way to assign a variable to a filtered field. Is there any?

(system) closed #6

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