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
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
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
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
That's the path I was headed down ... see below for final solution (had to figure out how to get the negative match).
Thanks!
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
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)
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
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.
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.
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.