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

[dsong555]

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?

[nabeel652]

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

[stoutrw · Accepted solution]

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]

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]

Glad it works!