topic Re: How to edit my search to format String to Number with fieldformat for all 60 columns? in Splunk Search

How to edit my search to format String to Number with fieldformat for all 60 columns?

laudai — Mon, 24 Apr 2017 15:19:53 GMT

Hey guys
Is there a quick way to format data?
I want to format data like this

<search> |fieldformat test1a=tonumber(test1a)

It works but I have more than 60 columns to do

so I try this <search> |fieldformat test*a=tonumber(test*a)
it's not any change to data

column have test1a test2a test3a test4a ... do1t do2t do3t do4t ...... ss1r ss2r ss3r ss4r ... and so on
This data are Scientific notation data
If I do Regular Expression I will type more than 20 row

Does any suggest quick format data?

Thanks you for anyway.

Re: How to edit my search to format String to Number with fieldformat for all 60 columns?

somesoni2 — Mon, 24 Apr 2017 15:37:06 GMT

You need foreach command for that.
Like this

 <search> | foreach test*a [fieldformat "<<FIELD>>"=tonumber('<<FIELD>>')

Re: How to edit my search to format String to Number with fieldformat for all 60 columns?

laudai — Wed, 26 Apr 2017 10:08:54 GMT

HI somesoni2 thank you for your answer.

I meet some problem in splunk string,number

For example:

|eval test="123.4E-3" |fieldformat test=tonumber(test)|table test

your test will get 0.1234

If you text like this

|eval total=0 | eval test1="1.2000E-3" | eval test2="2.4000E-3" |eval test3=3.6000E-3
|foreach test* [fieldformat <<FIELD>>=tonumber(<FIELD>)]
|foreach test* [eval total=total+<<FIELD>>]
|table test*,total

test1 test2 test3 total
1.2000E-3 2.4000E-3 0.0036000 0.0072000

In this case test1 test2 test3 are numbers so you can add them together,even you Double quotes numbers.
You can see the column is number or text with right-justified or left-justified

How can I tell a field value's type (number or string) just by looking?
https://answers.splunk.com/answers/241582/how-can-i-tell-a-field-values-type-number-or-strin.html

or you can use isnum() isstr() to check type

In Splunk you can test like this to confirm column type

|eval total=0 | eval test1="1.2000E-3" | eval test2="2.4000E-3" |eval test3=3.6000E-3 |eval test4="12" |eval testd="12d"
|eval 1isnum=if(isNum(test1),"true","false")
|eval 2isnum=if(isNum(test2),"true","false")
|eval 3isnum=if(isNum(test3),"true","false")
|eval 4isnum=if(isNum(test4),"true","false")
|eval disnum=if(isNum(testd),"true","false")
|eval 1isstr=if(isstr(test1),"true","false")
|eval 2isstr=if(isstr(test2),"true","false")
|eval 3isstr=if(isstr(test3),"true","false")
|eval 4isstr=if(isstr(test4),"true","false")
|eval disstr=if(isstr(testd),"true","false")
|table 1* 2* 3* 4* d*

in this case test1~4 are number and string
your will get :
1isnum 1isstr 2isnum 2isstr 3isnum 3isstr 4isnum 4isstr disnum disstr
true true true true true true true true false true

Why are fields returning true for both isNum() and isStr()
https://answers.splunk.com/answers/241582/how-can-i-tell-a-field-values-type-number-or-strin.html

Is foreach is a bug to use tonumber?

Thank you

Re: How to edit my search to format String to Number with fieldformat for all 60 columns?

DalJeanis — Wed, 26 Apr 2017 16:23:03 GMT

The tonumber() function will make the field into a number if it possibly can.

This function converts the input string NUMSTR to a number. NUMSTR can be a field name or a value. BASE
is optional and used to define the base of the number to convert to. BASE can be 2 to 36, and defaults to 10. If the tonumber function cannot parse a field value to a number, for example if the value contains a leading and trailing space, the function returns NULL. Use the trim function to remove leading or trailing spaces. If the tonumber function cannot parse a literal string to a number, it returns an error.

| makeresults  | eval total=0 | eval test1="1.2000E-3" | eval test2="2.4000E-3" 
| eval test3=3.6000E-3 | eval test4="12" | eval testd="12d" 
| foreach test* 
    [ eval <<FIELD>>.beforetype = typeof(<<FIELD>>) 
    | eval <<FIELD>> = tonumber(<<FIELD>>) 
    |  eval <<FIELD>>.aftertype = typeof(<<FIELD>>) 
    | eval total=total+if(isnum(<<FIELD>>),<<FIELD>>,0)
    ] 
| table test* total

As far as your isstr() question, it appears that "num" is a subset of "string" in splunk's implementation. That's not unheard of in token-oriented languages -- i wonder what else other than "null()" is NOT a string -- although it's unfamiliar if you are coming from the C/Java world.