aboutsummaryrefslogtreecommitdiffstats
path: root/notes/projects/radius/sql/ippool-functions.sql
diff options
context:
space:
mode:
Diffstat (limited to 'notes/projects/radius/sql/ippool-functions.sql')
-rw-r--r--notes/projects/radius/sql/ippool-functions.sql173
1 files 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
228 poolName VARCHAR(32), 228 poolName VARCHAR(32),
229 regionName VARCHAR(32), 229 regionName VARCHAR(32),
230 ipAddress INET, 230 ipAddress INET,
231 enabled BOOLEAN, 231 assignable BOOLEAN,
232 assigned BOOLEAN, 232 assigned BOOLEAN,
233 modified TIMESTAMP WITH TIME ZONE, 233 modified TIMESTAMP WITH TIME ZONE,
234 nasName VARCHAR(32), 234 nasName VARCHAR(32),
@@ -637,9 +637,10 @@ CREATE OR REPLACE FUNCTION netblock_audit
637 regionName VARCHAR(32), 637 regionName VARCHAR(32),
638 network CIDR, 638 network CIDR,
639 assignable BOOLEAN, 639 assignable BOOLEAN,
640 addressesAssigned BIGINT, 640 ipAssigned BIGINT,
641 addressesAvailable BIGINT, 641 ipUnassigned BIGINT,
642 addressesTotal BIGINT, 642 ipAvailable BIGINT,
643 ipTotal BIGINT,
643 description VARCHAR(256) 644 description VARCHAR(256)
644 ) 645 )
645 LANGUAGE plpgsql 646 LANGUAGE plpgsql
@@ -658,18 +659,27 @@ BEGIN
658 FROM ip 659 FROM ip
659 WHERE address <<= netblock.network 660 WHERE address <<= netblock.network
660 AND ip.nasId IS NOT NULL 661 AND ip.nasId IS NOT NULL
661 ) AS addressAssigned, 662 ) AS ipAssigned,
662 ( 663 (
663 SELECT count(address) 664 SELECT count(address)
664 FROM ip 665 FROM ip
665 WHERE address <<= netblock.network 666 WHERE address <<= netblock.network
666 AND ip.nasId IS NULL 667 AND ip.nasId IS NULL
667 ) AS addressesAvailable, 668 ) AS ipUnassigned,
669 (
670 SELECT DISTINCT count(address)
671 FROM ip
672 INNER JOIN netblock AS nb2
673 ON nb2.id = ip.netblockId
674 WHERE nb2.assignable IS TRUE
675 AND ip.address <<= netblock.network
676 AND ip.nasId IS NULL
677 ) AS ipAvailable,
668 ( 678 (
669 SELECT count(address) 679 SELECT count(address)
670 FROM ip 680 FROM ip
671 WHERE address <<= netblock.network 681 WHERE address <<= netblock.network
672 ) AS addressesTotal, 682 ) AS ipTotal,
673 netblock.description AS description 683 netblock.description AS description
674 FROM netblock 684 FROM netblock
675 INNER JOIN pool 685 INNER JOIN pool
@@ -680,4 +690,153 @@ BEGIN
680END; 690END;
681$$; 691$$;
682 692
693
694/*
695 * audits netblocks
696 *
697 * % SELECT * FROM netblock_audit();
698 *
699 * Returns table of netblocks
700 */
701CREATE OR REPLACE FUNCTION region_audit
702(
703)
704 RETURNS TABLE
705 (
706 poolName VARCHAR(32),
707 regionName VARCHAR(32),
708 ipAssigned BIGINT,
709 ipUnassigned BIGINT,
710 ipAvailable BIGINT,
711 ipUnassignable BIGINT,
712 ipTotal BIGINT,
713 description TEXT
714 )
715 LANGUAGE plpgsql
716 SECURITY DEFINER
717 AS $$
718DECLARE
719BEGIN
720
721 RETURN QUERY
722 SELECT DISTINCT pool.name AS poolName,
723 region.name AS regionname,
724 (
725 SELECT COUNT( address )
726 FROM ip
727 INNER JOIN netblock AS nb2
728 ON nb2.id = ip.netblockId
729 WHERE nb2.regionId = region.id
730 AND nb2.poolId = pool.id
731 AND ip.nasId IS NOT NULL
732 ) AS ipAssigned,
733 (
734 SELECT COUNT( address )
735 FROM ip
736 INNER JOIN netblock AS nb2
737 ON nb2.id = ip.netblockId
738 WHERE nb2.regionId = region.id
739 AND nb2.poolId = pool.id
740 AND ip.nasId IS NULL
741 ) AS ipUnassigned,
742 (
743 SELECT COUNT( address )
744 FROM ip
745 INNER JOIN netblock AS nb2
746 ON nb2.id = ip.netblockId
747 WHERE nb2.regionId = region.id
748 AND nb2.poolId = pool.id
749 AND nb2.assignable IS TRUE
750 AND ip.nasId IS NULL
751 ) AS ipAvailable,
752 (
753 SELECT COUNT( address )
754 FROM ip
755 INNER JOIN netblock AS nb2
756 ON nb2.id = ip.netblockId
757 WHERE nb2.regionId = region.id
758 AND nb2.poolId = pool.id
759 AND nb2.assignable IS FALSE
760 ) AS ipUnassignable,
761 (
762 SELECT COUNT( address )
763 FROM ip
764 INNER JOIN netblock AS nb2
765 ON nb2.id = ip.netblockId
766 WHERE nb2.regionId = region.id
767 AND nb2.poolId = pool.id
768 ) AS ipTotal,
769 (
770 'Regional addresses in '
771 || upper(region.Name)
772 || ' for '
773 || upper(pool.name)
774 || ' pool'
775 ) AS description
776 FROM netblock
777 INNER JOIN region
778 ON region.id = netblock.regionId
779 INNER JOIN pool
780 ON pool.id = netblock.poolId
781 UNION ALL
782 SELECT DISTINCT pool.name AS poolName,
783 NULL AS regionname,
784 (
785 SELECT COUNT( address )
786 FROM ip
787 INNER JOIN netblock AS nb2
788 ON nb2.id = ip.netblockId
789 WHERE nb2.regionId IS NULL
790 AND nb2.poolId = pool.id
791 AND ip.nasId IS NOT NULL
792 ) AS ipAssigned,
793 (
794 SELECT COUNT( address )
795 FROM ip
796 INNER JOIN netblock AS nb2
797 ON nb2.id = ip.netblockId
798 WHERE nb2.regionId IS NULL
799 AND nb2.poolId = pool.id
800 AND ip.nasId IS NULL
801 ) AS ipUnassigned,
802 (
803 SELECT COUNT( address )
804 FROM ip
805 INNER JOIN netblock AS nb2
806 ON nb2.id = ip.netblockId
807 WHERE nb2.regionId IS NULL
808 AND nb2.poolId = pool.id
809 AND nb2.assignable IS TRUE
810 AND ip.nasId IS NULL
811 ) AS ipAvailable,
812 (
813 SELECT COUNT( address )
814 FROM ip
815 INNER JOIN netblock AS nb2
816 ON nb2.id = ip.netblockId
817 WHERE nb2.regionId IS NULL
818 AND nb2.poolId = pool.id
819 AND nb2.assignable IS FALSE
820 ) AS ipUnassignable,
821 (
822 SELECT COUNT( address )
823 FROM ip
824 INNER JOIN netblock AS nb2
825 ON nb2.id = ip.netblockId
826 WHERE nb2.regionId IS NULL
827 AND nb2.poolId = pool.id
828 ) AS ipTotal,
829 (
830 'Overflow addresses for '
831 || upper(pool.name)
832 || ' pool'
833 ) AS description
834 FROM netblock
835 INNER JOIN pool
836 ON pool.id = netblock.poolId
837 WHERE netblock.regionId IS NULL;
838
839END;
840$$;
841
683/* end of sql */ 842/* end of sql */