Andy D'Arata Andy D'Arata - 2 months ago 6
MySQL Question

Compare rows of a table MYSQL and output differences

So, complete MYSQL newb here. I'm almost embarrassed to post this question, but here it goes:

So, I have the following table...

CREATE TABLE IF NOT EXISTS cran_cisco (
id int not null auto_increment,
device_fqdn varchar(250) DEFAULT 0,
device_ip varchar(250) DEFAULT 0,
link_state varchar(250) DEFAULT 0,
line_protocol varchar(250) DEFAULT 0,
description varchar(250) DEFAULT 0,
date timestamp default now(),
PRIMARY KEY(id)
);


And I have created an external script to add the following information, which will come four times a day. Here's an excerpt of what it might look like:

insert into cran_cisco (device_fqdn, device_ip, link_state, line_protocol, description)
values ('test_box_2', '10.10.10.2', 'up', 'up', 'this is a test interface');
insert into cran_cisco (device_fqdn, device_ip, link_state, line_protocol, description)
values ('test_box_2', '10.10.10.2', 'up', 'down', 'this is a test interface');
insert into cran_cisco (device_fqdn, device_ip, link_state, line_protocol, description)
values ('test_box_2', '10.10.10.2', 'up', 'up', 'this is a test interface');
insert into cran_cisco (device_fqdn, device_ip, link_state, line_protocol, description)
values ('test_box_2', '10.10.10.2', 'up', 'down', 'this is a test interface');
insert into cran_cisco (device_fqdn, device_ip, link_state, line_protocol, description)
values ('test_box_2', '10.10.10.2', 'up', 'down', 'this is a test interface');
insert into cran_cisco (device_fqdn, device_ip, link_state, line_protocol, description)
values ('test_box_2', '10.10.10.2', 'up', 'up', 'this is a test interface');
insert into cran_cisco (device_fqdn, device_ip, link_state, line_protocol, description)
values ('test_box_2', '10.10.10.2', 'up', 'up', 'this is a test interface');
insert into cran_cisco (device_fqdn, device_ip, link_state, line_protocol, description)
values ('test_box_2', '10.10.10.2', 'up', 'down', 'this is a test interface');
insert into cran_cisco (device_fqdn, device_ip, link_state, line_protocol, description)
values ('test_box_2', '10.10.10.2', 'up', 'up', 'this is a test interface');


The table would then look like this:

| id | device_fqdn | device_ip | link_state | line_protocol | description | date |
+----+-------------+------------+------------+---------------+--------------------------+---------------------+
| 1 | test_box_2 | 10.10.10.2 | up | down | this is a test interface | 2016-10-07 08:16:42 |
| 2 | test_box_2 | 10.10.10.2 | up | up | this is a test interface | 2016-10-07 08:17:22 |
| 3 | test_box_2 | 10.10.10.2 | up | up | this is a test interface | 2016-10-07 08:23:55 |
| 4 | test_box_2 | 10.10.10.2 | up | down | this is a test interface | 2016-10-07 08:23:55 |
| 5 | test_box_2 | 10.10.10.2 | up | up | this is a test interface | 2016-10-07 08:23:55 |
| 6 | test_box_2 | 10.10.10.2 | up | up | this is a test interface | 2016-10-07 08:23:55 |
| 7 | test_box_2 | 10.10.10.2 | up | down | this is a test interface | 2016-10-07 08:23:55 |
| 8 | test_box_2 | 10.10.10.2 | up | up | this is a test interface | 2016-10-07 08:23:55 |
| 9 | test_box_2 | 10.10.10.2 | up | up | this is a test interface | 2016-10-07 08:23:55 |
| 10 | test_box_2 | 10.10.10.2 | up | down | this is a test interface | 2016-10-07 08:23:55 |
| 11 | test_box_2 | 10.10.10.2 | up | up | this is a test interface | 2016-10-07 08:23:57 |
+----+-------------+------------+------------+---------------+--------------------------+---------------------+


I would like to develop a query to report every time the line protocol or link went down. The expect output should be something like this:

| id | device_fqdn | device_ip | link_state | line_protocol | description | date |
+----+-------------+------------+------------+---------------+--------------------------+---------------------+
| 1 | test_box_2 | 10.10.10.2 | up | down | this is a test interface | 2016-10-07 08:16:42 |
| 4 | test_box_2 | 10.10.10.2 | up | down | this is a test interface | 2016-10-07 08:23:55 |
| 7 | test_box_2 | 10.10.10.2 | up | down | this is a test interface | 2016-10-07 08:23:55 |
| 10 | test_box_2 | 10.10.10.2 | up | down | this is a test interface | 2016-10-07 08:23:55 |
+----+-------------+------------+------------+---------------+--------------------------+---------------------+


Any help would be greatly appreciated. Thank you in advance.

Answer

You can use an OR condition in a WHERE clause to check this:

SELECT * from cran_cisco WHERE LINK_STATE = 'down' OR LINE_PROTOCOL = 'down'

If you are trying to accomplish the results as described in the comments, you could try ordering by data and device and creating LAG equivalents to track when a device first goes down:

set @lags = 'start' ;   
set @lagp = 'start' ;  
select id, device_fqdn, device_ip, link_state, line_protocol, description, date from (
select *, @lags,@lagp, @lags:=link_state, @lagp := line_protocol from cran_cisco order by device_fqdn, date) c 
where (link_state = 'down' and @lags = 'up' ) or ( line_protocol = 'down' and @lagp = 'up')

Each lag variable displays the previous rows value. We then pull from that result only where link_state or line_protocol is down and the previous row was up.

Here is an functional example