Search code examples
sqlamazon-redshiftipv4

In redshift, group by IP network

I would like to find the most commonly banned networks in a redshift table. I have tried this:

select network(set_masklen(ip::inet,8)), count(1)
from banlist where status='BLOCKED' 
group by 1 order by 2 desc limit 10;

And got the following error:

INFO:  Function ""network"(inet)" not supported.
INFO:  Function "set_masklen(inet,integer)" not supported.
INFO:  Function "inet(text)" not supported.
INFO:  Function ""network"(inet)" not supported.
INFO:  Function "set_masklen(inet,integer)" not supported.
INFO:  Function "inet(text)" not supported.
ERROR:  Specified types or functions (one per INFO message) not supported on Redshift tables.

OTOH, this works:

# select network(set_masklen('10.0.0.1'::inet, 24));                                                          network
-------------
 10.0.0.0/24
(1 row)
Solution

  • While everyone else's responses regarding creating a UDF are probably a fantastic option, if you're willing to give up some of the flexibility and just get either class A, class B, or class C subnets, you can use SPLIT_PART and the concatenation operator to get a (not super) quick and dirty solution.

    select SPLIT_PART(ip_address, '.', 1) || '.' || SPLIT_PART(ip_address, '.', 2) || '.' || SPLIT_PART(ip_address,'.', 3) as network, count(1) as mc from banlist group by network order by mc desc limit 10;