I am attempting to write a query to audit a table of IP addresses against a table of overlapping net blocks. Individual net blocks can be marked active or inactive via a BOOLEAN. The audit query should return a BOOLEAN as active depending upon the active column in the netblock table. If any net block has the active column set to FALSE, then the IP addresses contained within the netblock should report as active set to FALSE.
The following is the definition of the ip table:
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 )
);
The following are example entries in 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)
The following is the definition of the netblock table:
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 )
);
The following are example entries in the netblock table:
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)
I have figure out a query to list all “active” and “inactive” IP addresses in separate queries:
-- 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;
Results of above query:
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)
How do I replace the static value of unknown for the active column in the following table by queuing the netblock table for inactvie/active net blocks?
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;
Results from above query:
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)
Advertisement
Answer
You can use ALL and a subquery to check if all the net blocks the IP is in are active.
SELECT ...
true = ALL (SELECT nb2.active
FROM netblock nb2
WHERE ip.address <<= nb2.network) active,
...
Alternatively you can use NOT EXISTS:
SELECT ...
NOT EXISTS (SELECT *
FROM netblock nb2
WHERE ip.address <<= nb2.network
AND NOT nb2.active) active,
...