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
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
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
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.
Thank you both for your help, it works perfectly well now!
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.