Count and sum in splunk

Question

I have this sets of data:

name    fruit   location

mary    apple   east

ben pear    east

peter   pear    east

ben apple   north

ben mango   north

peter   mango   north

mary    orange  north

alice   pear    north

janet   pear    north

janet   mango   west

janet   mango   west

peter   mango   west

janet   pear    west

I want to get fields: name, number of fruits sent to name, number of fruits sent to name in location

I tried:

|stats sum(count) as scount_by_name by name

|stats count as count_by_namelocation (......filled with other formulas......) by name location

|Table count_by_namelocation scount_by_name

But it does not work, scount_by_name is empty, what's the correct syntax for this?

Answer 1

There are a couple of issues here.

The first stats command tries to sum the count field, but that field does not exist. This is why scount_by_name is empty.

More importantly, however, stats is a transforming command. That means its output is very different from its input. Specifically, the only fields passed on to the second stats are name and scount_by_name so the second stats does not see a location field so it can't count anything.

One workaround for consecutive stats commands is to use either streamstats or eventstats, which are not transforming commands.

This run-anywhere example should illustrate.

|  makeresults 
|  eval _raw="name    fruit   location
mary    apple   east
ben    pear    east
peter   pear    east
ben    apple   north
ben    mango   north
peter   mango   north
mary    orange  north
alice   pear    north
janet   pear    north
janet   mango   west
janet   mango   west
peter   mango   west
janet   pear    west" 
| multikv forceheader=1
| streamstats count as scount_by_name by name
| streamstats count as count_by_namelocation by name location
| table count_by_namelocation scount_by_name