If your database stores user-generated content along with client IPs, you may need a way to detect and block abusive users. A common approach is to analyze database records to identify IPs with excessive activity, group them by subnet, and apply firewall rules to mitigate potential abuse.

First, we retrieve a list of IPs with multiple records over the past 21 days that exhibit patterns of potential abuse—such as frequent spam submissions, excessive requests, or other suspicious activity. The exact criteria will depend on your use case—whether you’re tracking spam submissions, excessive requests, or other forms of abuse. The following query is just an example:

SELECT
    user_ip,
    COUNT(*) AS record_count
FROM short_url_data
WHERE date_created >= NOW() - INTERVAL 21 DAY
GROUP BY user_ip
HAVING record_count > 1
ORDER BY user_ip DESC

This query outputs two columns: the first contains IP addresses, which we’ll use to compile our final list of IP ranges to block. The results are then saved to a text file for further processing.

...
98.8.35.1	8
98.191.0.47	4
98.191.0.37	12
98.188.47.132	3
98.181.137.83	15
98.181.137.80	3
98.178.72.21	14
98.175.31.222	5
98.175.31.195	4
...

With our saved file, we use awk to group IPs by their third octet and sum their record counts:

awk '{split($1, a, "."); key = a[1] "." a[2] "." a[3]; sum[key] += $2} END {for (k in sum) print k, sum[k]}' spam-ip-list.txt | sort -k2,2nr

This extracts the first three octets of each IP, aggregates the counts, and sorts the results in descending order based on the total occurrences.

...
68.71.243 2
68.71.252 2
68.71.254 2
72.205.54 2
84.33.11 2
86.95.2 2
91.237.124 2
91.94.11 2
91.94.12 2
91.94.14 2
91.94.15 2
91.94.7 2
91.94.8 2
92.101.243 2
94.249.160 2

Before applying firewall rules, it’s important to manually review the generated list of suspicious IP ranges. We generally take a closer look at those that appear at the top of the list which could potentially be from known malicious spam networks.

At this point we can also adjust the threshold for these results and only include those that have more than 10 hits.

awk '{split($1, a, "."); key = a[1] "." a[2] "." a[3]; sum[key] += $2} END {for (k in sum) if (sum[k] > 10) print k, sum[k]}' spam-ip-list.txt | sort -k2,2nr

An excerpt of the results might look like this:

196.242.195 1849
188.126.89 1783
196.242.178 1563
121.143.144 1148
60.105.240 1035
85.208.115 1005
185.158.106 788
191.101.250 744
51.89.204 693
...

Once you’ve identified the suspicious IP ranges and filtered out any false positives, you can prepare the final list of ranges to block.

We’ll use awk again to append .0/24 to each IP range, formatting them as CIDR blocks:

awk '{split($1, a, "."); print a[1] "." a[2] "." a[3] ".0/24"}' spam-ip-list.txt | sort -u > spam-ip-list-grouped.txt

We will now create a custom chain named SPAMCHAIN to keep our rules organized and avoid cluttering the main INPUT chain. Once created, we need to link it to INPUT; otherwise, it won’t be used. By inserting SPAMCHAIN at position 1 in INPUT, we ensure that incoming packets are checked against our custom rules first before any other processing takes place.

iptables -N SPAMCHAIN
iptables -I INPUT 1 -j SPAMCHAIN

We’ll use a shell script to iterate through a list of IP addresses and add rules to our custom iptables chain.

#!/bin/bash

SPAMIPLIST=spam-ip-list-grouped.txt

/bin/egrep -v "^#|^$|:" $SPAMIPLIST | sort | uniq | while read IP
do
	    iptables -A SPAMCHAIN -s $IP -j DROP
done

Verify we have all our rules added to the chain.

iptables -L SPAMCHAIN -n

Commit and save rules.

iptables-save

Look at some statistics for the rules we just created.

iptables -L SPAMCHAIN -n -v --line-numbers