topic Re: Sum up values into a row with the data grouped by fields in Splunk Search

Sum up values into a row with the data grouped by fields

madakkas — Wed, 25 Apr 2018 03:20:41 GMT

I have the below sample data

Groups Values G1 1 G1 2 G1 1 G1 2 G3 3 G3 3 G3 3

I am looking to sum up the values field grouped by the Groups and have it displayed as below .

Groups  Values  Sum
G1  1   8
G1  5   8
G1  1   8
G1  1   8
G3  3   9
G3  3   9
G3  3   9

the reason is that i need to eventually develop a scorecard model from each of the Groups and other variables in each row. All help is appreciated.

thank You to all the splunk gurus here.

Re: Sum up values into a row with the data grouped by fields

p_gurav — Wed, 25 Apr 2018 04:03:48 GMT

Can you try somethins:

| makeresults | eval abc="G1 1,G1 5,G1 1,G1 1,G3 3,G3 3,G3 3"  | makemv delim="," abc | mvexpand abc | rex field=abc "(?P<Group>[^\s]+)\s(?P<Value>.+)" | stats sum(Value) list(Value) AS abc1 by Group  | mvexpand abc1

| makeresults | eval abc="G1 1 G1 5 G1 1 G1 1 G3 3 G3 3 G3 3"  | rex field=abc max_match=0 "(?P<Group>[^\s]+)\s(?P<Value>[^\s]+)" | eval ab=mvzip(Group,Value) | mvexpand ab | rex field=ab max_match=0 "(?P<Group>[^,]+),(?P<Value>.+)" | stats sum(Value) AS Sum list(Value) AS Value by Group | mvexpand Value

Re: Sum up values into a row with the data grouped by fields

woodcock — Wed, 25 Apr 2018 04:24:33 GMT

What do your raw events (fields) look like?

Re: Sum up values into a row with the data grouped by fields

madakkas — Wed, 25 Apr 2018 05:32:27 GMT

Raw Events are in a csv file

Re: Sum up values into a row with the data grouped by fields

TISKAR — Wed, 25 Apr 2018 06:17:45 GMT

@madakkas, Can youu try this please:

<yourBaseSearch>| eventstats sum(Value) by Group

For Example:

| makeresults | eval abc="G1 1 G1 5 G1 1 G1 1 G3 3 G3 3 G3 3"  | rex field=abc max_match=0 "(?P<Group>[^\s]+)\s(?P<Value>[^\s]+)" | eval ab=mvzip(Group,Value) | mvexpand ab | rex field=ab max_match=0 "(?P<Group>[^,]+),(?P<Value>.+)" | eventstats sum(Value) as sum by Group 
| fields Group Value sum