Multiple clause table

[Brainstorms]

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!

[richgalloway · Accepted solution]

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

[Brainstorms]

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!

[ITWhisperer]

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