From bb6abb698da039bd6609b730fc1052444eccbf42 Mon Sep 17 00:00:00 2001 From: "David M. Syzdek" Date: Fri, 24 May 2019 12:43:10 -0800 Subject: Updating audit functions --- notes/projects/radius/sql/ippool-demo.sql | 62 ++++++++ notes/projects/radius/sql/ippool-functions.sql | 203 +++++++++++++++++++++++++ notes/projects/radius/sql/untitled.sql | 1 + 3 files changed, 266 insertions(+) diff --git a/notes/projects/radius/sql/ippool-demo.sql b/notes/projects/radius/sql/ippool-demo.sql index d0a992b..08a4157 100644 --- a/notes/projects/radius/sql/ippool-demo.sql +++ b/notes/projects/radius/sql/ippool-demo.sql @@ -155,6 +155,68 @@ SELECT ip_autoassign ); +-- load large blocks +SELECT netblock_add + ( netAddress := '209.193.0.0/22', + poolName := 'DHCP', + regionName := 'Anchorage' + ); +SELECT netblock_add + ( netAddress := '209.193.5.0/24', + poolName := 'DHCP', + regionName := 'Anchorage' + ); +SELECT netblock_add + ( netAddress := '209.193.6.0/23', + poolName := 'DHCP', + regionName := 'Anchorage' + ); +SELECT netblock_add + ( netAddress := '209.193.8.0/21', + poolName := 'DHCP', + regionName := 'Anchorage' + ); +SELECT netblock_add + ( netAddress := '209.193.16.0/20', + poolName := 'DHCP', + regionName := 'Anchorage' + ); +SELECT netblock_add + ( netAddress := '209.193.32.0/19', + poolName := 'DHCP', + regionName := 'Anchorage' + ); +SELECT netblock_add + ( netAddress := '209.112.128.0/18', + poolName := 'DHCP', + regionName := 'Soldotna' + ); +SELECT netblock_add + ( netAddress := '209.112.192.0/19', + poolName := 'DHCP', + regionName := 'Soldotna' + ); +SELECT netblock_add + ( netAddress := '66.230.80.0/20', + poolName := 'DHCP', + regionName := 'Fairbanks' + ); +SELECT netblock_add + ( netAddress := '66.230.96.0/19', + poolName := 'DHCP', + regionName := 'Anchorage' + ); +SELECT netblock_add + ( netAddress := '69.161.0.0/19', + poolName := 'DHCP', + regionName := 'Juneau' + ); +SELECT netblock_add + ( netAddress := '63.140.64.0/18', + poolName := 'DHCP' + ); + + -- add missing IP addresses to netblock -- SELECT ip_add_network( ipNetwork := '209.112.192.0/19' ); -- SELECT ip_add_network( ipNetwork := '209.112.128.0/18' ); diff --git a/notes/projects/radius/sql/ippool-functions.sql b/notes/projects/radius/sql/ippool-functions.sql index df9e27b..f7cc8c5 100644 --- a/notes/projects/radius/sql/ippool-functions.sql +++ b/notes/projects/radius/sql/ippool-functions.sql @@ -696,6 +696,209 @@ END; $$; +/* + * audits netblocks + * + * % SELECT * FROM netblock_audit(); + * + * Returns table of netblocks + */ +CREATE OR REPLACE FUNCTION pool_audit +( +) + RETURNS TABLE + ( + poolName VARCHAR(32), + addressType TEXT, + ipAssignedFree BIGINT, + ipAssignedBusy BIGINT, + ipAvailable BIGINT, + ipBusyout BIGINT, + ipTotal BIGINT, + description TEXT + ) + LANGUAGE plpgsql + SECURITY DEFINER + AS $$ +DECLARE +BEGIN + + RETURN QUERY + SELECT DISTINCT pool.name AS poolName, + 'regional' AS addressType, + ( + SELECT COUNT( address ) + FROM ip + INNER JOIN netblock AS nb2 + ON nb2.id = ip.netblockId + WHERE nb2.regionId IS NOT NULL + AND nb2.poolId = pool.id + AND nb2.busyout IS FALSE + AND ip.nasId IS NOT NULL + ) AS ipAssignedFree, + ( + SELECT COUNT( address ) + FROM ip + INNER JOIN netblock AS nb2 + ON nb2.id = ip.netblockId + WHERE nb2.regionId IS NOT NULL + AND nb2.poolId = pool.id + AND nb2.busyout IS TRUE + AND ip.nasId IS NOT NULL + ) AS ipAssignedBusy, + ( + SELECT COUNT( address ) + FROM ip + INNER JOIN netblock AS nb2 + ON nb2.id = ip.netblockId + WHERE nb2.regionId IS NOT NULL + AND nb2.poolId = pool.id + AND nb2.busyout IS FALSE + AND ip.nasId IS NULL + ) AS ipAvailable, + ( + SELECT COUNT( address ) + FROM ip + INNER JOIN netblock AS nb2 + ON nb2.id = ip.netblockId + WHERE nb2.regionId IS NOT NULL + AND nb2.poolId = pool.id + AND nb2.busyout IS TRUE + ) AS ipBusyout, + ( + SELECT COUNT( address ) + FROM ip + INNER JOIN netblock AS nb2 + ON nb2.id = ip.netblockId + WHERE nb2.regionId IS NOT NULL + AND nb2.poolId = pool.id + ) AS ipTotal, + ( + 'Regional addresses for ' + || upper(pool.name) + || ' pool' + ) AS description + FROM netblock + INNER JOIN pool + ON pool.id = netblock.poolId + UNION ALL + SELECT DISTINCT pool.name AS poolName, + 'non-regional' AS addressType, + ( + SELECT COUNT( address ) + FROM ip + INNER JOIN netblock AS nb2 + ON nb2.id = ip.netblockId + WHERE nb2.regionId IS NULL + AND nb2.poolId IS NULL + AND nb2.busyout IS FALSE + AND ip.nasId IS NOT NULL + ) AS ipAssignFree, + ( + SELECT COUNT( address ) + FROM ip + INNER JOIN netblock AS nb2 + ON nb2.id = ip.netblockId + WHERE nb2.regionId IS NULL + AND nb2.poolId = pool.id + AND nb2.busyout IS TRUE + AND ip.nasId IS NOT NULL + ) AS ipAssignFree, + ( + SELECT COUNT( address ) + FROM ip + INNER JOIN netblock AS nb2 + ON nb2.id = ip.netblockId + WHERE nb2.regionId IS NULL + AND nb2.poolId = pool.id + AND nb2.busyout IS FALSE + AND ip.nasId IS NULL + ) AS ipAvailable, + ( + SELECT COUNT( address ) + FROM ip + INNER JOIN netblock AS nb2 + ON nb2.id = ip.netblockId + WHERE nb2.regionId IS NULL + AND nb2.poolId = pool.id + AND nb2.busyout IS TRUE + ) AS ipBusyout, + ( + SELECT COUNT( address ) + FROM ip + INNER JOIN netblock AS nb2 + ON nb2.id = ip.netblockId + WHERE nb2.regionId IS NULL + AND nb2.poolId = pool.id + ) AS ipTotal, + ( + 'Overflow addresses for ' + || upper(pool.name) + || ' pool' + ) AS description + FROM netblock + INNER JOIN pool + ON pool.id = netblock.poolId + WHERE netblock.regionId IS NULL; + UNION ALL + SELECT DISTINCT pool.name AS poolName, + 'ALL' AS addressType, + ( + SELECT COUNT( address ) + FROM ip + INNER JOIN netblock AS nb2 + ON nb2.id = ip.netblockId + WHERE nb2.poolId IS NULL + AND nb2.busyout IS FALSE + AND ip.nasId IS NOT NULL + ) AS ipAssignFree, + ( + SELECT COUNT( address ) + FROM ip + INNER JOIN netblock AS nb2 + ON nb2.id = ip.netblockId + WHERE nb2.poolId = pool.id + AND nb2.busyout IS TRUE + AND ip.nasId IS NOT NULL + ) AS ipAssignFree, + ( + SELECT COUNT( address ) + FROM ip + INNER JOIN netblock AS nb2 + ON nb2.id = ip.netblockId + WHERE nb2.poolId = pool.id + AND nb2.busyout IS FALSE + AND ip.nasId IS NULL + ) AS ipAvailable, + ( + SELECT COUNT( address ) + FROM ip + INNER JOIN netblock AS nb2 + ON nb2.id = ip.netblockId + WHERE nb2.poolId = pool.id + AND nb2.busyout IS TRUE + ) AS ipBusyout, + ( + SELECT COUNT( address ) + FROM ip + INNER JOIN netblock AS nb2 + ON nb2.id = ip.netblockId + WHERE nb2.poolId = pool.id + ) AS ipTotal, + ( + 'All addresses for ' + || upper(pool.name) + || ' pool' + ) AS description + FROM netblock + INNER JOIN pool + ON pool.id = netblock.poolId + WHERE netblock.regionId IS NULL; + +END; +$$; + + /* * audits netblocks * diff --git a/notes/projects/radius/sql/untitled.sql b/notes/projects/radius/sql/untitled.sql index beacd77..d9d010f 100644 --- a/notes/projects/radius/sql/untitled.sql +++ b/notes/projects/radius/sql/untitled.sql @@ -1,5 +1,6 @@ +SELECT * FROM pool_audit() ORDER BY poolname; SELECT * FROM region_audit() ORDER BY poolName, regionName; SELECT * FROM netblock_audit() ORDER BY poolName, network; SELECT * FROM ip_audit() ORDER BY ipAddress; -- cgit v1.2.3