How to search for successful transactions only if a certain number of failed transactions are returned by client IP?

[howyagoin]

I'm looking to develop a table/report which shows me IP addresses in a HTTP access log whereby the client first generated more than, say, 1000 errors (404, 4xx) across unique urls, get that exact count, and then display the number of successful transactions from that same IP.

Whilst I can do a search such as:

index=www http_response="2*" [search index=www http_response="4*" | stats dc(url) as url_failures by clientip | where url_failures > 1000 ] | stats dc(url) as url_success by clientip

Obviously, that doesn't give me what I want, as what I want is a table output that looks like this:

clientip                      failures               successes
1.2.3.4                       1083                   320
5.6.7.8                       8312                   9004

I know that I've seen an example of this somewhere in the command examples/manual, but can't retrace what got me there. The important part is that I only want to count the successes for cases where a given IP has generated more than a certain number of failures.

Thanks for any pointers...I've tried doing this with join and subsearch and just can't get what I'm after. I suspect I'm going to have to throw some eval in there.

[musskopf]

Have you tried this approach?

index=www | eval status=IF(http_response<400,"success","failure")
| chart count by clientip,status
| where failure>1000

I haven't tried the search but should give what you're after. You could replace the count with dc(url) if that's what your accounting for.

[howyagoin]

I see where you're going with that, but, I have some alternative uses for the query that wouldn't have the benefit of a numeric value. Such as wanting to find the IP address of everyone who visited a particular URI path, regardless of result, and then trace back and see where else they visited on a site. I'll keep poking!