aboutsummaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authorDavid M. Syzdek <syzdek@bindlebinaries.com>2019-05-09 13:21:22 -0800
committerDavid M. Syzdek <syzdek@bindlebinaries.com>2019-05-09 13:21:22 -0800
commitd6888eb15f88261a88950330d804cf0052df2609 (patch)
treeca680419968aa2f021f29033cdda6cf6dd1473e4
parentd0d9a43d752a1c098762fe45d04485a862731a7b (diff)
downloadscratchpad-d6888eb15f88261a88950330d804cf0052df2609.tar.gz
scratchpad-d6888eb15f88261a88950330d804cf0052df2609.tar.bz2
scratchpad-d6888eb15f88261a88950330d804cf0052df2609.tar.xz
scratchpad-d6888eb15f88261a88950330d804cf0052df2609.zip
Updating demo data and example queries
Diffstat
-rw-r--r--notes/projects/radius/sql/ippool-demo.sql112
-rw-r--r--notes/projects/radius/sql/ippool-queries.sql80
2 files changed, 130 insertions, 62 deletions
diff --git a/notes/projects/radius/sql/ippool-demo.sql b/notes/projects/radius/sql/ippool-demo.sql
index 1ad6f22..41fb90c 100644
--- a/notes/projects/radius/sql/ippool-demo.sql
+++ b/notes/projects/radius/sql/ippool-demo.sql
@@ -11,85 +11,99 @@
11SET ROLE radius_ippool; 11SET ROLE radius_ippool;
12 12
13 13
14-- create example pools
14SELECT pool_add( poolName := 'DHCP' ); 15SELECT pool_add( poolName := 'DHCP' );
15SELECT pool_add( poolName := 'PPPoE' ); 16SELECT pool_add( poolName := 'PPPoE' );
16SELECT pool_add( poolName := 'reserved' ); 17SELECT pool_add( poolName := 'reserved' );
17 18
18 19
20-- create example regions
19SELECT region_add( regionName := 'Anchorage' ); 21SELECT region_add( regionName := 'Anchorage' );
20SELECT region_add( regionName := 'Soldotna' ); 22SELECT region_add( regionName := 'Soldotna' );
21SELECT region_add( regionName := 'Fairbanks' ); 23SELECT region_add( regionName := 'Fairbanks' );
22SELECT region_add( regionName := 'No where' ); 24SELECT region_add( regionName := 'No where' );
23 25
24 26
27-- create example NAS clients
25SELECT nas_add( nasName := 'br1.cwc', regionName := 'Anchorage' ); 28SELECT nas_add( nasName := 'br1.cwc', regionName := 'Anchorage' );
26SELECT nas_add( nasName := 'bras.eng', regionName := 'Anchorage' ); 29SELECT nas_add( nasName := 'bras.eng', regionName := 'Anchorage' );
27SELECT nas_add( nasName := 'br1.sol', regionName := 'Soldotna' ); 30SELECT nas_add( nasName := 'br1.sol', regionName := 'Soldotna' );
28 31
29 32
33-- Create example netblocks
30SELECT netblock_add 34SELECT netblock_add
31 ( 35 ( netAddress := '209.193.4.0/28',
32 netAddress := '209.193.4.0/28', 36 poolName := 'DHCP',
33 poolName := 'DHCP', 37 regionName := 'Soldotna',
34 regionName := 'Soldotna', 38 netActive := TRUE,
35 netActive := FALSE, 39 addIpEntries := TRUE
36 netDesc := 'Admin Network',
37 addIpEntries := TRUE
38 ); 40 );
39SELECT netblock_add 41SELECT netblock_add
40 ( 42 ( netAddress := '209.193.4.0/29',
41 netAddress := '209.193.4.0/29', 43 poolName := 'DHCP',
42 poolName := 'DHCP', 44 regionName := 'Soldotna',
43 regionName := 'Soldotna', 45 netDesc := 'Reserved for engineering test accounts',
44 netActive := FALSE, 46 netActive := FALSE,
45 netDesc := 'Admin Network', 47 addIpEntries := TRUE
46 addIpEntries := TRUE
47 ); 48 );
48SELECT netblock_add 49SELECT netblock_add
49 ( 50 ( netAddress := '209.193.4.16/28',
50 netAddress := '209.193.4.16/28', 51 poolName := 'DHCP',
51 poolName := 'DHCP', 52 netActive := TRUE,
52 netActive := FALSE, 53 addIpEntries := TRUE
53 netDesc := 'Admin Network',
54 addIpEntries := TRUE
55 ); 54 );
56SELECT netblock_add 55SELECT netblock_add
57 ( 56 ( netAddress := '209.193.4.32/28',
58 netAddress := '209.193.4.32/28', 57 poolName := 'DHCP',
59 poolName := 'DHCP', 58 regionName := 'Anchorage',
60 regionName := 'Anchorage', 59 netActive := FALSE,
61 netActive := FALSE, 60 addIpEntries := TRUE
62 netDesc := 'Admin Network',
63 addIpEntries := TRUE
64 ); 61 );
65SELECT netblock_add 62SELECT netblock_add
66 ( 63 ( netAddress := '209.193.4.48/28',
67 netAddress := '209.193.4.48/28', 64 poolName := 'DHCP',
68 poolName := 'DHCP', 65 regionName := 'Fairbanks',
69 regionName := 'Fairbanks', 66 netActive := FALSE,
70 netActive := FALSE, 67 addIpEntries := TRUE
71 netDesc := 'Admin Network',
72 addIpEntries := TRUE
73 ); 68 );
74 69
75 70
76UPDATE ip 71-- create example explicit IP reservations
77 SET nasid = ( SELECT id FROM nas WHERE name LIKE 'br1.sol' ), 72SELECT ip_assign
78 username = 'jdoe0@acsalaska.net', 73 ( assignIp := '209.193.4.8/32',
79 acctSession = 'aaqaqdasdqasdasqaasd' 74 assignNas := 'br1.sol',
80 WHERE address = '209.193.4.8/32'; 75 assignUser := 'jdoe8@acsalaska.net',
81UPDATE ip 76 assignSess := 'aqaqqaasdqweaasdqa8'
82 SET nasid = ( SELECT id FROM nas WHERE name LIKE 'br1.sol' ), 77 );
83 username = 'jdoe1@acsalaska.net', 78SELECT ip_assign
84 acctSession = 'qqasasdqweasdasdqqas' 79 ( assignIp := '209.193.4.9/32',
85 WHERE address = '209.193.4.9/32'; 80 assignNas := 'br1.sol',
86UPDATE ip 81 assignUser := 'jdoe9@acsalaska.net',
87 SET nasid = ( SELECT id FROM nas WHERE name LIKE 'br1.sol' ), 82 assignSess := 'qweadszcxqweasdzcx9'
88 username = 'jdoe1@acsalaska.net', 83 );
89 acctSession = 'qqasasdqweasdasdqqas' 84SELECT ip_assign
90 WHERE address = '209.193.4.9/32'; 85 ( assignIp := '209.193.4.10/32',
86 assignNas := 'br1.sol',
87 assignUser := 'jdoe10@acsalaska.net'
88 );
89SELECT ip_assign
90 ( assignIp := '209.193.4.11/32',
91 assignNas := 'br1.sol',
92 assignUser := 'jdoe11@acsalaska.net'
93 );
94SELECT ip_assign
95 ( assignIp := '209.193.4.12/32',
96 assignNas := 'br1.sol',
97 assignUser := 'jdoe12@acsalaska.net'
98 );
99SELECT ip_assign
100 ( assignIp := '209.193.4.13/32',
101 assignNas := 'br1.sol',
102 assignUser := 'jdoe13@acsalaska.net'
103 );
91 104
92 105
106-- add missing IP addresses to netblock
93SELECT ip_add_network( ipNetwork := '209.112.192.0/19' ); 107SELECT ip_add_network( ipNetwork := '209.112.192.0/19' );
94SELECT ip_add_network( ipNetwork := '209.112.128.0/18' ); 108SELECT ip_add_network( ipNetwork := '209.112.128.0/18' );
95 109
diff --git a/notes/projects/radius/sql/ippool-queries.sql b/notes/projects/radius/sql/ippool-queries.sql
index 78ff04f..4a0ed4a 100644
--- a/notes/projects/radius/sql/ippool-queries.sql
+++ b/notes/projects/radius/sql/ippool-queries.sql
@@ -23,28 +23,82 @@ SET ROLE radius_ippool;
23 * administrator will need to customize the query to obtain the needed 23 * administrator will need to customize the query to obtain the needed
24 * result set for a given need. 24 * result set for a given need.
25 */ 25 */
26SELECT pool.name AS poolName, 26SELECT DISTINCT pool.name AS poolName,
27 region.name AS regionName, 27 region.name AS regionName,
28 ip.address AS address, 28 ip.address AS address,
29 TRUE AS active, 29 'unknown' AS active,
30 ( 30 (
31 nasId IS NOT NULL 31 nasId IS NOT NULL
32 OR 32 OR
33 acctSession IS NOT NULL 33 acctSession IS NOT NULL
34 ) AS assigned, 34 ) AS assigned,
35 ip.modified AS modified, 35 ip.modified AS modified,
36 nas.name AS nasName, 36 nas.name AS nasName,
37 ip.username AS username, 37 ip.username AS username,
38 ip.acctSession AS acctSession 38 ip.acctSession AS acctSession
39 FROM ip 39 FROM ip
40 INNER JOIN netblock 40 INNER JOIN netblock
41 ON ip.address <<= netblock.network 41 ON ip.address <<= netblock.network
42 INNER JOIN pool 42 INNER JOIN pool
43 ON netblock.poolId = pool.id 43 ON netblock.poolId = pool.id
44 INNER JOIN region 44 LEFT OUTER JOIN region
45 ON netblock.regionId = region.id 45 ON netblock.regionId = region.id
46 LEFT OUTER JOIN nas 46 LEFT OUTER JOIN nas
47 ON ip.nasId = nas.id; 47 ON ip.nasId = nas.id
48 ORDER BY address;
49
50
51/*
52 * list all inactive IP addresses
53 */
54SELECT ip.address AS ipAddress,
55 netblock.network AS network,
56 netblock.active AS active
57 FROM ip
58 INNER JOIN netblock
59 ON ip.address <<= netblock.network
60 WHERE netblock.active = FALSE
61 ORDER BY address;
62
63
64/*
65 * list all actie IP addresses
66 */
67SELECT ip.address AS ipAddress,
68 netblock.network AS network,
69 netblock.active AS active
70 FROM ip
71 INNER JOIN netblock
72 ON ip.address <<= netblock.network
73 WHERE netblock.active = TRUE
74 AND ip.address NOT IN
75 (
76 SELECT address AS ipAddress
77 FROM ip
78 INNER JOIN netblock
79 ON ip.address <<= netblock.network
80 AND netblock.active = FALSE
81 )
82 ORDER BY ipAddress;
83
84
85
86SELECT DISTINCT netblock.regionId AS regionId,
87 ip.address AS address,
88 'unknown' AS active,
89 (
90 nasId IS NOT NULL
91 OR
92 acctSession IS NOT NULL
93 ) AS assigned,
94 ip.modified AS modified,
95 ip.nasid AS nasId,
96 ip.username AS username,
97 ip.acctSession AS acctSession
98 FROM ip
99 INNER JOIN netblock
100 ON ip.address <<= netblock.network
101 ORDER BY address;
48 102
49 103
50/* end of sql */ 104/* end of sql */