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:
x
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 addressesSELECT 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 addressesSELECT 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,