Doing Math over count of events

(Mulligan) #1


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.

How can i Disply the result of division between two field?
Json input - calculated with derived values like unique-count
(Lee Drengenberg) #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-*);

  1. back on the Data tab select Filter Ratio

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

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

  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.


Scripted field with division does not show result
(Mulligan) #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.

(Lee Drengenberg) #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?

(Mulligan) #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"

(Lee Drengenberg) #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.

Visual Builder Can't use overall aggregation as input to another aggregation
(Mulligan) #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.

(Lee Drengenberg) #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 +

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

(Mulligan) #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.

(Mulligan) #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.

(Mulligan) #11

(Mulligan) #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%.

(Lee Drengenberg) #13

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

(Mulligan) #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?


(Mulligan) #15


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.

(Lee Drengenberg) #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.

(Mulligan) #17

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

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:

(Mulligan) #18


(Mulligan) #19

Hi Lee,

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

(Lee Drengenberg) #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);

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.

Counting occurrence of two events and then displaying the percentage
Guage/Dial Ratio of Events Success