Splunk Search

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

dsong555
Engager

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?

Tags (1)
0 Karma
1 Solution

stoutrw
Path Finder

You should just be able to do :

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", "myCategory"

View solution in original post

nabeel652
Builder

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
0 Karma

stoutrw
Path Finder

You should just be able to do :

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", "myCategory"

dsong555
Engager

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.

stoutrw
Path Finder

Glad it works!

0 Karma
Get Updates on the Splunk Community!

.conf25 Registration is OPEN!

Ready. Set. Splunk! Your favorite Splunk user event is back and better than ever. Get ready for more technical ...

Detecting Cross-Channel Fraud with Splunk

This article is the final installment in our three-part series exploring fraud detection techniques using ...

Splunk at Cisco Live 2025: Learning, Innovation, and a Little Bit of Mr. Brightside

Pack your bags (and maybe your dancing shoes)—Cisco Live is heading to San Diego, June 8–12, 2025, and Splunk ...