Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am struggling with a SQL query that lists hosts that are offline.

Problem

The problem occurs when I need to list hosts that don't have any ports open (status = 0), so looking at the table at the bottom it should just list 192.168.1.2, however it lists all of the hosts.

I have tried many queries + sub queries with no luck yet, I would be grateful if you can tell me where I am going wrong and let me know what the correct query is. Thank you.

MariaDB [scanner]> SELECT DISTINCT ports.ip_add FROM ports WHERE ports.status = FALSE;
+-------------+
| ip_add      |
+-------------+
| 192.168.1.1 |
| 192.168.1.2 |
| 192.168.1.3 |
+-------------+

Similar Working Query

This query lists all hosts that have a least one port open (status = 1), great.

MariaDB [scanner]> SELECT DISTINCT ports.ip_add FROM ports WHERE ports.status = TRUE;
+-------------+
| ip_add      |
+-------------+
| 192.168.1.1 |
| 192.168.1.3 |
+-------------+

Table

MariaDB [scanner]> SELECT * FROM ports LIMIT 9;
+--------+-------------+----------+------------+---------------------+
| id     | ip_add      | port     | status     | probe_meta          |
+--------+-------------+----------+------------+---------------------+
|      1 | 192.168.1.1 |       22 |          1 | 2016-03-29 00:01:00 |
|      2 | 192.168.1.1 |       21 |          1 | 2016-03-29 00:02:00 |
|      3 | 192.168.1.1 |       23 |          1 | 2016-03-29 00:03:00 |
|      4 | 192.168.1.2 |       22 |          0 | 2016-03-29 00:05:00 |
|      5 | 192.168.1.2 |       21 |          0 | 2016-03-29 00:06:00 |
|      6 | 192.168.1.2 |       23 |          0 | 2016-03-29 00:07:00 |
|      7 | 192.168.1.3 |       22 |          1 | 2016-03-29 00:09:00 |
|      8 | 192.168.1.3 |       21 |          0 | 2016-03-29 00:10:00 |
|      9 | 192.168.1.3 |       23 |          0 | 2016-03-29 00:11:00 |
+--------+-------------+----------+------------+---------------------+
share|improve this question
up vote 4 down vote accepted

One other way of doing this would be to aggregate the status column by ip_addr, where the total is equal to zero.

The test-bed:

CREATE TABLE `ports`
(
    `id` INT NOT NULL
    , `ip_add` VARCHAR(15) NOT NULL
    , `port` INT NOT NULL
    , `status` BIT NOT NULL
    , `probe_meta` DATETIME
);

INSERT INTO `ports` 
VALUES (1, '192.168.0.1', 22, 1, '2016-03-21 00:00:00');

INSERT INTO `ports` 
VALUES (1, '192.168.0.2', 22, 0, '2016-03-21 00:01:00');

INSERT INTO `ports` 
VALUES (1, '192.168.0.3', 22, 1, '2016-03-21 00:02:00');

INSERT INTO `ports` 
VALUES (1, '192.168.0.3', 22, 0, '2016-03-21 00:03:00');

The query:

SELECT ip_add
FROM `ports`
GROUP BY ip_add
HAVING SUM(status) = 0;

The results:

enter image description here

SQLFiddle

share|improve this answer
1  
Thank you Max, your solution also works. – rwcommand Mar 30 at 15:50
1  
If the status possible values are only 0 and 1, HAVING MAX(status)=0 would also work. – ypercubeᵀᴹ Mar 30 at 18:25
    
Both clever solutions (more clever than mine :-) – Lennart Mar 30 at 18:35
    
BIT_OR would be a... bit more explicit. – jpmc26 Mar 30 at 22:17

MySQL syntax is not my expertise, but this is the logical way to do it in T-SQL, so you get how you need to filter:

select distinct ip_add 
from ports 
where status = 0
and ip_add not in 
(
    select ip_add 
    from ports 
    where status = 1
    and ip_add is not null
)

This is the quick and dirty way, although there are more efficient ways to do it if the dataset is large, but hopefully you get the idea.

share|improve this answer
    
Thanks this works well :) – rwcommand Mar 30 at 14:58

The problem you describe is known as relational division. In mathematics you could say something like: FORALL x: p(x). This however is not possible to express in SQL, so we will have to transform the formulation of the problem. One common way is to say that: NOT EXISTS x:NOT p(x) which can be expressed in SQL. However, in your case it's probably easier to just count the ports with status 0 and compare with the total number of ports:

SELECT ports.ip_add 
FROM ports
GROUP BY ports.ip_add
HAVING COUNT(1) = COUNT(case when ports.status = 0 THEN 1 END);
share|improve this answer
    
Lennart thank you for the explanation, this helps. – rwcommand Mar 30 at 15:37
    
SUM(NOT ports.status) would be more elegant in the HAVING clause. – Dávid Horváth Mar 30 at 18:21
    
@DávidHorváth smaller but less readable. And non-standard as it would only work in MySQL (and SQLite), nowhere else. The CASE expression is much more clearer to read and understand, at least for me. – ypercubeᵀᴹ Mar 30 at 18:23
    
@DávidHorváth in the I of the beholder I guess :-) Personally I'm not to found of mixing boolean operators and integers. Another option might be: COUNT(NULLIF(ports.status,1)) – Lennart Mar 30 at 18:28
    
@rwcommand, I like Max Vernons answer and ypercubeᵀᴹ comment better than my answer. Feel free to accept that one instead if you agree. – Lennart Mar 30 at 18:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.