Skip to content
Advertisement

How to return boolean value if sub-query returns any result

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,
       ...
8 People found this is helpful
Advertisement