Multiple Where Claus in the same search

[hartfoml]

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

[DalJeanis]

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.

[jbanerje]

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

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

[tiberious726]

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

[tiberious726]

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

[hartfoml]

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.

[tiberious726]

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.

[tiberious726]

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.

[hartfoml]

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