Splunk Search

Multiple clause table

Brainstorms
Explorer

Hey all, 

I got a really helpful response last time and now I'm back with another question. 

I have a search with the same sourcetype that I want to run multiple clauses against to return different results in a table for comparison. Example:

sourcetype = xyz
| where (color == "red" OR color == "blue" OR color == "purple" OR color == "green") AND (crayon == "crayola" OR crayon == "prisma" OR crayon == "offBrand" OR crayon == "brandA")
|  some stuff here that matches the search to a lookup
| stats count(name) as "All sets" by Type (say name and type is the information pulled from the lookup)
| where (color == "red" OR color == "blue) AND (crayon != "crayola" AND crayon != "offBrand")
| stats count(name) as "Set A" by Type
| where (color == "red" OR color == "blue) AND (crayon != "prisma" AND crayon != "brandA")
| stats count(name) as "Set B" by Type 

The end result I want is this:

 

All setsSet ASet B
532

 

I know it's bad practice to use join and append. I also know the where clauses are supposed to be higher up. I'm just not sure how to achieve this.

I can get the 'All sets' just fine of course but after that nothing works.

Any help for this newbie would be much appreciated. Thanks!

Labels (4)
0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

The stats command transforms the data so that only named fields are present.  That means the color and crayon fields are not available to subsequent where commands.

Also, the where command discards events, which means the second where will throw out all "prisma" crayons leaving none for the last where to find.

The stats command can compute more than one statistic at a time and has an eval function available to handle complex expressions.

See if this helps

sourcetype = xyz color IN ("red", "blue", "purple", "green") AND crayon IN ("crayola", "prisma", "offBrand", "brandA")
|  some stuff here that matches the search to a lookup
| stats count(name) as "All sets", sum(eval((color == "red" OR color == "blue) AND (crayon != "crayola" AND crayon != "offBrand"))) as "Set A", sum(eval((color == "red" OR color == "blue) AND (crayon != "prisma" AND crayon != "brandA"))) as "Set B" by Type

 

---
If this reply helps you, Karma would be appreciated.

View solution in original post

richgalloway
SplunkTrust
SplunkTrust

The stats command transforms the data so that only named fields are present.  That means the color and crayon fields are not available to subsequent where commands.

Also, the where command discards events, which means the second where will throw out all "prisma" crayons leaving none for the last where to find.

The stats command can compute more than one statistic at a time and has an eval function available to handle complex expressions.

See if this helps

sourcetype = xyz color IN ("red", "blue", "purple", "green") AND crayon IN ("crayola", "prisma", "offBrand", "brandA")
|  some stuff here that matches the search to a lookup
| stats count(name) as "All sets", sum(eval((color == "red" OR color == "blue) AND (crayon != "crayola" AND crayon != "offBrand"))) as "Set A", sum(eval((color == "red" OR color == "blue) AND (crayon != "prisma" AND crayon != "brandA"))) as "Set B" by Type

 

---
If this reply helps you, Karma would be appreciated.

Brainstorms
Explorer

Rich, your solutions are always fantastic and well explained. I appreciate the extra explanation because it helps me see where I went wrong.

Thank you so much!

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Given that all your counts are based on the presence of state (from the lookup), you could do something like this

| makeresults count=200
| eval state=if(random()%2=0,"state",null())
| eval colour=mvindex(split("red,blue,purple,green",","),random()%4)
| eval crayon=mvindex(split("crayola,prisma,offBrand,brandA",","),random()%4)




| eval setA=if((colour == "red" OR colour == "blue") AND (crayon != "crayola" AND crayon != "offBrand"),1,0)
| eval setB=if((colour == "red" OR colour == "blue") AND (crayon != "prisma" AND crayon != "brandA"),1,0)
| where isnotnull(state)
| stats count as allsets sum(setA) as setA sum(setB) as setB
0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...