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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...