Doing Math over count of events

Elastic Stack Kibana
1

Hi,

I have some tomcat app logs in which i am parsing the TransactionName and TransactionStatus fields. My end goal is to create a single value visualisation which shows the percentage of successful transactions over total.

My TransactionStatus field is non-numeric.

for reference Splunk query is like this :slight_smile:

index=app sourcetype=tomcat_app_log KpiMetric="CLQ" TransactionName="SearchApplications" | stats count(eval(TransactionStatus = "Success")) as SUCCESS count(eval(TransactionStatus = "Failure")) as FAILURE | eval SuccessRate= Round(SUCCESS/(SUCCESS + FAILURE)*100 , 2) | fields SuccessRate

I am not sure where this calculation can be done.

2

Here's one option if you're on a recent version of Kibana that has the Visual Builder visualization type. I know it's in 5.4.0 and later.

  1. go to the Panel Options tab and set your index pattern (here I'm using metricbeat-*);

image

  1. back on the Data tab select Filter Ratio

image

  1. I picked metricset.name:process as my numerator, but yours would be something like transactionStatus:Success

image

  1. The Gauge tab shows a single value and there some options to format as a percent, change the color based on value, etc.

image
image.png1843×1215 138 KB

  1. But the gauge is showing the value of the last interval. Not the average over your whole timespan. So you might want to change the interval from the default auto to something that puts everything in your timespan into one bucket. WARNING: You should turn off the Auto Apply when changing the interval or you'll have errors (there's a bug on it).

  2. Lastly, there's also a Markdown tab that lets you put the value with other text. And when you save this visualization it seems that it saves whatever panel you're on. So you can add it to a dashboard.

Regards,
Lee

3

Thanks Lee for the nice explanation, but for me it doesnt work. In my case the TransationStatus field is text based and i want to calculate what was the percentage of Successful transaction out of total.

SuccessRate= Round(SUCCESS/(SUCCESS + FAILURE)*100 , 2)

The method you mentioned doesnt work for me. It is always showing 0.

4

If you just go to Discover, and look at your total Hits count in the upper left corner,
then in the query bar put transactionStatus:success does that filter your docs down to only the "Success" ones you're looking for?

5

Yes it gives me the number of documents that have TransactionStatus:"Success" , but as i said my end goal is to get the value of

SuccessRate= Round(SUCCESS/(SUCCESS + FAILURE)*100 , 2)

where SUCCESS - number of events which have TransactionStatus:"Success" and FAILURE - events which have TransactionStatus:"Failure"

6

Do all your docs have either success or failure? If so, then the visual builder screen I showed should work because it's doing transactionStatus:success/*
In this case * means all docs, which is both success and failure.

If some of your docs don't have either success or failure, then we'll need to use something other than * for the denominator.

7

Not all of them have TransactionStatus:Success or TransactionStatus:Failure, some of the documents have that field as blank. But my main intent is to count all the documents with either Success or Failure and calculate the percentage for Success.

8

OK, so in that case the Denominator should be the total where your transactionStatus is either success or failure. In
Elasticsearch queries, listing 2 search terms implies OR so in this screenshot I've listed 2 search terms in the denominator and one in the numerator field. This is giving me
metricseat.name:process/(metricset.name:process + metricset.name:filesystem)

(you don't have to multiply by 100 because there is already a percent formatting option)

image

9

I actually did as per your instructions and for few seconds it was showing some percentage value, then suddenly it changed back to 0%.

Here is my screenshot.

image

10

I am not sure if this is a bug, i see % when i click on the Metric visualisation and see some numbers when i see the time series.

image

11

image

12

So i just calculated manually,

Total documents : 105
Documents containing TransactionStatus:Success : 98

So the percentage comes out to be 93.33 . For few seconds i saw that number on the Gauge but when i saved the visualisation, it changed it back to 0%.

13

Did your timepicker change to something outside the range of your data? When I saved mine it still looked correct.

14

So now the Guage says it is 100% , but the Markdown says 0%, is it possible to reflect that to true value as well?

image

15

Again,

i changed the time picker to just reflect documents for last 24 hours, the discovery tab is showing the total number of documents is 88 and documents which have TransactionStatus:Success is 77, so the percentage should be 90.5 but it is showing 100 % , its strange.

16

What is your interval set to? There may be some issues at the boundaries of the timespan if the interval is about the same or larger.

17

What do you mean by time interval, the interval set in the visualisation builder?

image

The below screenshot is for last 15 minutes, it is showing 4 transactions out of which 3 are successful. But the Visualisation shows 0 again. :frowning:

18

image

19

Hi Lee,

can i get any help on this one? I am stuck at this point.

20

Thanks for your patience. I'm learning more about Visual Builder as I go here...

If you have your first aggregation which is a Filter Ratio which is calculating success/(success or failure).

That ratio probably changes over time, and your gauge or markdown is showing the last value. But it sounds like you want a metric that shows the percentage over the entire timespan, which might be Last 15 minutes or Last 30 days.

So you can add Overall Average aggregation to do that. First click the + sign on your existing aggregation, and then select Overall Average under the Sibling Pipeline Aggregations.
The Metric field to the right, should list your Filter Ratio aggregation from above (mine is different in this screenshot);

image
image.png2122×786 48 KB

So now you shouldn't have to worry about the Interval and it should just be set to Auto.

Let me know how that works.

2 Likes