Splunk Search

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

SplunkTrust
SplunkTrust

can you share sample events and desired results?

0 Karma

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

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

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:
avg_total_Height_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:
avg_total_Pressure_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:
avg_total_Pressure_kpa=3.595175e+2

Thank you.

0 Karma

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
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!