topic Re: Not able to convert field from string to number in Splunk Search

Not able to convert field from string to number

nilbak1 — Wed, 25 Mar 2020 10:02:20 GMT

The below is my query to extact fields from screenshot attached.

index=***** host=***** source=****** | rex field=_raw max_match=0 "(?[a-z]+),(?\w+\-?\d?.*)\,(?\d?.*)"

Now I want to convert Size field from string to numeric as have to perform various statistical operations.

I used tonumber, convert, fieldformat but none worked.

This is my final query
index=**** host=***** source=******** | rex field=_raw max_match=0 "(?[a-z]+),(?\w+\-?\d?.*)\,(?\d?.*)" | table Brand,Size,Files | eval _counter = mvrange(0,mvcount(Brand)) | stats list(*) as * by _counter | foreach * [ eval <> = mvindex('<>' , _counter)] | xyseries Brand Files Size | transpose 0 header_field=Brand column_name=Files | foreach * [ eval <> = if(isnull(<>) OR len(<>)==0, "0", <>) ]

I have to convert Size values from kb into mb for this I need to change them from string to number.

Re: Not able to convert field from string to number

harsmarvania57 — Thu, 26 Mar 2020 13:00:38 GMT

Hi,

Please try below query

<yourBaseQuery>
| rex field=_raw max_match=0 "(?<linedata>[^\r\n]+)"
| mvexpand linedata
| rex field=linedata max_match=0 "(?<Brand>[a-z]+),(?<Files>\w+\-?\d?.*)\,(?<Size>\d?.*)"
| fields Brand, Files, Size
| eval Size=Size/1024, {Brand}=Size
| stats values(*) as * by Files
| foreach * [ eval <<FIELD>> = if(isnull(<<FIELD>>), 0, <<FIELD>>) ]

Re: Not able to convert field from string to number

woodcock — Thu, 26 Mar 2020 13:49:54 GMT

It probably has whitespace around it so try adding trim() like this:

| makeresults 
| eval Size="  3 " 
| eval Size = tonumber(trim(Size)) 
| eval type=typeof(Size)

Re: Not able to convert field from string to number

nilbak1 — Fri, 27 Mar 2020 06:53:42 GMT

Thanks @harsmarvania57 .
It worked perfectly.

Re: Not able to convert field from string to number

nilbak1 — Fri, 27 Mar 2020 06:55:16 GMT

@woodcock
I already tried using trim once but it didn't worked.
Anyways thanks for the help.