topic Re: Multiple clause table in Splunk Search

Multiple clause table

Brainstorms — Fri, 15 Oct 2021 14:25:36 GMT

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 sets	Set A	Set B
5	3	2

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!

Re: Multiple clause table

ITWhisperer — Fri, 15 Oct 2021 15:03:32 GMT

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

Re: Multiple clause table

richgalloway — Fri, 15 Oct 2021 15:32:00 GMT

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

Re: Multiple clause table

Brainstorms — Fri, 15 Oct 2021 15:55:17 GMT

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!