topic Re: Eval round gives inconsistent half-rounding results in Splunk Search

Eval round gives inconsistent half-rounding results

pentwist — Mon, 22 Oct 2018 22:52:40 GMT

I tried setting up a Splunk alert to check for inconsistencies between a rounded total and a raw total, but the alert has been giving false positives at random intervals because Splunk's round function doesn't seem to applying a consistent half-rounding rule. A clear example of this is if the following query is run:

*
| eval rounded_amount_22=round(22.275,2)
| eval rounded_amount_21=round(21.275,2)
| eval rounded_amount_20=round(20.275,2)
| eval rounded_amount_19=round(19.275,2)
| eval rounded_amount_18=round(18.275,2)
| eval rounded_amount_17=round(17.275,2)
| eval rounded_amount_16=round(16.275,2)
| eval rounded_amount_15=round(15.275,2)
| eval rounded_amount_14=round(14.275,2)
| eval rounded_amount_13=round(13.275,2)
| eval rounded_amount_12=round(12.275,2)
| eval rounded_amount_11=round(11.275,2)
| eval rounded_amount_10=round(10.275,2)
| head 1
| table
rounded_amount_10,
rounded_amount_11,
rounded_amount_12,
rounded_amount_13,
rounded_amount_14,
rounded_amount_15,
rounded_amount_16,
rounded_amount_17,
rounded_amount_18,
rounded_amount_19,
rounded_amount_20,
rounded_amount_21,
rounded_amount_22

If Splunk was using half-up, half-down, or banker's rounding, I'd expect to see consistency in the results, but I just don't see a pattern in what I get back:

rounded_amount_10: 10.28
rounded_amount_11: 11.28
rounded_amount_12: 12.28
rounded_amount_13: 13.28
rounded_amount_14: 14.28
rounded_amount_15: 15.28
rounded_amount_16: 16.27
rounded_amount_17: 17.27
rounded_amount_18: 18.27
rounded_amount_19: 19.27
rounded_amount_20: 20.27
rounded_amount_21: 21.28
rounded_amount_22: 22.28

In particular, every rounding scheme I know of should treat 10.275 and 20.275 the same - but as shown in the above list, Splunk is rounding up to 10.28 and down to 20.27. I just don't see a pattern in it.

I've also tried wrapping the round function in exact, but that doesn't change the results.

What's going on here? Is it possible for this query to return consistent half-rounding results?

Re: Eval round gives inconsistent half-rounding results

msivill_splunk — Thu, 25 Oct 2018 15:47:41 GMT

Just tested something similar in Python and got similar results

x = round(15.275, 2)
print(x)
y = round(17.275, 2)
print(y)

produces

15.28
17.27

Is this down to Floating point inaccuracy?

Re: Eval round gives inconsistent half-rounding results

msivill_splunk — Thu, 25 Oct 2018 15:58:59 GMT

Playing around with this some more the following works

| makeresults
| eval rounded_amount_22=round(22.275*1000,-1)/1000
| eval rounded_amount_21=round(21.275*1000,-1)/1000
| eval rounded_amount_20=round(20.275*1000,-1)/1000
| eval rounded_amount_19=round(19.275*1000,-1)/1000
| eval rounded_amount_18=round(18.275*1000,-1)/1000
| eval rounded_amount_17=round(17.275*1000,-1)/1000
| eval rounded_amount_16=round(16.275*1000,-1)/1000
| eval rounded_amount_15=round(15.275*1000,-1)/1000
| eval rounded_amount_14=round(14.275*1000,-1)/1000
| eval rounded_amount_13=round(13.275*1000,-1)/1000
| eval rounded_amount_12=round(12.275*1000,-1)/1000
| eval rounded_amount_11=round(11.275*1000,-1)/1000
| eval rounded_amount_10=round(10.275*1000,-1)/1000

The challenge here I think is the way computers hold floating numbers. http://effbot.org/pyfaq/why-are-floating-point-calculations-so-inaccurate.htm gives some further background to the problem.

The above scales up the number to a whole number performs the round then reduces it back to a float.

Re: Eval round gives inconsistent half-rounding results

DalJeanis — Thu, 25 Oct 2018 17:21:26 GMT

Here's a workaround... just add a tiny smidge to the thing being rounded.

| makeresults 
 | eval smidge=0.000000000001
 | eval rounded_amount_16R=round(16.275,2)
 | eval rounded_amount_15R=round(15.275,2)
 | eval rounded_amount_16S=round(16.275+smidge,2)
 | eval rounded_amount_15S=round(15.275+smidge,2)
 | table smidge  rounded_amount_*

If you want to see output in table form, you can do this...

 | untable smidge fieldname fieldvalue
 | rex field=fieldname "(?<test>rounded_amount_\d\d)(?<method>\w)"
 | chart first(fieldvalue) by test method

Re: Eval round gives inconsistent half-rounding results

pentwist — Thu, 25 Oct 2018 23:29:20 GMT

Good explanation of the problem and a clear workaround based on that. I had figured this would probably be related to a floating-points somehow, but since PHP doesn't have this problem (but definitely is prone to floating-point number inaccuracies) I thought there might be another explanation... super interesting that Python gives the same results.

Regardless, using round with a negative value hadn't occurred to me, and provides a clear and relatively readable method of ensuring reliable results. Great stuff!

Re: Eval round gives inconsistent half-rounding results

msivill_splunk — Fri, 26 Oct 2018 08:13:47 GMT

Great, glad this works for you and thank you for the up vote.