Splunk Search
Highlighted

Performing calculations on multi-valued fields

New Member

Hello, I am trying to perform calculations on multiple fields.

I am working with data in the format of Key='value1,value2,value3,value4' which can contain anywhere from 1 to 4 values.

Input:
height='32,12,14,13' or width='32'
as well as variance='3.24e-2,4.23e+3,1.12e-4,1.01e-3'

Query:
`
index=myindex sourcetype=mysourcetype
| transaction source
| foreach *
[eval <>=if(match('<>', "[\d.,e+-]+"), '<>', '')
| eval total=0 | eval count=1
| eval <>=replace(<>,"\'","")
| makemv delim="," '<>'
| mvexpand '<>'
| foreach <>
[eval total<>=total + <>
| eval count=count + 1]
| eval avg
<>=<>/count]
| table *, total, count
| transpose include_empty=false 10

`

Expected Output:
height=17.75
width=32
variance=1.0575e+3

Actual Output:
height=032,12,14,13, width=32
variance=03.24e-2,4.23e+3,1.12e-4,1.01e-3

Thank you in advance for any help provided!

0 Karma
Highlighted

Re: Performing calculations on multi-valued fields

SplunkTrust
SplunkTrust

can you share sample events and desired results?

0 Karma
Highlighted

Re: Performing calculations on multi-valued fields

New Member

Hi Adonio, here is an example event.

21-Mar-19 05:10:46 
total_Height_mm='66,41,29,28'

Each event has a different multi-value pair.
There are ~150+ events per source

Thank you!

0 Karma
Highlighted

Re: Performing calculations on multi-valued fields

SplunkTrust
SplunkTrust

I am still puzzled as to what is the desired output ...
can you share at least 10 events and the desired output from search?

0 Karma
Highlighted

Re: Performing calculations on multi-valued fields

New Member

I apologize, hopefully this will be clearer

If I had an event like this:
21-Mar-19 05:10:46
total_Height_mm='66,41,29,28'

I would expect to get:
avgtotalHeight_mm=(66 + 41 + 29 + 28)/4 = 41

Here are some additional events and their desired output:

21-Mar-19 05:10:46
total_Pressure_psi='16.16,16.16,16.16,16.16'

Output:
avgtotalPressure_psi = 16.16

21-Mar-19 05:10:46
total_Pressure_kpa='3.2405e+2,3.8095e+2,3.4152e+2,3.9155e+2'

Output:
avgtotalPressure_kpa=3.595175e+2

Thank you.

0 Karma
Highlighted

Re: Performing calculations on multi-valued fields

SplunkTrust
SplunkTrust

hope i understood your question, try this out.
also, plenty will depend on how the data is indexed and the fields you extract

| makeresults count=1
| eval data = "21-Mar-19 05:10:46 total_Height_mm='66,41,29,28';;;21-Mar-19 05:10:46 total_Pressure_psi='16.16,16.16,16.16,16.16';;;21-Mar-19 05:10:46 total_Pressure_kpa='3.2405e+2,3.8095e+2,3.4152e+2,3.9155e+2'"
| makemv delim=";;;" data 
| mvexpand data
| rex field=data "(?<time>\S+\s\S+)\s(?<fields>[^\=]+)\=\'(?<values>[^\']+)"
| makemv delim="," values
| mvexpand values
| stats avg(values) as avg_value by fields
0 Karma