Finding clientip where more than one of event X but none of event Y

[jewhite]

I want to find clientip's (in apache access_combined logs) where more than one event occurred (e.g. status=200 file=FOO) but the same client IP has no other sort of activity ... (e.g. clientip=OneofThoseIPs NOT(file=FOO) | stats count by file | where count = 0)

I can think of how to do it in a SQL WHERE ... AND clause/query, but am struggling in SPLUNK to accomplish it.

Any help appreciated ... thanks

[jewhite · Accepted solution]

Final solution ... Thanks for the help!

source="/var/log/*/*access_log" | stats count(eval(match(file,"foo") AND status=200)) as countFOO count(eval(match(file,"^foo"))) as countNonFOO by clientip
| where countFOO > 0 AND countNonFOO < 1 | sort by clientip

[Stephen_Sorkin]

You can use eval within stats to count the events that match a predicate. For example:

... | stats count(eval(status=200 AND file="FOO")) as successful_foo_count count(eval(status=200 AND file!="FOO")) as other_access_count by clientip
| where successful_foo_count>0 AND other_access_count==0

[jewhite]

That's the path I was headed down ... see below for final solution (had to figure out how to get the negative match).

Thanks!

[Stephen_Sorkin]

The first one will match the exact string "*foo" for file, which is not what you want.

Instead, write stats count(eval(match(file, ".*foo"))) as fooCount by clientip

[jewhite]

interesting ... because
source="/var/log/*/*access_log" | stats count(eval(file="*foo" AND status=200)) as fooCount by clientip
| where fooCount > 10 | sort by clientip

returns 0 results, but

source="/var/log/*/www*access_log" | stats count(eval(file="fullFileName.foo" AND status=200)) as fooCount by clientip
| where fooCount > 10 | sort by clientip

returns a list of results (both return lots of matching events)

[Stephen_Sorkin]

Yes. You thelike() function implements SQL's LIKE (where "%" is used as a wildcard, match() implements regex matching functionality (where ".*" is a wildcard) and searchmatch() takes a standard Splunk search expression (like searchmatch("field=foo") or searchmatch("field=\"foo bar\""). You can read about these, and other, eval functions at http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/CommonEvalFunctions

[jewhite]

Still playing with it ... but here's a basic question, I haven't found a clear answer to and testing seems to indicate a 'no' answer ...

Can you wildcard in eval? [ e.g. eval(field="*foo") ]

I'm playing with match options as well ... seems to work a little better, but still not quite there.

[Stephen_Sorkin]

I don't quite follow. This search should be able to count the number of occurrences of any set of types of events, globally, for each clientip.

[jewhite]

Thanks!

The count(eval()) as fieldLabel is what I needed ... Doing either 'stats count ...' operation works by itself. Testing it more though, the issue is that since one of the generated fields (the other_access_count) is 0 it appears it can't count the hits when they are together. Will have to rethink it a little more.