1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
|
/*
* IP Engineering RADIUS Servers
*
* ippool-demo.sql - inserts demo data into RADIUS IP Pools database
*
*/
SET ROLE radius_user;
/*
* list all IP addresses with referenced/inferred data
*
* This query will return a potentialy large dataset which makes it
* unsuitable for a function or stored procedure. The implementation of
* `RETURN QUERY` in PostgreSQL 11 stores the entire result set before
* returning from a function. If the dataset is very large, the result
* set will be written to disk to avoid memory exchaustion. This will lead
* to performance issues.
*
* Additionally, the following query is very generic and most likely an
* administrator will need to customize the query to obtain the needed
* result set for a given need.
*/
--
-- Method 1:
SELECT DISTINCT pool.name AS poolName,
region.name AS regionName,
ip.address AS address,
TRUE = ALL
(
SELECT nb2.active
FROM netblock AS nb2
WHERE ip.address <<= nb2.network
) AS active,
(
nasId IS NOT NULL
) AS assigned,
ip.modified AS modified,
nas.name AS nasName,
ip.username AS username,
ip.acctSession AS acctSession
FROM ip
INNER JOIN netblock
ON ip.address <<= netblock.network
INNER JOIN pool
ON netblock.poolId = pool.id
LEFT OUTER JOIN region
ON netblock.regionId = region.id
LEFT OUTER JOIN nas
ON ip.nasId = nas.id
ORDER BY address;
--
-- Method 2:
SELECT DISTINCT pool.name AS poolName,
region.name AS regionName,
ip.address AS address,
NOT EXISTS
(
SELECT *
FROM netblock AS nb2
WHERE ip.address <<= nb2.network
AND NOT nb2.active
) AS active,
(
nasId IS NOT NULL
) AS assigned,
ip.modified AS modified,
nas.name AS nasName,
ip.username AS username,
ip.acctSession AS acctSession
FROM ip
INNER JOIN netblock
ON ip.address <<= netblock.network
INNER JOIN pool
ON netblock.poolId = pool.id
LEFT OUTER JOIN region
ON netblock.regionId = region.id
LEFT OUTER JOIN nas
ON ip.nasId = nas.id
ORDER BY address;
/*
* list all inactive IP addresses
*/
SELECT DISTINCT ip.address AS ipAddress,
netblock.network AS network,
netblock.active AS active
FROM ip
INNER JOIN netblock
ON ip.address <<= netblock.network
WHERE netblock.active = FALSE
ORDER BY ipAddress;
/*
* list all active IP addresses
*/
SELECT DISTINCT netblock.regionId AS regionId,
ip.address AS ipAddress,
netblock.active AS active
FROM ip
INNER JOIN netblock
ON ip.address <<= netblock.network
WHERE netblock.active = TRUE
AND ip.address NOT IN
(
SELECT address AS ipAddress
FROM ip
INNER JOIN netblock
ON ip.address <<= netblock.network
AND netblock.active = FALSE
)
ORDER BY ipAddress;
/* end of sql */
|
