topic Need Help with sum over two columns without subsearch in Splunk Search

Need Help with sum over two columns without subsearch

PPape — Tue, 30 Aug 2016 12:56:31 GMT

Hi Guys,

I need some help with a stats command.

Given is Data like this csv

Round,Player1,Player2,ScorePlayer1,ScorePlayer2
1,Harry,Tom,5,1
2,Eva,Mike,1,0
3,Harry,Eva,3,4
4,Mike,Tom,4,6
5,Tom,Harry,3,2
6,Mike,Eva,7,5
7,Eva,Tom,2,5

All I want to have is a stats command that gives me a table with the total amount of points for every player.
I managed to do it with a subsearch but this feels kind of weird... there has to be a better solution.

Re: Need Help with sum over two columns without subsearch

sundareshr — Tue, 30 Aug 2016 13:14:43 GMT

Try this

base search here | eval player=player1.";".player2 | eval score=ScorePlayer1.";".ScorePlayer2 | makemv player delim=";" | makemv score  delim=";" | eval z=mvzip(player, score) | mvexpand z | rex field=z "(?<player>[^;]+);(?<score>.*)" | stats sum(score) as score by player

Re: Need Help with sum over two columns without subsearch

PPape — Tue, 30 Aug 2016 13:22:48 GMT

Thank you very much. This worked and is faster than my subsearch.

Re: Need Help with sum over two columns without subsearch

PPape — Wed, 31 Aug 2016 09:49:41 GMT

There is a little mistake. The mvzip command uses an comma as seperator so the semikolon in the rex command must be replaced with an comma. Than it works perfectly! Thank you again