topic Finding clientip where more than one of event X but none of event Y in Splunk Search

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

jewhite — Thu, 22 Mar 2012 15:56:15 GMT

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

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

Stephen_Sorkin — Thu, 22 Mar 2012 16:27:24 GMT

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

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

jewhite — Mon, 28 Sep 2020 11:33:59 GMT

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.

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

Stephen_Sorkin — Thu, 22 Mar 2012 17:19:30 GMT

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.

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

jewhite — Mon, 26 Mar 2012 13:27:06 GMT

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.

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

Stephen_Sorkin — Mon, 26 Mar 2012 13:44:57 GMT

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

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

jewhite — Mon, 28 Sep 2020 11:34:45 GMT

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)

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

Stephen_Sorkin — Mon, 26 Mar 2012 16:25:50 GMT

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

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

jewhite — Mon, 26 Mar 2012 16:43:15 GMT

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

Thanks!

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

jewhite — Mon, 26 Mar 2012 16:45:14 GMT

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