Group NGINX access log by client IP

I have NGINX log setup to be processed by Logs How can I make a chart (or a table) to show IPs with the highest amount of access? (Essentially I am looking to look for people botting the site) but I’m not entirely sure how to do that.

Thanks!

Hi, @sml: Try this (I am not sure about WHERE logtype; I don’t have NGINX logs to test it):

SELECT count(*) FROM Log WHERE logtype = 'nginx' FACET clientip

Thanks, this works — how do I exclude an IP range?

For example, let’s say I want to exclude client IPs from 168.77.x.x

I can see that I can exclude individual IPs but when I tried 168.77.0.0/24it doesn’t work, specifically I wrote this:

SELECT count(*) FROM Log WHERE logtype = 'nginx' WHERE clientip NOT IN ('168.77.0.0/24') FACET clientip

With this query in hand, is it possible to have NR alert me when an IP comes from a significant percentage of all transaction? (For example, send an alert when an IP made up with 5% of all requests within an hour?)

How do I exclude an IP range?

SELECT count(*) 
FROM Log 
WHERE logtype = 'nginx'
  AND clientip NOT LIKE '167.77.%'
FACET clientip

Is it possible to have NR alert me when an IP comes from a significant percentage of all transaction?

I don’t know how to have a query return the percentage of requests for each IP address. Perhaps someone else will have a suggestion.

1 Like

What about an alert condition whenever a count is above a specific threshold? When I tried to use this exact expression in alert/condition, I actually ran into an issue when it said that the facet is too large — which I gathered as it not liking that there are so many unique values. But how I might setup such an alert since I won’t know the exact values in advance?

From the documentation:

Faceted queries can return a maximum of 5000 values for static and anomaly conditions.

Important

If the query returns more than the maximum number of values, the alert condition can’t be created. If you create the condition and the query returns more than this number later, the alert will fail. Modify your query so that it returns a fewer number of values.

You might try using a nested aggregation query that returns only IP addresses with a count above a certain value.