Archive
Highlighted

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

Explorer

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

0 Karma
Highlighted

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

Motivator

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
0 Karma
Highlighted

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

Explorer

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 otheraccesscount) is 0 it appears it can't count the hits when they are together. Will have to rethink it a little more.

0 Karma
Highlighted

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

Motivator

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.

Highlighted

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

Explorer

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.

0 Karma
Highlighted

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

Motivator

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

0 Karma
Highlighted

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

Explorer

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//wwwaccess_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)

0 Karma
Highlighted

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

Motivator

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

0 Karma
Highlighted

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

Explorer

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

Thanks!

0 Karma
Highlighted

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

Explorer

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

View solution in original post

0 Karma
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.