我正在尝试编写一个查询以根据重叠的净块表审核IP地址表。单个净块可以通过布尔值标记为活动性或无效。根据netblock表中的活动列，审核查询应返回布尔值为active。如果任何净块将active列设置为false，则NETBLOCK中包含的IP地址应报告为active设置为false。
以下是ip表的定义：
CREATE TABLE IF NOT EXISTS ip
(
address INET NOT NULL UNIQUE,
modified timestamp WITH TIME ZONE NOT NULL
DEFAULT TIMESTAMP 'epoch',
username VARCHAR(64),
acctSession VARCHAR(32),
nasId INTEGER,
PRIMARY KEY ( address ),
FOREIGN KEY ( nasId )
REFERENCES nas
( id )
);
以下是ip中的示例条目：
radius_ippool=> SELECT * FROM ip ORDER BY address;
address | modified | username | acctsession | nasid
--------------+-------------------------------+--------------------+-------------------------+-------
209.193.4.0 | 1970-01-01 00:00:00-10 | | |
209.193.4.1 | 1970-01-01 00:00:00-10 | | |
209.193.4.2 | 1970-01-01 00:00:00-10 | | |
209.193.4.3 | 1970-01-01 00:00:00-10 | | |
209.193.4.4 | 1970-01-01 00:00:00-10 | | |
209.193.4.5 | 1970-01-01 00:00:00-10 | | |
209.193.4.6 | 1970-01-01 00:00:00-10 | | |
209.193.4.7 | 1970-01-01 00:00:00-10 | | |
209.193.4.8 | 2019-05-09 11:55:20.456856-08 | jdoe8@example.net | aqaqqaasdqweaasdqa8 | 3
209.193.4.9 | 2019-05-09 11:55:20.638136-08 | jdoe9@aexample.net | qweadszcxqweasdzcx9 | 3
209.193.4.10 | 2019-05-09 11:55:20.85176-08 | jdoe10@example.net | user:jdoe10@example.net | 3
209.193.4.11 | 2019-05-09 11:55:20.872469-08 | jdoe11@example.net | user:jdoe11@example.net | 3
209.193.4.12 | 2019-05-09 11:55:20.894765-08 | jdoe12@example.net | user:jdoe12@example.net | 3
209.193.4.13 | 2019-05-09 11:55:21.02472-08 | jdoe13@example.net | user:jdoe13@example.net | 3
209.193.4.14 | 1970-01-01 00:00:00-10 | | |
209.193.4.15 | 1970-01-01 00:00:00-10 | | |
(16 rows)
以下是netblock表的定义：
CREATE TABLE IF NOT EXISTS netblock
(
id SERIAL,
network CIDR NOT NULL UNIQUE,
poolId INTEGER NOT NULL,
regionId INTEGER,
active BOOLEAN NOT NULL DEFAULT TRUE,
description VARCHAR(256),
PRIMARY KEY ( id ),
FOREIGN KEY ( poolId )
REFERENCES pool
( id ),
FOREIGN KEY ( regionId )
REFERENCES region
( id )
);
以下是netblock表中的示例条目：
radius_ippool=> SELECT * FROM netblock ORDER BY network;
id | network | poolid | regionid | active | description
----+----------------+--------+----------+--------+----------------------------------------
1 | 209.193.4.0/28 | 1 | 2 | t |
2 | 209.193.4.0/29 | 1 | 2 | f | Reserved for engineering test accounts
4 | 209.193.4.4/30 | 1 | 2 | t |
(3 rows)
我已经找出一个查询，可以在单独查询中列出所有"活动"one_answers"非活动" IP地址：
-- query to return "inactive" 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 = FALSE
ORDER BY ipAddress;
-- query to return "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;
上述查询的结果：
regionid | ipaddress | active
----------+-------------+--------
2 | 209.193.4.0 | f
2 | 209.193.4.1 | f
2 | 209.193.4.2 | f
2 | 209.193.4.3 | f
2 | 209.193.4.4 | f
2 | 209.193.4.5 | f
2 | 209.193.4.6 | f
2 | 209.193.4.7 | f
(8 rows)
regionid | ipaddress | active
----------+--------------+--------
2 | 209.193.4.8 | t
2 | 209.193.4.9 | t
2 | 209.193.4.10 | t
2 | 209.193.4.11 | t
2 | 209.193.4.12 | t
2 | 209.193.4.13 | t
2 | 209.193.4.14 | t
2 | 209.193.4.15 | t
(8 rows)
如何通过排队intactvie/active Net Blocks的NetBlock表来替换active列的unknown的静态值？
SELECT DISTINCT netblock.regionId AS regionId,
ip.address AS address,
'unknown' AS active,
(
nasId IS NOT NULL
OR
acctSession IS NOT NULL
) AS assigned,
ip.modified AS modified,
ip.nasid AS nasId,
ip.username AS username,
ip.acctSession AS acctSession
FROM ip
INNER JOIN netblock
ON ip.address <<= netblock.network
ORDER BY address;
从上面查询的结果：
regionid | address | active | assigned | modified | nasid | username | acctsession
----------+--------------+---------+----------+-------------------------------+-------+--------------------+-------------------------
2 | 209.193.4.0 | unknown | f | 1970-01-01 00:00:00-10 | | |
2 | 209.193.4.1 | unknown | f | 1970-01-01 00:00:00-10 | | |
2 | 209.193.4.2 | unknown | f | 1970-01-01 00:00:00-10 | | |
2 | 209.193.4.3 | unknown | f | 1970-01-01 00:00:00-10 | | |
2 | 209.193.4.4 | unknown | f | 1970-01-01 00:00:00-10 | | |
2 | 209.193.4.5 | unknown | f | 1970-01-01 00:00:00-10 | | |
2 | 209.193.4.6 | unknown | f | 1970-01-01 00:00:00-10 | | |
2 | 209.193.4.7 | unknown | f | 1970-01-01 00:00:00-10 | | |
2 | 209.193.4.8 | unknown | t | 2019-05-09 11:55:20.456856-08 | 3 | jdoe8@example.net | aqaqqaasdqweaasdqa8
2 | 209.193.4.9 | unknown | t | 2019-05-09 11:55:20.638136-08 | 3 | jdoe9@aexample.net | qweadszcxqweasdzcx9
2 | 209.193.4.10 | unknown | t | 2019-05-09 11:55:20.85176-08 | 3 | jdoe10@example.net | user:jdoe10@example.net
2 | 209.193.4.11 | unknown | t | 2019-05-09 11:55:20.872469-08 | 3 | jdoe11@example.net | user:jdoe11@example.net
2 | 209.193.4.12 | unknown | t | 2019-05-09 11:55:20.894765-08 | 3 | jdoe12@example.net | user:jdoe12@example.net
2 | 209.193.4.13 | unknown | t | 2019-05-09 11:55:21.02472-08 | 3 | jdoe13@example.net | user:jdoe13@example.net
2 | 209.193.4.14 | unknown | f | 1970-01-01 00:00:00-10 | | |
2 | 209.193.4.15 | unknown | f | 1970-01-01 00:00:00-10 | | |
(16 rows)
您可以使用ALL和子查询检查IP是否处于活动状态的所有净块。
SELECT ...
true = ALL (SELECT nb2.active
FROM netblock nb2
WHERE ip.address <<= nb2.network) active,
...
另外，您可以使用NOT EXISTS：
SELECT ...
NOT EXISTS (SELECT *
FROM netblock nb2
WHERE ip.address <<= nb2.network
AND NOT nb2.active) active,
...