diff options
Diffstat (limited to 'notes/projects/radius/sql/ippool-functions.sql')
| -rw-r--r-- | notes/projects/radius/sql/ippool-functions.sql | 203 |
1 files changed, 203 insertions, 0 deletions
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 | */ |
| 706 | CREATE 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 $$ | ||
| 723 | DECLARE | ||
| 724 | BEGIN | ||
| 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 | |||
| 898 | END; | ||
| 899 | $$; | ||
| 900 | |||
| 901 | |||
| 902 | /* | ||
| 903 | * audits netblocks | ||
| 904 | * | ||
| 905 | * % SELECT * FROM netblock_audit(); | ||
| 906 | * | ||
| 907 | * Returns table of netblocks | ||
| 908 | */ | ||
| 706 | CREATE OR REPLACE FUNCTION region_audit | 909 | CREATE OR REPLACE FUNCTION region_audit |
| 707 | ( | 910 | ( |
| 708 | ) | 911 | ) |