From ce2cda9fe4fd2d45aca581db20d468d4ffdebc64 Mon Sep 17 00:00:00 2001 From: "David M. Syzdek" Date: Thu, 23 May 2019 21:13:14 -0800 Subject: Updating audting functions --- notes/projects/radius/sql/ippool-functions.sql | 173 ++++++++++++++++++++++++- 1 file changed, 166 insertions(+), 7 deletions(-) diff --git a/notes/projects/radius/sql/ippool-functions.sql b/notes/projects/radius/sql/ippool-functions.sql index 616f5b2..dc91396 100644 --- a/notes/projects/radius/sql/ippool-functions.sql +++ b/notes/projects/radius/sql/ippool-functions.sql @@ -228,7 +228,7 @@ CREATE OR REPLACE FUNCTION ip_audit poolName VARCHAR(32), regionName VARCHAR(32), ipAddress INET, - enabled BOOLEAN, + assignable BOOLEAN, assigned BOOLEAN, modified TIMESTAMP WITH TIME ZONE, nasName VARCHAR(32), @@ -637,9 +637,10 @@ CREATE OR REPLACE FUNCTION netblock_audit regionName VARCHAR(32), network CIDR, assignable BOOLEAN, - addressesAssigned BIGINT, - addressesAvailable BIGINT, - addressesTotal BIGINT, + ipAssigned BIGINT, + ipUnassigned BIGINT, + ipAvailable BIGINT, + ipTotal BIGINT, description VARCHAR(256) ) LANGUAGE plpgsql @@ -658,18 +659,27 @@ BEGIN FROM ip WHERE address <<= netblock.network AND ip.nasId IS NOT NULL - ) AS addressAssigned, + ) AS ipAssigned, ( SELECT count(address) FROM ip WHERE address <<= netblock.network AND ip.nasId IS NULL - ) AS addressesAvailable, + ) AS ipUnassigned, + ( + SELECT DISTINCT count(address) + FROM ip + INNER JOIN netblock AS nb2 + ON nb2.id = ip.netblockId + WHERE nb2.assignable IS TRUE + AND ip.address <<= netblock.network + AND ip.nasId IS NULL + ) AS ipAvailable, ( SELECT count(address) FROM ip WHERE address <<= netblock.network - ) AS addressesTotal, + ) AS ipTotal, netblock.description AS description FROM netblock INNER JOIN pool @@ -680,4 +690,153 @@ BEGIN END; $$; + +/* + * audits netblocks + * + * % SELECT * FROM netblock_audit(); + * + * Returns table of netblocks + */ +CREATE OR REPLACE FUNCTION region_audit +( +) + RETURNS TABLE + ( + poolName VARCHAR(32), + regionName VARCHAR(32), + ipAssigned BIGINT, + ipUnassigned BIGINT, + ipAvailable BIGINT, + ipUnassignable BIGINT, + ipTotal BIGINT, + description TEXT + ) + LANGUAGE plpgsql + SECURITY DEFINER + AS $$ +DECLARE +BEGIN + + RETURN QUERY + SELECT DISTINCT pool.name AS poolName, + region.name AS regionname, + ( + SELECT COUNT( address ) + FROM ip + INNER JOIN netblock AS nb2 + ON nb2.id = ip.netblockId + WHERE nb2.regionId = region.id + AND nb2.poolId = pool.id + AND ip.nasId IS NOT NULL + ) AS ipAssigned, + ( + SELECT COUNT( address ) + FROM ip + INNER JOIN netblock AS nb2 + ON nb2.id = ip.netblockId + WHERE nb2.regionId = region.id + AND nb2.poolId = pool.id + AND ip.nasId IS NULL + ) AS ipUnassigned, + ( + SELECT COUNT( address ) + FROM ip + INNER JOIN netblock AS nb2 + ON nb2.id = ip.netblockId + WHERE nb2.regionId = region.id + AND nb2.poolId = pool.id + AND nb2.assignable IS TRUE + 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 = region.id + AND nb2.poolId = pool.id + AND nb2.assignable IS FALSE + ) AS ipUnassignable, + ( + SELECT COUNT( address ) + FROM ip + INNER JOIN netblock AS nb2 + ON nb2.id = ip.netblockId + WHERE nb2.regionId = region.id + AND nb2.poolId = pool.id + ) AS ipTotal, + ( + 'Regional addresses in ' + || upper(region.Name) + || ' for ' + || upper(pool.name) + || ' pool' + ) AS description + FROM netblock + INNER JOIN region + ON region.id = netblock.regionId + INNER JOIN pool + ON pool.id = netblock.poolId + UNION ALL + SELECT DISTINCT pool.name AS poolName, + NULL AS regionname, + ( + 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 ip.nasId IS NOT NULL + ) AS ipAssigned, + ( + 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 ip.nasId IS NULL + ) AS ipUnassigned, + ( + 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.assignable IS TRUE + 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.assignable IS FALSE + ) AS ipUnassignable, + ( + 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; + +END; +$$; + /* end of sql */ -- cgit v1.2.3