Forum Moderators: open
The list of IPs to match against the table are stored in another table as varchar(16).
Currently, we loop through each row of the list to check, convert to integer, then check if the result is within any of the ranges in the table. All this is done in a stored procedure using fetch to loop through the table to match. This is a nightly procedure matching our log files against the banned IPs table.
Is there a better/more efficient way to do this?
If that works for storage then you could optimise the search it by using a set based set solution instead of the fetch loop.
SELECT Banned.IP FROM Banned INNER JOIN Todays
ON Banned.IP = Todays.IP
Would give the matching list.
Although you might now need a loop to create the banned ranges!
This is a nightly procedure matching our log files against the banned IPs table.
I don't understand why you are matching log file IPs against a "banned" list. Is it just for statistical purposes?
Couldn't you just use your list of banned IPs in iptables or something to block them at the firewall level?
don't understand why you are matching log file IPs against a "banned" list.
In regards to putting the actual IPs in a table isntead of the ranges, that's something I've thought about as well. It's probably a little more efficient than what we're doing now. I was just hoping for a magic bullet that would give a much bigger efficiency boost.
SELECT ip FROM logtable L WHERE EXISTS (SELECT * FROM banned_ips WHERE ip_low<=L.ip and ip_hi>=L.ip)
Of course, you would need a quick query to convert all your varchar IPs in your logtable to numerics (the [ip] column in the query above, but this is easy.)
Assume ip_low and ip_hi are the lower and upper numeric IP ranges.
The query would give you those IPs that fall within a banned range and would be fast if ip_low and ip_hi were indexed.
[edited by: FalseDawn at 2:17 am (utc) on Nov. 30, 2006]
