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!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...