Skip to content

Instantly share code, notes, and snippets.

@reyjrar

reyjrar/client_by_network.sql

Created October 14, 2012 10:20
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save reyjrar/3888185 to your computer and use it in GitHub Desktop.
Save reyjrar/3888185 to your computer and use it in GitHub Desktop.
Download ZIP
Grouping IP's by /24's in PgSQL
Raw
client_by_network.sql
select
CAST(regexp_replace( CAST( ip | inet '0.0.0.255' as TEXT), '255/32$', '0') || '/24' as inet) as network,
regexp_replace( CAST( ip | inet '0.0.0.255' as TEXT), '255/32$', '0') as network_addr,
count(1) as clients,
to_char(min(first_ts), 'YYYY-MM-DD HH24:MI') as first_ts,
to_char(max(last_ts), 'YYYY-MM-DD HH24:MI') as last_ts,
bool_or(is_local) as is_local
from client
group by ip | inet '0.0.0.255'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment