aboutsummaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authorDavid M. Syzdek <syzdek@bindlebinaries.com>2019-05-24 12:43:10 -0800
committerDavid M. Syzdek <syzdek@bindlebinaries.com>2019-05-24 12:43:10 -0800
commitbb6abb698da039bd6609b730fc1052444eccbf42 (patch)
tree8192bdf46e03d4ab50e91abe0e12d6a963aac627
parentcf9cdd6b146c9cac2d3693b5a3c14cd2d4d87298 (diff)
downloadscratchpad-bb6abb698da039bd6609b730fc1052444eccbf42.tar.gz
scratchpad-bb6abb698da039bd6609b730fc1052444eccbf42.tar.bz2
scratchpad-bb6abb698da039bd6609b730fc1052444eccbf42.tar.xz
scratchpad-bb6abb698da039bd6609b730fc1052444eccbf42.zip
Updating audit functions
Diffstat
-rw-r--r--notes/projects/radius/sql/ippool-demo.sql62
-rw-r--r--notes/projects/radius/sql/ippool-functions.sql203
-rw-r--r--notes/projects/radius/sql/untitled.sql1
3 files changed, 266 insertions, 0 deletions
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
155 ); 155 );
156 156
157 157
158-- load large blocks
159SELECT netblock_add
160 ( netAddress := '209.193.0.0/22',
161 poolName := 'DHCP',
162 regionName := 'Anchorage'
163 );
164SELECT netblock_add
165 ( netAddress := '209.193.5.0/24',
166 poolName := 'DHCP',
167 regionName := 'Anchorage'
168 );
169SELECT netblock_add
170 ( netAddress := '209.193.6.0/23',
171 poolName := 'DHCP',
172 regionName := 'Anchorage'
173 );
174SELECT netblock_add
175 ( netAddress := '209.193.8.0/21',
176 poolName := 'DHCP',
177 regionName := 'Anchorage'
178 );
179SELECT netblock_add
180 ( netAddress := '209.193.16.0/20',
181 poolName := 'DHCP',
182 regionName := 'Anchorage'
183 );
184SELECT netblock_add
185 ( netAddress := '209.193.32.0/19',
186 poolName := 'DHCP',
187 regionName := 'Anchorage'
188 );
189SELECT netblock_add
190 ( netAddress := '209.112.128.0/18',
191 poolName := 'DHCP',
192 regionName := 'Soldotna'
193 );
194SELECT netblock_add
195 ( netAddress := '209.112.192.0/19',
196 poolName := 'DHCP',
197 regionName := 'Soldotna'
198 );
199SELECT netblock_add
200 ( netAddress := '66.230.80.0/20',
201 poolName := 'DHCP',
202 regionName := 'Fairbanks'
203 );
204SELECT netblock_add
205 ( netAddress := '66.230.96.0/19',
206 poolName := 'DHCP',
207 regionName := 'Anchorage'
208 );
209SELECT netblock_add
210 ( netAddress := '69.161.0.0/19',
211 poolName := 'DHCP',
212 regionName := 'Juneau'
213 );
214SELECT netblock_add
215 ( netAddress := '63.140.64.0/18',
216 poolName := 'DHCP'
217 );
218
219
158-- add missing IP addresses to netblock 220-- add missing IP addresses to netblock
159-- SELECT ip_add_network( ipNetwork := '209.112.192.0/19' ); 221-- SELECT ip_add_network( ipNetwork := '209.112.192.0/19' );
160-- SELECT ip_add_network( ipNetwork := '209.112.128.0/18' ); 222-- 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
@@ -703,6 +703,209 @@ $$;
703 * 703 *
704 * Returns table of netblocks 704 * Returns table of netblocks
705 */ 705 */
706CREATE OR REPLACE FUNCTION pool_audit
707(
708)
709 RETURNS TABLE
710 (
711 poolName VARCHAR(32),
712 addressType TEXT,
713 ipAssignedFree BIGINT,
714 ipAssignedBusy BIGINT,
715 ipAvailable BIGINT,
716 ipBusyout BIGINT,
717 ipTotal BIGINT,
718 description TEXT
719 )
720 LANGUAGE plpgsql
721 SECURITY DEFINER
722 AS $$
723DECLARE
724BEGIN
725
726 RETURN QUERY
727 SELECT DISTINCT pool.name AS poolName,
728 'regional' AS addressType,
729 (
730 SELECT COUNT( address )
731 FROM ip
732 INNER JOIN netblock AS nb2
733 ON nb2.id = ip.netblockId
734 WHERE nb2.regionId IS NOT NULL
735 AND nb2.poolId = pool.id
736 AND nb2.busyout IS FALSE
737 AND ip.nasId IS NOT NULL
738 ) AS ipAssignedFree,
739 (
740 SELECT COUNT( address )
741 FROM ip
742 INNER JOIN netblock AS nb2
743 ON nb2.id = ip.netblockId
744 WHERE nb2.regionId IS NOT NULL
745 AND nb2.poolId = pool.id
746 AND nb2.busyout IS TRUE
747 AND ip.nasId IS NOT NULL
748 ) AS ipAssignedBusy,
749 (
750 SELECT COUNT( address )
751 FROM ip
752 INNER JOIN netblock AS nb2
753 ON nb2.id = ip.netblockId
754 WHERE nb2.regionId IS NOT NULL
755 AND nb2.poolId = pool.id
756 AND nb2.busyout IS FALSE
757 AND ip.nasId IS NULL
758 ) AS ipAvailable,
759 (
760 SELECT COUNT( address )
761 FROM ip
762 INNER JOIN netblock AS nb2
763 ON nb2.id = ip.netblockId
764 WHERE nb2.regionId IS NOT NULL
765 AND nb2.poolId = pool.id
766 AND nb2.busyout IS TRUE
767 ) AS ipBusyout,
768 (
769 SELECT COUNT( address )
770 FROM ip
771 INNER JOIN netblock AS nb2
772 ON nb2.id = ip.netblockId
773 WHERE nb2.regionId IS NOT NULL
774 AND nb2.poolId = pool.id
775 ) AS ipTotal,
776 (
777 'Regional addresses for '
778 || upper(pool.name)
779 || ' pool'
780 ) AS description
781 FROM netblock
782 INNER JOIN pool
783 ON pool.id = netblock.poolId
784 UNION ALL
785 SELECT DISTINCT pool.name AS poolName,
786 'non-regional' AS addressType,
787 (
788 SELECT COUNT( address )
789 FROM ip
790 INNER JOIN netblock AS nb2
791 ON nb2.id = ip.netblockId
792 WHERE nb2.regionId IS NULL
793 AND nb2.poolId IS NULL
794 AND nb2.busyout IS FALSE
795 AND ip.nasId IS NOT NULL
796 ) AS ipAssignFree,
797 (
798 SELECT COUNT( address )
799 FROM ip
800 INNER JOIN netblock AS nb2
801 ON nb2.id = ip.netblockId
802 WHERE nb2.regionId IS NULL
803 AND nb2.poolId = pool.id
804 AND nb2.busyout IS TRUE
805 AND ip.nasId IS NOT NULL
806 ) AS ipAssignFree,
807 (
808 SELECT COUNT( address )
809 FROM ip
810 INNER JOIN netblock AS nb2
811 ON nb2.id = ip.netblockId
812 WHERE nb2.regionId IS NULL
813 AND nb2.poolId = pool.id
814 AND nb2.busyout IS FALSE
815 AND ip.nasId IS NULL
816 ) AS ipAvailable,
817 (
818 SELECT COUNT( address )
819 FROM ip
820 INNER JOIN netblock AS nb2
821 ON nb2.id = ip.netblockId
822 WHERE nb2.regionId IS NULL
823 AND nb2.poolId = pool.id
824 AND nb2.busyout IS TRUE
825 ) AS ipBusyout,
826 (
827 SELECT COUNT( address )
828 FROM ip
829 INNER JOIN netblock AS nb2
830 ON nb2.id = ip.netblockId
831 WHERE nb2.regionId IS NULL
832 AND nb2.poolId = pool.id
833 ) AS ipTotal,
834 (
835 'Overflow addresses for '
836 || upper(pool.name)
837 || ' pool'
838 ) AS description
839 FROM netblock
840 INNER JOIN pool
841 ON pool.id = netblock.poolId
842 WHERE netblock.regionId IS NULL;
843 UNION ALL
844 SELECT DISTINCT pool.name AS poolName,
845 'ALL' AS addressType,
846 (
847 SELECT COUNT( address )
848 FROM ip
849 INNER JOIN netblock AS nb2
850 ON nb2.id = ip.netblockId
851 WHERE nb2.poolId IS NULL
852 AND nb2.busyout IS FALSE
853 AND ip.nasId IS NOT NULL
854 ) AS ipAssignFree,
855 (
856 SELECT COUNT( address )
857 FROM ip
858 INNER JOIN netblock AS nb2
859 ON nb2.id = ip.netblockId
860 WHERE nb2.poolId = pool.id
861 AND nb2.busyout IS TRUE
862 AND ip.nasId IS NOT NULL
863 ) AS ipAssignFree,
864 (
865 SELECT COUNT( address )
866 FROM ip
867 INNER JOIN netblock AS nb2
868 ON nb2.id = ip.netblockId
869 WHERE nb2.poolId = pool.id
870 AND nb2.busyout IS FALSE
871 AND ip.nasId IS NULL
872 ) AS ipAvailable,
873 (
874 SELECT COUNT( address )
875 FROM ip
876 INNER JOIN netblock AS nb2
877 ON nb2.id = ip.netblockId
878 WHERE nb2.poolId = pool.id
879 AND nb2.busyout IS TRUE
880 ) AS ipBusyout,
881 (
882 SELECT COUNT( address )
883 FROM ip
884 INNER JOIN netblock AS nb2
885 ON nb2.id = ip.netblockId
886 WHERE nb2.poolId = pool.id
887 ) AS ipTotal,
888 (
889 'All addresses for '
890 || upper(pool.name)
891 || ' pool'
892 ) AS description
893 FROM netblock
894 INNER JOIN pool
895 ON pool.id = netblock.poolId
896 WHERE netblock.regionId IS NULL;
897
898END;
899$$;
900
901
902/*
903 * audits netblocks
904 *
905 * % SELECT * FROM netblock_audit();
906 *
907 * Returns table of netblocks
908 */
706CREATE OR REPLACE FUNCTION region_audit 909CREATE OR REPLACE FUNCTION region_audit
707( 910(
708) 911)
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 @@
1 1
2 2
3SELECT * FROM pool_audit() ORDER BY poolname;
3SELECT * FROM region_audit() ORDER BY poolName, regionName; 4SELECT * FROM region_audit() ORDER BY poolName, regionName;
4SELECT * FROM netblock_audit() ORDER BY poolName, network; 5SELECT * FROM netblock_audit() ORDER BY poolName, network;
5SELECT * FROM ip_audit() ORDER BY ipAddress; 6SELECT * FROM ip_audit() ORDER BY ipAddress;