If you are getting suspicious traffic, it often comes from a range of IP addresses. The below queries get the count using the first three octets of an IP address, so for “192.168.0.1” it will return “192.168.0” and a count of all traffic for the 192.168.0.1/24 range (192.168.0.1 – 192.168.0.255).
To get the first three octets of the IP address, and the count of hits use:
SELECT EXTRACT_PREFIX(c-ip, 2, '.') as ip-range, COUNT(ip-range) as requestcount FROM '[LOGFILEPATH]' WHERE date > SUB(TO_LOCALTIME(SYSTEM_TIMESTAMP()), TIMESTAMP('0000-01-02 00:00', 'yyyy-MM-dd HH:mm')) GROUP BY ip-range ORDER BY COUNT(ip-range) DESC
To get the first three octets of the X-Forwarded-For IP address (see how to add the X-Forwarded-For address to your log file), and the count of hits use:
SELECT EXTRACT_PREFIX(X-Forwarded-For, 2, '.') as ip-range, COUNT(ip-range) as requestcount FROM '[LOGFILEPATH]' WHERE date > SUB(TO_LOCALTIME(SYSTEM_TIMESTAMP()), TIMESTAMP('0000-01-02 00:00', 'yyyy-MM-dd HH:mm')) GROUP BY ip-range ORDER BY COUNT(ip-range) DESC
Digging Deeper
You can then obtain more detailed lists of IP addresses using this query – just update the ip-range in the WHERE-clause based on what you find in the above queries:
SELECT c-ip, EXTRACT_PREFIX(c-ip, 2, '.') as ip-range, COUNT(c-ip) as requestcount FROM '[LOGFILEPATH]' WHERE date > SUB(TO_LOCALTIME(SYSTEM_TIMESTAMP()), TIMESTAMP('0000-01-02 00:00', 'yyyy-MM-dd HH:mm')) AND ip-range = '192.168.0' GROUP BY c-ip, ip-range ORDER BY COUNT(c-ip) DESC
And once again, for X-Forwarded-For IP addresses:
SELECT X-Forwarded-For, EXTRACT_PREFIX(X-Forwarded-For, 2, '.') as ip-range, COUNT(X-Forwarded-For) as requestcount FROM '[LOGFILEPATH]' WHERE date > SUB(TO_LOCALTIME(SYSTEM_TIMESTAMP()), TIMESTAMP('0000-01-02 00:00', 'yyyy-MM-dd HH:mm')) AND ip-range = '192.168.0' GROUP BY X-Forwarded-For, ip-range ORDER BY COUNT(X-Forwarded-For) DESC