Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Archive

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

pentwist

Engager

10-22-2018
03:52 PM

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?

1 Solution

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Re: Eval round gives inconsistent half-rounding results

msivill_splunk

Splunk Employee

10-25-2018
08:47 AM

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?

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

msivill_splunk

Splunk Employee

10-25-2018
08:58 AM

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.

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Re: Eval round gives inconsistent half-rounding results

pentwist

Engager

10-25-2018
04:29 PM

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!

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Re: Eval round gives inconsistent half-rounding results

msivill_splunk

Splunk Employee

10-26-2018
01:13 AM

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

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Re: Eval round gives inconsistent half-rounding results

DalJeanis

SplunkTrust

10-25-2018
10:21 AM

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
```