topic Re: Problems with tonumber convertion - always returns the same value in Splunk Search

Problems with tonumber convertion - always returns the same value

ChrisLH — Fri, 08 Aug 2014 09:35:48 GMT

Hi,

while doing some field comparisons I had problems matching one number field to another. After some research into my data I got the strange phenomena that converting my field to a number always leads to the same result.
My input value is already numerical but if a apply the following search:

sourcetype=systemevents eventTyp=PT0001 | eval number=tonumber(seriennummerZentral) | eval string=tostring(seriennummerZentral) | table seriennummerZentral number string

I get:

seriennummerZentral↕ number↕ string↕

06021001100101011000001492 6021001100101010952552448.000000 06021001100101011000001492
06021001100101011000001492 6021001100101010952552448.000000 06021001100101011000001492
06021001100101011000001492 6021001100101010952552448.000000 06021001100101011000001492
06021001100101011000001487 6021001100101010952552448.000000 06021001100101011000001487
06021001100101011000001487 6021001100101010952552448.000000 06021001100101011000001487
06021001100101011000001451 6021001100101010952552448.000000 06021001100101011000001451
06021001100101011000001487 6021001100101010952552448.000000 06021001100101011000001487
06021001100101011000001451 6021001100101010952552448.000000 06021001100101011000001451
06021001100101011000001487 6021001100101010952552448.000000 06021001100101011000001487
06021001100101011000001451 6021001100101010952552448.000000 06021001100101011000001451
06021001100101011000001487 6021001100101010952552448.000000 06021001100101011000001487
06021001100101011000001487 6021001100101010952552448.000000 06021001100101011000001487

seriennummerZentral is changing as is string but number is always the same

Re: Problems with tonumber convertion - always returns the same value

somesoni2 — Fri, 08 Aug 2014 11:26:30 GMT

I guess that you've hit the limit of integers when converting to number. Do you have any specific need for the conversion?

Re: Problems with tonumber convertion - always returns the same value

ChrisLH — Fri, 08 Aug 2014 12:21:22 GMT

I wanted to select events by comparing two number fields:
seriennummerZentral seriennummerFzg
with the search statement:

sourcetype=systemevents eventTyp=PT0001 | where seriennummerZentral=seriennummerFzg

and got only positve results no matter the values. I figured it's maybe a problem with data types and tryed converting to number and to string.
| where tostring (seriennummerZentral) = tostring (seriennummerFzg)
is working
| where tonumber (seriennummerZentral) = tonumber (seriennummerFzg)
gives the same results as the initial statement, always true

Re: Problems with tonumber convertion - always returns the same value

somesoni2 — Fri, 08 Aug 2014 12:40:40 GMT

I believe you won't be doing any numeric operation on the Serial No fields, you should use it as string (with conversion if required).

Re: Problems with tonumber convertion - always returns the same value

ChrisLH — Fri, 08 Aug 2014 12:48:27 GMT

yeaa, you're probably right 🙂
and I think the problem was the integer limit.
Thx !

Re: Problems with tonumber convertion - always returns the same value

martin_mueller — Fri, 08 Aug 2014 18:40:42 GMT

Being slightly pedantic here, but what's actually happening is that 6,021,001,100,101,010,952,552,448 is the most accurate IEEE754 Double representation - for all of your input serial numbers. The small differences between them are beyond what a Double can distinguish.

For comparison, the next bigger Double with no other values between them would be 6,021,001,100,101,012,026,294,272 - about a billion higher.