Splunk Search

Calculating a sum of a list of numeric values

mszopa
Explorer

Hello everyone!
I have a field called word_score_cat1 that looks like this:
word_score_cat1=7.12500 1.5171 2.1923 1.6765 0.3239 0.96068 7.12500 0.000000 8.14285 0.000000 0.000000 0.000000 0.000000 0.000000 3.3530 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000
or
word_score_cat1=0.000000 0.96068 1.6011 1.5171 0.000000 0.5588 0.000000 6.33333 0.000000 0.65516
etc.
So it's a list of space separated numeric values, however the amount of values can vary. I need to create a field called cat1_sum that would return the sum of values in word_score_cat1.
If the number of values were constant, I would manage it but since it changes, I'm not sure how to do it. Can anybody think of how to handle this?
Than you all for your time,
Magda

Tags (3)
0 Karma
1 Solution

dineshraj9
Builder

You can create a multivalued field and then take the sum of the field this way.

<your search> | makemv word_score_cat1 delim=" " | mvexpand word_score_cat1 | stats sum(word_score_cat1) as cat1_sum

E.g. -

| makeresults | eval word_score_cat1="7.12500 1.5171 2.1923 1.6765 0.3239 0.96068 7.12500 0.000000 8.14285 0.000000 0.000000 0.000000 0.000000 0.000000 3.3530 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000" | makemv word_score_cat1 delim=" " | mvexpand word_score_cat1 | stats sum(word_score_cat1) as cat1_sum

View solution in original post

dineshraj9
Builder

You can create a multivalued field and then take the sum of the field this way.

<your search> | makemv word_score_cat1 delim=" " | mvexpand word_score_cat1 | stats sum(word_score_cat1) as cat1_sum

E.g. -

| makeresults | eval word_score_cat1="7.12500 1.5171 2.1923 1.6765 0.3239 0.96068 7.12500 0.000000 8.14285 0.000000 0.000000 0.000000 0.000000 0.000000 3.3530 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000" | makemv word_score_cat1 delim=" " | mvexpand word_score_cat1 | stats sum(word_score_cat1) as cat1_sum

DalJeanis
SplunkTrust
SplunkTrust

Two additional notes -
1) If you have more than one record, then you need to have a unique item to group the records on, so add a |streamstats count as recno line before the makemv and add by recno into the stats command.
2) You do not need to mvexpand, and splunk will still sum the values of the mv field.

 | makeresults 
 | eval word_score_cat1="7.12500 1.5171 2.1923 1.6765 0.3239 0.96068 7.12500 0.000000 8.14285 0.000000 0.000000 0.000000 0.000000 0.000000 3.3530 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000!!!!7.12500 1.5171 2.1923 1.6765 0.3239 0.96068 7.12500 0.100100 8.14285 0.000000 0.000000 0.000000 0.000000 0.000000 3.3530 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.300000 0.000000 0.000000 0.000000" 
 | makemv word_score_cat1 delim="!!!!" 
 | mvexpand word_score_cat1 
 | rename COMMENT as "The above just creates test data."

 | streamstats count as recno 
 | makemv word_score_cat1 delim=" " 
 | stats sum(word_score_cat1) as cat1_sum by recno

Technically, delim=" " is the default, so that part of the code is optional.

mszopa
Explorer

Thank you both for your help, it works perfectly well now!

0 Karma

dineshraj9
Builder

Yes, valid point made by @DalJeanis. If you want to take the sum for each event then you need to add streamstats use it as a serial# like shown above.

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk Life | Splunk is Officially Part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint. Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...