topic Re: Trying to get stats output for 2 fields after the "by" in Splunk Search

Trying to get stats output for 2 fields after the "by"

dsong555 — Wed, 22 May 2019 21:05:32 GMT

I have data that looks like this:

event,myField,myHost,myCategory
yes,a,host1,category1
yes,b,host1,category1
yes,c,host2,category1
yes,a,host2,category3
yes,b,host2,category2

Here's my search:

sourcetype="sourcetype1"| where event="yes" | eval aTotal=if(myField="a", 1, 0) | eval bTotal=if(myField="b", 1, 0) | eval cTotal=if(myField="c", 1, 0) | stats sum(aTotal) as A, sum(bTotal) as B, sum(cTotal) as C by "myHost"

So currently my output looks like this:

myHost, A, B, C
host1, 1, 1, 0
host2, 1, 1, 1

I'm looking to get output that looks like:

myHost, myCategory, A, B, C
host1, category1, 1, 1, 0
host2, category1, 0, 0, 1
host2, category2, 0, 1, 0
host2, category3, 1, 0, 0

Can anyone help?

Re: Trying to get stats output for 2 fields after the "by"

stoutrw — Wed, 22 May 2019 22:23:41 GMT

You should just be able to do :

Re: Trying to get stats output for 2 fields after the "by"

nabeel652 — Thu, 23 May 2019 00:04:35 GMT

Try this:

| makeresults | eval data=" yes,a,host1,category1|yes,b,host1,category1|yes,c,host2,category1|yes,a,host2,category3|yes,b,host2,category2" | makemv data delim="|" | mvexpand data | rex field=data "(?<event>[^\,]*)\,(?<myField>[^\,]*)\,(?<myHost>[^\,]*)\,(?<myCategory>[^\,]*)$" | table event myField, myHost, myCategory | stats count(eval(myField=="a")) as A, count(eval(myField=="b")) as B, count(eval(myField=="c")) as C by myHost, myCategory

Re: Trying to get stats output for 2 fields after the "by"

dsong555 — Thu, 23 May 2019 17:27:40 GMT

Thank you. I swear I tried using two fields after the 'by' in the stats command earlier and it didn't work, but I'm glad it works now.

Re: Trying to get stats output for 2 fields after the "by"

stoutrw — Thu, 23 May 2019 17:39:49 GMT

Glad it works!