Splunk Search

Not able to convert field from string to number

nilbak1
Communicator

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?.*)"

alt text

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

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.

0 Karma
1 Solution

harsmarvania57
Ultra Champion

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>>) ]

View solution in original post

woodcock
Esteemed Legend

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)
0 Karma

nilbak1
Communicator

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

0 Karma

harsmarvania57
Ultra Champion

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>>) ]

nilbak1
Communicator

Thanks @harsmarvania57 .
It worked perfectly.

0 Karma
Get Updates on the Splunk Community!

Unlock New Opportunities with Splunk Education: Explore Our Latest Courses!

At Splunk Education, we’re dedicated to providing top-tier learning experiences that cater to every skill ...

Technical Workshop Series: Splunk Data Management and SPL2 | Register here!

Hey, Splunk Community! Ready to take your data management skills to the next level? Join us for a 3-part ...

Spotting Financial Fraud in the Haystack: A Guide to Behavioral Analytics with Splunk

In today's digital financial ecosystem, security teams face an unprecedented challenge. The sheer volume of ...