topic Multiple Where Claus in the same search in Splunk Search

Multiple Where Claus in the same search

hartfoml — Wed, 27 Mar 2013 18:18:42 GMT

I have a search like this

sourcetype=foo-bar category=foo | stats count by category | where count>5

I have 5 category's that show in the table with a count, when count is more than 5

I would like to run the same search with multiple thresholds for each category line this

sourcetype=foo-bar category=foo | stats count by category | where count(category=1)>5 | Where count(category=2)>10 | Where count(category=3)>15 etc.

Anyone know how to do this without making 5 different searches

Re: Multiple Where Claus in the same search

tiberious726 — Wed, 27 Mar 2013 18:35:34 GMT

Use the logical operators (AND OR NOT etc, note that they have to be capitalized). Also stats commands are allow to have a where clause, so you could:

sourcetype=foo-bar category=foo | stats count by category where count(category=1)>5 OR count(category=2)>10 OR count(category=3)>15

EDIT: this isn't entirely true, splunk's parser (into the prefix-notation it uses internally) can't handle this for whatever reason. Instead you need to make the operators explicitally binary, that is,

sourcetype=foo-bar category=foo | stats count by category where (count(category=1)>5 OR count(category=2)>10) OR count(category=3)>15

Re: Multiple Where Claus in the same search

hartfoml — Wed, 27 Mar 2013 19:28:01 GMT

Sorry this didn't seem to work

I get this error "Error in 'stats' command: Repeated group-by field 'OR'" when I try the OR operator with the stats command

Re: Multiple Where Claus in the same search

tiberious726 — Wed, 27 Mar 2013 22:40:28 GMT

copy and paste the actual search sting you are using. This works fine for me, and the error message you posted looks like you tried to put two 'OR's in a row (like "... OR OR ...") which will not work.

Re: Multiple Where Claus in the same search

tiberious726 — Wed, 27 Mar 2013 22:43:46 GMT

LOL, I take that back, apparently splunk's parser is much more stupid than I gave it credit for, x OR y OR z will not work in a where clause, however (x OR y) OR z will indeed work, just use parens to make your operators explicitly binary.

Re: Multiple Where Claus in the same search

hartfoml — Thu, 28 Mar 2013 15:17:22 GMT

Sorry this didn't work either. I must be doing something wrong???

When I run this search:[sourcetype="foo-bar" category="foo" | stats count by dst ]

I get a table with 3 IP's and a count for each IP all with a count higher than 3

When I run the same search like this [ sourcetype="foo-bar" category="foo" | stats count by dst Where (count(dst=x.x.x.1)>0 OR count(dst=x.x.x.2)>0 OR count(dst=x.x.x.3)>0) ]

I get no results from this search. If I change the OR to AND I still get no results.

Re: Multiple Where Claus in the same search

tiberious726 — Mon, 01 Apr 2013 04:52:47 GMT

Sorry if I wasn't clear. For whatever reason splunk doesn't treat OR as associative in the where clause of a stats command. This means you have to explicitly treat it as a binary operator. This means you have to do this:
"where (count(foo) > 1 OR count(bar) >6 ) OR count(foobar) < 7"
Notice the parenthesis. To abstract away, you have to do (x or y) or z , rather than x or y or z

Re: Multiple Where Claus in the same search

jbanerje — Tue, 28 Feb 2017 16:25:46 GMT

Multiple conditions can be checked by the where clause as shown below :

| inputlookup test.csv | where like(field1, "IP") and not like(field2, "Pass")

Re: Multiple Where Claus in the same search

DalJeanis — Tue, 28 Feb 2017 20:41:42 GMT

Here's one way. Put the category names and tests into the eval mytests statement, where the number is not 5. Ignore any where the number IS 5, because that will default after the join.

sourcetype=foo-bar category=foo | stats count by category 
| join type=left category 
   [| makeresults | eval mytests="category1>7 category3>5 category6>9" 
    | makemv mytests | mvexpand mytests | makemv delim=">" mytests 
    | eval category=mvindex(mytests,0), limit=mvindex(mytests,1) 
    | table category, limit
    ]
| eval limit=coalesce(limit,5)

So now each category knows what its individual limit is.

| where count>limit

And there you go.